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;

As per [mysql documentation](

[To see links please register here]

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

<strike>It would seem TRUNCATE in MySQL is not a complete feature yet (it also does not invoke triggers).</strike>
See comment

I would simply do it with :

DELETE FROM mytest.instance;
ALTER TABLE mytest.instance AUTO_INCREMENT = 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.

you can do

DELETE FROM `mytable` WHERE `id` > 0

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][1]*

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


[1]:

[To see links please register here]

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 is indeed [the one provided by **zerkms**][1], 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):

[![enter image description here][2]][2]
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>);

[1]:

[To see links please register here]

[2]:

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

ALTER TABLE my_table ENGINE = InnoDB;

Easy if you are using phpMyAdmin.

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

[![enter image description here][1]][1]


[1]: