Description:
The manual said "TRUNCATE TABLE fails for an InnoDB table or NDB table if there are any FOREIGN KEY constraints from other tables that reference the table. Foreign key constraints between columns of the same table are permitted. "
https://dev.mysql.com/doc/refman/5.7/en/truncate-table.html
https://dev.mysql.com/doc/refman/5.6/en/truncate-table.html
and "You cannot issue DROP TABLE for a table that is referenced by a FOREIGN KEY constraint, unless you do SET foreign_key_checks = 0. When you drop a table, any constraints that were defined in the statement used to create that table are also dropped."
https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html
https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html
Based on Manual, we cannot issue TRUNCATE TABLE even if SET foreign_key_checks = 0, but implementation is different between InnoDB and NDB.
InnoDB: can issue TRUNCATE TABLE.
NDB: cannot issue TRUNCATE TABLE.
How to repeat:
See this manual for test case.
https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html
[InnoDB]
CREATE TABLE parent (
id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (
id INT,
parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id)
REFERENCES parent(id)
ON DELETE CASCADE
) ENGINE=INNODB;
SET foreign_key_checks = 0;
mysql> truncate table parent;
Query OK, 0 rows affected (0.31 sec)
mysql> drop table parent;
Query OK, 0 rows affected (0.31 sec)
[NDB]
CREATE TABLE parent (
id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=NDB;
CREATE TABLE child (
id INT,
parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id)
REFERENCES parent(id)
ON DELETE CASCADE
) ENGINE=NDB;
mysql> truncate table parent;
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint ()
mysql> drop table parent;
Query OK, 0 rows affected (0.31 sec)
Suggested fix:
I don't know about current innodb implementation is good or not.
drop(or truncate) table parent cause inconsistent status for FK.
