Description:
If a table B references a table A, and B gets truncated, then A gets truncated, the query fails with a foreign key constraint error, even though there is no record anymore pointing to A.
However, deleting manually the records from A does work.
How to repeat:
mysql> CREATE TABLE A (
-> id INT UNSIGNED NOT NULL,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> CREATE TABLE B (
-> id INT UNSIGNED NOT NULL,
-> fk INT UNSIGNED NOT NULL,
-> PRIMARY KEY (id),
-> KEY (fk),
-> FOREIGN KEY (fk) REFERENCES A (id)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO A (id) VALUES (1);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO B (id,fk) VALUES (1, 1);
Query OK, 1 row affected (0.02 sec)
mysql> TRUNCATE B;
Query OK, 0 rows affected (0.00 sec)
mysql> TRUNCATE A;
ERROR 1714 (42000): Cannot truncate a table referenced in a foreign key constraint (`test`.`b`, CONSTRAINT `b_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `test`.`A` (`id`))
mysql> SELECT COUNT(*) FROM B;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> DELETE FROM A WHERE id = 1;
Query OK, 0 rows affected (0.00 sec)
Description: If a table B references a table A, and B gets truncated, then A gets truncated, the query fails with a foreign key constraint error, even though there is no record anymore pointing to A. However, deleting manually the records from A does work. How to repeat: mysql> CREATE TABLE A ( -> id INT UNSIGNED NOT NULL, -> PRIMARY KEY (id) -> ); Query OK, 0 rows affected (0.06 sec) mysql> CREATE TABLE B ( -> id INT UNSIGNED NOT NULL, -> fk INT UNSIGNED NOT NULL, -> PRIMARY KEY (id), -> KEY (fk), -> FOREIGN KEY (fk) REFERENCES A (id) -> ); Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO A (id) VALUES (1); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO B (id,fk) VALUES (1, 1); Query OK, 1 row affected (0.02 sec) mysql> TRUNCATE B; Query OK, 0 rows affected (0.00 sec) mysql> TRUNCATE A; ERROR 1714 (42000): Cannot truncate a table referenced in a foreign key constraint (`test`.`b`, CONSTRAINT `b_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `test`.`A` (`id`)) mysql> SELECT COUNT(*) FROM B; +----------+ | COUNT(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql> DELETE FROM A WHERE id = 1; Query OK, 0 rows affected (0.00 sec)