How to truncate a foreign key constrained table?
constraints, dml, foreign-keys, mysql, truncate
Solution
You cannot `TRUNCATE` a table that has FK constraints applied on it (`TRUNCATE` is not the same as `DELETE`).
To work around this, use either of these solutions. Both present risks of damaging the data integrity.
Option 1:
- Remove constraints
- Perform `TRUNCATE`
- Delete manually the rows that now have references to nowhere
- Create constraints
Option 2: suggested by user447951 in their answer
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE table $table_name;
SET FOREIGN_KEY_CHECKS = 1;
Problem
Why doesn't a TRUNCATE on `mygroup` work? Even though I have `ON DELETE CASCADE SET` I get: ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`mytest`.`instance`, CONSTRAINT `instance_ibfk_1` FOREIGN KEY (`GroupID`) REFERENCES `mytest`.`mygroup` (`ID`)) ``` drop database mytest; create database mytest; use mytest; CREATE TABLE mygroup ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY ) ENGINE=InnoDB; CREATE TABLE instance ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, GroupID INT NOT NULL, DateTime DATETIME DEFAULT NULL, FOREIGN KEY (GroupID) REFERENCES mygroup(ID) ON DELETE CASCADE, UNIQUE(GroupID) ) ENGINE=InnoDB; ```