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, CONSTRAINTinstance_ibfk_1FOREIGN KEY (GroupID) REFERENCESmytest.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;