Is it impossible to trick a table with foreign keys even though it is empty?

Question

Is it impossible to trick a table with foreign keys even though it is empty?

Navigation

#1 by (1 votes)

1

Consider table "A" and "B".

Table "B" has a foreign keys referring to table "A".
The "A" table has no foreign keys.
None of the tables have records.

When trying to truncate table "A", the following error is returned:

Can not truncate a table referenced in a foreign key constraint ( bd , B , CONSTRAINT FK_1E45D7B68A6FD59C FOREIGN KEY ( a_id ) REFERENCES bd . A ( id ))

If there is nothing in the tables, why does the error occur?

mysql

asked by anonymous 11.01.2016 / 12:47

1 answer

Is it possible to convert an [Any] array to NSData? Start class Fragment of another Class Fragment

score 1 · Accepted Answer

Although there is no reason to want to truncate an empty table, the error occurs because truncate is different from delete . Since you are using foreign key, you are probably using InnoDB , which makes truncate even more peculiar.

When you type TRUNCATE table1 into InnoDB , it automatically tries to give DROP table1 and then re-creates it. But it is impossible to delete a table that is referenced by a foreign key.

If you just want to truncate the table to be able to return the auto_increment to 1, you can

ALTER TABLE tabela1 AUTO_INCREMENT=1

If the table is empty, as you said, this will work perfectly.