Cannot truncate a table referenced in a foreign key constraint from empty table

Question

I have the following tables:

CREATE TABLE `companies_investorfundinground` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `funding_round_id` int(11) NOT NULL,
  `investor_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `companies_funding_round_id_8edc4cc4_fk_companies_fundinground_id` (`funding_round_id`),
  KEY `companies_investor_investor_id_30d4fd3e_fk_companies_investor_id` (`investor_id`),
  CONSTRAINT `companies_funding_round_id_8edc4cc4_fk_companies_fundinground_id` FOREIGN KEY (`funding_round_id`) REFERENCES `companies_fundinground` (`id`),
  CONSTRAINT `companies_investor_investor_id_30d4fd3e_fk_companies_investor_id` FOREIGN KEY (`investor_id`) REFERENCES `companies_investor` (`id`)
)


CREATE TABLE `companies_fundinground` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `funding_round_code` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `companies_fundinground_447d3092` (`company_id`),
  CONSTRAINT `companies_company_id_36dd5970_fk_companies_company_entity_ptr_id` FOREIGN KEY (`company_id`) REFERENCES `companies_company` (`entity_ptr_id`)
)

I was able to truncate companies_investorfundinground.

I try to delete companies_fundinground but I get the error:

Cannot truncate a table referenced in a foreign key constraint companies_funding_round_id_8edc4cc4_fk_companies_fundinground_id

Why am I getting this error if companies_investorfundinground is completely truncated?

Answer 1

TRUNCATE TABLE is equivalent to dropping the table and recreating it as a new table. This would break the foreign key reference.

It says in https://dev.mysql.com/doc/refman/5.7/en/truncate-table.html:

Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements. To achieve high performance, it bypasses the DML method of deleting data. Thus, it cannot be rolled back, it does not cause ON DELETE triggers to fire, and it cannot be performed for InnoDB tables with parent-child foreign key relationships.

Consider this another way: if TRUNCATE TABLE is supposed to be fast and efficient, is it worth spending the time to check a child table to see if it has any referencing rows? That table might have millions of rows, but have NULL in its foreign key column on all rows.

If you know for certain that you won't upset the child table, you have a workaround:

mysql> create table p ( id int primary key );

mysql> create table f ( pid int, foreign key (pid) references p(id));

mysql> truncate table p;
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint
(`test`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `test`.`p` (`id`))

mysql> set foreign_key_checks=0;

mysql> truncate table p;

mysql> set foreign_key_checks=1;