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;

Answer 1

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.

Answer 2

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;

Answer 3

I would simply do it with :

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

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.

Answer 5

Easy if you are using phpMyAdmin.

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

Answer 6

you can do

DELETE FROM `mytable` WHERE `id` > 0

Answer 7

As per MySQL documentation, TRUNCATE cannot be used on tables with foreign key relationships. There is no complete alternative AFAIK.

Dropping the constraint 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

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.

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

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

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.

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();

Answer 13

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;

Answer 14

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

ALTER TABLE my_table ENGINE = InnoDB;

Answer 15

Just use CASCADE

TRUNCATE "products" RESTART IDENTITY CASCADE;

But be ready for cascade deletes )

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;