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;

---

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;

---

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.

---

我只是简单地做到这一点：

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

链接地址: http://www.djcxy.com/p/65392.html

上一篇: ormlite在DatabaseField中存储未知的外来对象

下一篇: 如何截断外键约束表？