How to truncate a foreign key constrained table

Question

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

User · Answer

As per mysql documentation  TRUNCATE cannot be used on tables with foreign key relationships  There is no complete alternative AFAIK   Dropping the contraint still does not invoke the ON DELETE and ON UPDATE  The only solution I can ATM think of is to either    delete all rows  drop the foreign keys  truncate  recreate keys delete all rows  reset auto increment  if used    It would seem TRUNCATE in MySQL is not a complete feature yet  it also does not invoke triggers   See comment

User · Answer

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

User · Answer

Just use CASCADE  TRUNCATE  products  RESTART IDENTITY CASCADE    But be ready for cascade deletes

User · Answer

Getting the old foreign key check state and sql mode are best way to truncate   Drop the table as Mysql Workbench do while synchronizing model to database   SET  OLD UNIQUE CHECKS   UNIQUE CHECKS  UNIQUE CHECKS 0  SET  OLD FOREIGN KEY CHECKS   FOREIGN KEY CHECKS  FOREIGN KEY CHECKS 0   SET  OLD SQL MODE   SQL MODE  SQL MODE  TRADITIONAL ALLOW INVALID DATES    DROP TABLE TABLE NAME  TRUNCATE TABLE NAME   SET SQL MODE  OLD SQL MODE  SET FOREIGN KEY CHECKS  OLD FOREIGN KEY CHECKS  SET UNIQUE CHECKS  OLD UNIQUE CHECKS

User · Answer

Tested on MYSQL Database  Solution 1    SET FOREIGN KEY CHECKS   0  TRUNCATE table1    Solution 2    DELETE FROM table1  ALTER TABLE table1 AUTO INCREMENT   1  TRUNCATE table1    This works for me  I hope  this will help you also  Thanks for asking this question

User · Answer

While this question was asked I didn t know about it  but now if you use phpMyAdmin you can simply open the database and select the table s  you want to truncate    At the bottom there is a drop down with many options  Open it and select Empty option under the heading Delete data or table  It takes you to the next page automatically where there is an option in checkbox called Enable foreign key checks  Just unselect it and press the Yes button and the selected table s  will be truncated    Maybe it internally runs the query suggested in user447951 s answer  but it is very convenient to use from phpMyAdmin interface

User · Answer

Yes you can   SET FOREIGN KEY CHECKS   0   TRUNCATE table1  TRUNCATE table2   SET FOREIGN KEY CHECKS   1    With these statements  you risk letting in rows into your tables that do not adhere to the FOREIGN KEY constraints

User · Answer

Answer is indeed the one provided by zerkms  as stated on Option 1      Option 1  which does not risk damage to data integrity          Remove constraints   Perform TRUNCATE   Delete manually the rows that now have references to nowhere   Create constraints      The tricky part is Removing constraints  so I want to tell you how  in case someone needs to know how to do that    Run SHOW CREATE TABLE   lt Table Name gt  query to see what is your FOREIGN KEY s name  Red frame in below image     Run ALTER TABLE  lt Table Name gt  DROP FOREIGN KEY  lt Foreign Key Name gt   This will remove the foreign key constraint  Drop the associated Index  through table structure page   and you are done    to re-create foreign keys   ALTER TABLE  lt Table Name gt  ADD FOREIGN KEY   lt Field Name gt   REFERENCES  lt Foreign Table Name gt   lt Field Name gt

User · Answer

you can do  DELETE FROM  mytable  WHERE  id   gt  0

User · Answer

I would simply do it with    DELETE FROM mytest instance  ALTER TABLE mytest instance AUTO INCREMENT   1

User · Answer

Easy if you are using phpMyAdmin   Just uncheck Enable foreign key checks option under SQL tab and run TRUNCATE  lt TABLE NAME gt

User · Answer

If the database engine for tables differ you will get this error so change them to InnoDB  ALTER TABLE my table ENGINE   InnoDB

[mysql] How to truncate a foreign key constrained table?

Examples related to mysql

Examples related to foreign-keys

Examples related to constraints

Examples related to truncate

Examples related to dml