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;

Solution 1

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:

1. Remove constraints
2. Perform TRUNCATE
3. Delete manually the rows that now have references to nowhere
4. Create constraints

Option 2: suggested by user447951 in their answer

SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE table $table_name; 
SET FOREIGN_KEY_CHECKS = 1;

Solution 2

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.

Solution 3

I would simply do it with :

DELETE FROM mytest.instance;
ALTER TABLE mytest.instance AUTO_INCREMENT = 1;

Solution 4

Easy if you are using phpMyAdmin.

Just uncheck Enable foreign key checks option under SQL tab and run TRUNCATE <TABLE_NAME>

Solution 5

you can do

DELETE FROM `mytable` WHERE `id` > 0

View more solutions

Comments

[vinc3m1]

do we have to set SET FOREIGN_KEY_CHECKS=1; again afterwards?

[Pelle]

No, you don't. The setting is only valid during the connection. As soon as you disconnect, the next connection will have it set back to 1.

[SlimDeluxe]

This does not apply the 'ON DELETE' event in the referenced table, so this is not a complete answer.

[zerkms]

@barjonah: actually, it might break data integrity (see stackoverflow.com/questions/5452760/…). So, what you call "light" in the real world is considered to be a bad practice. PS: thanks for the downvote

[Sablefoste]

If using in PHPMYADMIN, this works only if you use all the transactions in the same SQL window (separated by a ;). This is because each fresh web SQL call will reset the FOREIGN_KEY_CHECKS to 1.

[SandorRacz]

Here is a very good way to find orphaned foreign keys (restore data integrity) http://stackoverflow.com/a/12085689/997776

[SandorRacz]

Here is a very good way to find orphaned foreign keys (restore data integrity) in case you are interested http://stackoverflow.com/a/12085689/997776

[Simon MᶜKenzie]

A note on your point about MySQL's 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.

[winkbrace]

Smart. When you want to delete all records anyway, you might as well reset the auto increment.

[phil294]

This is obviously the best way to do it. No risk of losing constraints, just plain delete. It's worth noticing that DELETE performs slower than TRUNCATE. But since this action is usually performed only rarely, this does not matter.

[Jeff]

This is good if that's all you want to do, but 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.

[Admin]

when I'm using delete statement, it report error 1175: You are using safe update mode, just add SET SQL_SAFE_UPDATES = 0; then it's fine

[Dung]

disable foreign key before truncate is also a good way, i did it with success from source: stackoverflow.com/questions/8641703/…

[NoobishPro]

@Dung disabling foreign key checks is only allowed in the development period. It breaks any existing relationships. zerkms is 100% right about data integrity. You can not disable foreign key checks on a working database unless you're planning to empty it completely (or at least all related tables)

[Sadık]

actually the answer is what I tried and got this error. SET FOREIGN_KEY_CHECKS = 0; TRUNCATE a_table;SET FOREIGN_KEY_CHECKS = 1; That lead to the error "cannot truncate a table..."

[zerkms]

@ArtenesNogueira the test environment should play the same rules. If it does not - then you cannot be confident what works in test would work in prod.

[Félix Adriyel Gagnon-Grenier]

@PelletenCate What makes you think you want the rest of your connection to ignore foreign key rules? This actually sounds like a recipe for disaster.

[Félix Adriyel Gagnon-Grenier]

"Deleting manually rows that now reference to nowhere" is nothing close to respecting data integrity. Errors can happen in this scenario. Foreign keys do not fail. Not a single user script that imitates deleting foreign rows is garanteed to never fail.

[Pelle]

Well, two things make me think that: 1. I have never seen any case where I would want to keep the connection alive after running TRUNCATE, and 2. I think running TRUNCATE in any environment that allows for recipes for disaster is a bad idea in the first place. In my opinion, your question – while valid – doesn't relate to anything I've ever run into over the last 15 years. YMMV.

[Félix Adriyel Gagnon-Grenier]

@PelletenCate So you can't envision any scenario where, after having truncated something, a script will either save an entity, or do some logging, or do any activity with database? Any of these could present risk or further damaging integrity if foreign keys haven't been set back.

[Pelle]

I had no intention of making it sound at all like I know every possible use case; I don't, and wouldn't have seen close to 1% even if I had 150 years of experience :-) Truncating a table in itself already is a recipe for data integrity violation, and I would never ever advise anyone to do that on any production system in the first place. To answer your question: I can totally envision it (I mean, it can be done so someone probably did it), but – thankfully – I never really had to on a mission critical system, and if I'd ever get at that point, I'd ask myself first what the hack went wrong.

[Martin Schneider]

this is not working for me on libmysql - mysqlnd 5.0.12 and phpMyAdmin 4.6.6. Raises the same error as only TRUNCATE. Furthermore all other tables that have foreign keys to it, are empty. George's answer did work.

[sijpkes]

@ArtenesNogueira I think you mean the dev environment. Testing needs to conform to production or it wouldn't be 'testing', it would just be 'playing'.

