mysql - 无法从空表中截断外键约束中引用的表

Question

我有以下表格:

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

我能够截断 companies_investorfundinground。

我尝试删除 companies_fundinground 但出现错误:

Cannot truncate a table referenced in a foreign key constraint companies_funding_round_id_8edc4cc4_fk_companies_fundinground_id

如果 companies_investorfundinground 被完全截断，为什么我会收到此错误？

Answer 1

TRUNCATE TABLE 相当于删除表并重新创建一个新表。这会破坏外键引用。

它在 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.

换个角度考虑:如果 TRUNCATE TABLE 应该快速高效，是否值得花时间检查子表以查看它是否有任何引用行？该表可能有数百万行，但所有行的外键列中都有 NULL。

如果您确定不会打乱子表，则有一个解决方法:

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;