In MySQL we create tables and build relationship among them. In the process if we need to truncate foreign key constrained table then we can’t do it simply and MySQL will throw an error even we have already removed table having foreign key constraint:
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint…
I’m presenting here 2 ways to truncate foreign key constrained table in MySQL with a simple category, product tables example.
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE TABLE category ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Name VARCHAR (100) NOT NULL ) ENGINE=InnoDB; CREATE TABLE product ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, CatID INT NOT NULL, Name VARCHAR (100) NOT NULL, Description VARCHAR (500) NOT NULL, FOREIGN KEY (CatID) REFERENCES category(ID) ON DELETE CASCADE ) ENGINE=InnoDB; |
In the table above we have tables with foreign key constraints and if we wish to truncate category table then we can’t do it by running simple query TRUNCATE category as we cannot truncate a table that has foreign key constraints applied on it.
2 ways to truncate foreign key constrained table
First one longer but does not risk damage to data integrity:
- Remove constraints
- Perform TRUNCATE
- Delete manually the rows that now have references to ‘nowhere‘
- Create constraints again
Second one is short but can damage your data integrity. It’s a bad practice and use it if you can risk damage to data integrity.
1 2 3 4 5 | SET FOREIGN_KEY_CHECKS = 0; TRUNCATE category; SET FOREIGN_KEY_CHECKS = 1; |
So here we are temporarily disabling foreign key checks to forcibly truncate foreign key constrained table (category in our case).