[zerkms]

@DeepakJain for your data. You need to learn how to handle data properly without getting it to the inconsistent state, even in development.

[AAEM]

I tried it bur, the following error appeared:Error Code: 1142. DELETE command denied to user 'root'@'localhost' for table 'mytable'

[Shihe Zhang]

When using this solution, it reports error 1175: You are using safe update mode,... change delete clause to DELETE FROM mydb.mytable where id != 0 makes it perfect.

[zerkms]

@bhattraideb postgresql supports truncate cascade: postgresql.org/docs/current/sql-truncate.html

[merlin]

Please note that this is also a feature within phpMyAdmin, once you go to the sql query window, just uncheck the box.

[Lukáš Řádek]

Is it a correct behaviour, that this error is thrown even when referencing table is empty and so there are no references to truncated table? i.e. the constraint is not data based, but rather structure based?

[zerkms]

@LukášŘádek I think it has something to do with DDL being non-transactional in mysql.

[Miloslav Milo Janoušek]

or just DELETE FROM mytable

[nice_dev]

This wouldn't reset the auto increment.

[Peter]

The OP tagged MySQL. While this is valid in Postgres, it is incorrect in MySQL.

[Dawid Gałecki]

If you want to execute few lines of code ane turn off checking foreign keys for each line of code, you have to add SET FOREIGN_KEY_CHECKS = 0; before each query call. As mentioned above, value goes back to 1 after each code execution.

[Kamlesh]

@zerkms sorry dear, your solution is not working. i am using below statement. SET FOREIGN_KEY_CHECKS = 0; TRUNCATE yurt90w_user_package_transactions_bookings; TRUNCATE yurt90w_user_package_transactions ; SET FOREIGN_KEY_CHECKS = 1; But still getting the same error "Cannot truncate a table referenced in a foreign key constraint"

[Kamlesh]

You Rocked Dear, It works like charm. Deleted, set auto increment to 1 then truncate table. BTW i was not able to truncate table while i was using SET FOREIGN_KEY_CHECKS = 0; statement before truncate table query. Thanks dear :)

[zerkms]

@Kamlesh it's not my solution, I adviced to not do it that barbaric way.

[fsevenm]

@zerkms some people tried that but it leads to an error. But in mine, it works and I think it's shorter in code

[zerkms]

@fsevenm it may silently corrupt your data.

[fsevenm]

@zerkms how can it be so? I maybe don't know that

[John Lord]

you can most certainly do this. However if you have a table with seven million records, go take a lunch while you're waiting.

[Peter]

Why nobody mentions that TRUNCATE will reset the PRIMARY KEY? With DELETE it won't reset the PRIMARY KEY so your first record will have ID like 325579 which is strange. TRUNCATE shouldn't fail this way IMHO. Truncate is reset the table so it should reset no matter what.

[Novasol]

Solution 2 worked for me in phpMyAdmin, but without TRUNCATE table1;

[Kamlesh]

Solutions depend on mysql version that's why I posted 2 solutions. Happy to share and help :)

[Kamlesh]

@Novasol Yes, you are right, it works but if you run TRUNCATE table1; command then mysql table index will be set to 0 internally automatically which will take less time when you make join this table1 with any other table/tables.

[Dilaksha A]

Well, the check MySQL does is too basic. In my opinion, it should only check if the truncate operation violates the foreign key constraint. If not, just allow, just as how MSSQL and other RDBMS systems do

[zerkms]

@DilakshaA TRUNCATE is different in how it's implemented. If one wants to perform those checks - they may run DELETE.

[Dilaksha A]

@zerkms - apologies if I was nto clear, of course the MySQL implementation of truncate and delete are different. What I am saying is truncate implementation could be better - not make it same as delete statement. If the truncate operation does not violate FK constraints (i.e. the child tables are empty), there is no reason to error it saying FK violation - as there would not be any! This is how other RDBMS truncate implementation work.

[OzzyTheGiant]

Definitely don't use the second option for production or for large datasets but if you're just testing a quick insert while developing an app and it was one row, the second option is just fine.

[zerkms]

@OzzyTheGiant if you're developing an app - why not use DELETE.

[OzzyTheGiant]

@zerkms I do, but it annoys me that MySQL doesn't reset the auto_increment id number back to 1 or the lowest number possible, so I truncate.

[zerkms]

@OzzyTheGiant when you develop - what's the different between having id 1 or 42?

[OzzyTheGiant]

@zerkms in general, there's not much difference. It's just a preference, like I said, it annoys me, I just want it to reset to 1. I'm not saying anyone needs to truncate the table. You can go ahead and use delete. It's not a big deal if all I did was delete the one row that existed in a development table.

[Kyle]

@AAEM Could be a few problems. Usually means you need to grant delete permissions to your "root" user on your database, or run flush privileges. See here: stackoverflow.com/questions/4767055/…