本文介绍了截断外键约束表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

为什么 TRUNCATE mygroup 上不起作用?
即使我有 ON DELETE CASCADE SET 我得到:

Why doesn't a TRUNCATE on mygroup work?Even though I have ON DELETE CASCADE SET I get:



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;


推荐答案

您不能 TRUNCATE 一个应用了FK约束的表( TRUNCATE DELETE 不同)。

要解决此问题:

选项1:数据完整性:


  1. 删除约束

  2. 执行 TRUNCATE

  3. 手动删除现在已引用无处的行

  4. 创建约束
  5. $ b ,这是不好的做法。

    Option 2: suggested by user447951 in his answer, which is bad practice. Use it if you are OK risking damage to data integrity.

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

    这篇关于截断外键约束表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

05-27 04:52
查看更多