1
Consider table "A" and "B".
Table "B" has a Foreign Keys for table "A".
Table "A" has no Foreign Keys.
None of the tables have records.
When trying to make a trucate to table "A", the following error is returned:
Cannot truncate a table referenced in a Foreign key Constraint (
bd.B, CONSTRAINTFK_1E45D7B68A6FD59CFOREIGN KEY (a_id) REFERENCESbd.A(id))
If there is nothing in the tables, because the error occurs?
1
Although there is no reason to want to truncate an empty table, the error happens because truncate is different from delete. As you are using foreign key, it is likely that you are using InnoDB, which makes the truncate an even more peculiar process.
When you write a TRUNCATE table1 in the InnoDB, it automatically tries to give a DROP table1 and then recreates it. But it is impossible to exclude a table that is referenced by a foreign key.
If you just want to truncate the table so you can revert the auto_increment to 1, you can
ALTER TABLE tabela1 AUTO_INCREMENT=1
If the table is empty, as you said, it will work perfectly.
I use a program that does this, if there are problems in the import, it does a truncate and re-import the table to keep the ID’s correct. The problem has been solved in another way, since it is possible to configure the system to use "transactions", so it only records if everything goes well.
Browser other questions tagged mysql
You are not signed in. Login or sign up in order to post.
Why you want to truncate an empty table?
– Marco Aurélio Deleu
@Marcoauréliodeleu is an automatic import script, if something goes wrong, the script runs again and all inserted records are deleted. I didn’t put that in the question because it doesn’t help solve the problem.
– Filipe Moraes