mysql - 删除具有递归外键约束的记录

Question

从一个表中删除记录的最佳方法是什么，该表具有指向另一个表的外键，而另一个表具有指向它的外键？

例如，我有以下表格。 accounts 有一个或多个网络，accounts 必须有一个默认网络。

CREATE TABLE IF NOT EXISTS accounts (
  id INT NOT NULL,
  networksId INT NOT NULL,
  PRIMARY KEY (id),
  INDEX fk_accounts_networks1_idx (networksId ASC),
  CONSTRAINT fk_accounts_networks1
    FOREIGN KEY (networksId)
    REFERENCES networks (id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS networks (
  id INT NOT NULL AUTO_INCREMENT,
  accountsId INT NOT NULL,
  PRIMARY KEY (id),
  INDEX fk_sites_accounts1_idx (accountsId ASC),
  CONSTRAINT fk_sites_accounts1
    FOREIGN KEY (accountsId)
    REFERENCES accounts (id)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

尽管我在网络上有一个 CASCADE DELETE FK 到帐户，我试图明确删除网络以希望绕过外键约束，但是没有成功。

DELETE a, n FROM accounts a INNER JOIN networks n ON n.accountsId=a.id WHERE a.id=123;

我唯一的解决方案是不是像下面这样？

SET FOREIGN_KEY_CHECKS=0;
DELETE FROM networks WHERE accountsId=123;
SET FOREIGN_KEY_CHECKS=1;
DELETE FROM accounts WHERE id=123;

Answer 1

您在两个表之间具有约束相互依赖性。这不是一个好的设计，因为它往往会使 DDL 和 DML 操作比通常需要的更复杂。

除了暂时禁用外键外，执行成功 DELETE 的唯一可能解决方案是使其中一个引用字段可为空，这样您就可以通过在删除前将其设置为 NULL 来打破关系。 ON DELETE CASCADE 将负责删除其他表中的相关记录。

使一个引用字段可为空:

ALTER TABLE networks MODIFY accountsId INT NULL;

删除:

UPDATE network SET accountsId = NULL WHERE id = ?;
DELETE FROM network WHERE id = ?;
-- the corresponding record in table accounts gets automatically deleted.