How to truncate a foreign key constrained table?
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;
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.
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;
I would simply do it with :
DELETE FROM mytest.instance;
ALTER TABLE mytest.instance AUTO_INCREMENT = 1;
DELETE performs slower than TRUNCATE. But since this action is usually performed only rarely, this does not matter.
DELETE can be absolutely brutal if you have too many rows - since it hits the logs, whereas TRUNCATE just rips the data out. Really depends on use case.
error 1175: You are using safe update mode,... change delete clause to DELETE FROM mydb.mytable where id != 0 makes it perfect.
Easy if you are using phpMyAdmin.
Just uncheck Enable foreign key checks option under SQL tab and run TRUNCATE <TABLE_NAME>
https://i.stack.imgur.com/eKvIg.jpg
you can do
DELETE FROM `mytable` WHERE `id` > 0
mytable
flush privileges. See here: stackoverflow.com/questions/4767055/…
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.
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
TRUNCATE being incomplete - truncate isn't supposed to invoke triggers etc. If it did, it would just be the same as DELETE! It's row-agnostic, hence it's unable to perform row-related operations (like invoking triggers or examining foreign keys). It works in the same way in Oracle and Sql Server.
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.
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
;). This is because each fresh web SQL call will reset theFOREIGN_KEY_CHECKSto 1.