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;

Prohibitionist

Answer 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;

Herr

@barjonah: actually, it might break data integrity (see #5453260). So, what you call "light" in the real world is considered to be a bad practice. PS: thanks for the downvote – Herr

Here is a very good way to find orphaned foreign keys (restore data integrity) https://mcmap.net/q/54876/-force-innodb-to-recheck-foreign-keys-on-a-table-tables – Congest

disable foreign key before truncate is also a good way, i did it with success from source: #8642203 – Speaks

@Speaks 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) – Piwowar

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..." – Demulsify

@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. – Herr

"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. – Babism

@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'. – Antoniettaantonin

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

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

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? – Constrain

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

@Herr 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" – Curfew

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

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

@Stertorous it may silently corrupt your data. – Herr

@Herr how can it be so? I maybe don't know that – Stertorous

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 – Batchelder

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

@Herr - 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. – Batchelder

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. – Ovule

@Ovule if you're developing an app - why not use DELETE. – Herr

@Herr 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. – Ovule

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

@Herr 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. – Ovule

Answer 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.

Haddington

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

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. – Antlia

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

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. – Dvina

Here is a very good way to find orphaned foreign keys (restore data integrity) in case you are interested https://mcmap.net/q/54876/-force-innodb-to-recheck-foreign-keys-on-a-table-tables – Congest

@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. – Babism

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. – Antlia

@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. – Babism

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. – Antlia

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. – Vinegarette

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

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. – Modla

Answer 3

I would simply do it with :

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

Doorjamb

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

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. – Luxor

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. – Dona

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 – Rioux

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. – Orientate

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 :) – Curfew

Answer 4

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.

Curfew

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

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

@Champ 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. – Curfew

Answer 5

Easy if you are using phpMyAdmin.

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

Fussell

Perfect Answer for phpMyAdmin users. – Parietal

Ahh, thx, nothing else works in phpmyadmin xd – Keating

Answer 6

you can do

DELETE FROM `mytable` WHERE `id` > 0

Guaiacol

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

or just DELETE FROM mytable – Chairborne

This wouldn't reset the auto increment. – Greatest

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

@Rickierickman 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: #4767555 – Olsson

Answer 7

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

Liberty

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. – Wanonah

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. – Mariellamarielle

Answer 8

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.

Plainspoken

Answer 9

Answer is indeed the one provided by zerkms, as stated on Option 1:

Option 1: which does not risk damage to data integrity:

1. Remove constraints
2. Perform TRUNCATE
3. Delete manually the rows that now have references to nowhere
4. 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:

1. Run SHOW CREATE TABLE <Table Name> query to see what is your FOREIGN KEY's name (Red frame in below image):

2. Run ALTER TABLE <Table Name> DROP FOREIGN KEY <Foreign Key Name>. This will remove the foreign key constraint.

3. Drop the associated Index (through table structure page), and you are done.

to re-create foreign keys:

ALTER TABLE <Table Name>
ADD FOREIGN KEY (<Field Name>) REFERENCES <Foreign Table Name>(<Field Name>);

Attending

Answer 10

Another workaround is delete all rows in the table then reset auto-increment columns:

delete from table_name where 1

then Run:

ALTER TABLE table_name AUTO_INCREMENT = 1

Hollar

Answer 11

How to truncate a foreign key constrained table? This illustration will demonstrate how to solve mysql error when truncating a table with foreign key constraint. If you are using PHPMYADMIN, it is very easy to truncate a table with foreign key constraint.

1. Login to PHPMYADMIN and click the table you want to truncate.
2. Then go to SQL tab Place your code to truncate the table in the SQL Editor example truncate table students; Replace students with the name of the table.
3. At the bottom of the editor untick the "Enable foreign key checks" checkbox as shown below:

It will work like magic.

Enchanter

Answer 12

if you are using laravel migrations, you can do this using facades helpers

prefer to use Eloquent objects, answer the "Eloquent" way

 Schema::disableForeignKeyConstraints();
 Teacher::truncate();
 Schema::enableForeignKeyConstraints();

In Laravel 7 and 8, for compatibility across 4 databases (MySql, Postgres, SQLite and SqlServer) and no Eloquent, you can use:

Schema::disableForeignKeyConstraints();
    DB::table('teachers')->truncate();
Schema::enableForeignKeyConstraints();

Pointing

Pretty handy trick. Thanks! – Worthless

Answer 13

Just use CASCADE

TRUNCATE "products" RESTART IDENTITY CASCADE;

But be ready for cascade deletes )

Liquefacient

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

Answer 14

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;

Geese

Answer 15

If the database engine for tables differ you will get this error so change them to InnoDB

ALTER TABLE my_table ENGINE = InnoDB;

Tuinenga

Answer 16

Before crud operation query and truncating table, we use a safe query at that point.

For a crude operation like deleting a query, you can write this command.

SET SQL_SAFE_UPDATES = 0;

after this that query can rewrite zero to one for safe query.

 SET SQL_SAFE_UPDATES = 1;

For truncate tables, we can write the command below before truncating the query.

SET FOREIGN_KEY_CHECKS = 0;

after that query can rewrite zero to one for safe query.

 SET FOREIGN_KEY_CHECKS = 1;

Conspecific