如何截断外键约束表？

为什么mygroup上的截断不起作用？虽然我有ON DELETE CASCADE SET我得到：

错误1701（42000）：无法截断外键约束中引用的表（mytest.instance， CONSTRAINTinstance_ibfk_1 FOREIGN 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;
733373 次浏览
小开
最佳答案

您不能TRUNCATE应用了FK约束的表（TRUNCATE与DELETE不同）。

要解决此问题，请使用以下任何一种解决方案。两者都存在损坏数据完整性的风险。

备选案文1：

  1. 删除约束
  2. 执行TRUNCATE
  3. 手动删除现在引用无处的行
  4. 创建约束

备选案文2：建议由user447951在他们的答案

SET FOREIGN_KEY_CHECKS = 0;TRUNCATE table $table_name;SET FOREIGN_KEY_CHECKS = 1;
小开

是的，你可以：

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE table1;TRUNCATE table2;
SET FOREIGN_KEY_CHECKS = 1;

使用这些语句，您可能会让不符合FOREIGN KEY约束的行进入表中。

小开

根据MySQL留档，TRUNCATE不能用于具有外键关系的表。没有完整的替代AFAIK。

删除禁忌仍然不会调用ON DELETE和ON UPDATE。我唯一能想到的ATM解决方案是：

  • 删除所有行，删除外键，截断，重新创建键
  • 删除所有行，重置auto_increment（如果使用）

似乎MySQL中的TRUNCATE还不是一个完整的功能（它也不调用触发器）。评论

小开

我会简单地这样做：

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

答案确实是由zerkms提供的，如备选案文1所述：

备选案文1：不会损害数据完整性：

  1. 删除约束
  2. 执行截断
  3. 手动删除现在对任何地方都有引用的行
  4. 创建约束

棘手的部分是消除限制，所以我想告诉你如何，如果有人需要知道如何做到这一点：

  1. 运行SHOW CREATE TABLE <Table Name>查询以查看您的外键的名称（下图中的红色框）：

    输入图片描述

  2. 运行ALTER TABLE <Table Name> DROP FOREIGN KEY <Foreign Key Name>。这将删除外键约束。

  3. 删除关联的Index（通过表结构页面），就完成了。

重新创建外键：

ALTER TABLE <Table Name>ADD FOREIGN KEY (<Field Name>) REFERENCES <Foreign Table Name>(<Field Name>);
小开

虽然这个问题被问到我不知道，但现在如果您使用phpMyAdmin，您可以简单地打开数据库并选择要截断的表。

  • 在底部有一个包含许多选项的下拉列表。打开它并在标题Delete data or table下选择Empty选项。
  • 它会自动将您带到下一页，其中复选框中有一个名为Enable foreign key checks的选项。只需取消选中它并按下Yes按钮，选定的表格将被截断。

也许它在内部运行user447951的回答中建议的查询，但从phpMyAdmin界面使用非常方便。

小开

你可以做

DELETE FROM `mytable` WHERE `id` > 0
小开

获取旧的外键检查状态和sql模式是截断/删除表的最佳方式，就像Mysql Workdesk在同步模型到数据库时所做的那样。

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;`SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
DROP TABLE TABLE_NAME;TRUNCATE TABLE_NAME;
SET SQL_MODE=@OLD_SQL_MODE;SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
小开

如果表的数据库引擎不同，您将收到此错误，因此将它们更改为InnoDB

ALTER TABLE my_table ENGINE = InnoDB;
小开

如果您使用的是phpMyAdmin。

只需取消选中SQL选项卡下的Enable foreign key checks选项并运行TRUNCATE <TABLE_NAME>

在此处输入图片描述

小开

只需使用CASCADE

TRUNCATE "products" RESTART IDENTITY CASCADE;

但准备好级联删除）

小开

在MYSQL数据库上测试

解决方案1： 

SET FOREIGN_KEY_CHECKS = 0;TRUNCATE table1;

解决方案2： 

DELETE FROM table1;ALTER TABLE table1 AUTO_INCREMENT = 1;TRUNCATE table1;

这对我很有用。我希望这对你也有帮助。谢谢你问这个问题。

小开

另一种解决方法是删除表中的所有行，然后重置自动增量列：

delete from table_name where 1

然后运行：

ALTER TABLE table_name AUTO_INCREMENT = 1
小开

如何截断外键约束表？这个插图将演示如何在使用外键约束截断表时解决mysql错误。如果您使用的是PHPMYADMIN，则很容易截断具有外键约束的表。

  1. 登录到PHPMYADMIN并单击要截断的表。
  2. 然后转到SQL选项卡放置代码以截断SQL中的表编辑器示例截断表学生；将学生替换为表的名称。
  3. 在编辑器底部取消勾选“启用外键检查”复选框，如下所示：

输入图片描述

它会像魔法一样工作。

小开

如果您正在使用laravel迁移，您可以使用facades助手执行此操作

喜欢使用雄辩的对象，回答“雄辩”的方式

 Schema::disableForeignKeyConstraints();Teacher::truncate();Schema::enableForeignKeyConstraints();

在Laravel 7和8中，为了跨4个数据库（MySql、Postgres、SQLite和SqlServer）兼容，并且没有Eloquent，您可以使用：

Schema::disableForeignKeyConstraints();DB::table('teachers')->truncate();Schema::enableForeignKeyConstraints();

提交答案