mysql - 如何更新链接到多个表的 FK - Cascade on Update

Question

我有 3 个相互链接的表

1. 任务
2. 客户
3. 合规性

关系如下图所示

compliance表具有如下外键

task表具有如下外键

问题:当我编辑/更新 clientno在client表，我得到

1452: Cannot add or update a child row: a foreign key constraint fails
(`task`, CONSTRAINT `task_ibfk_1` FOREIGN KEY (`officeid`, `clientid`) REFERENCES `client` (`officeid`, `clientno`) ON UPDATE CASCADE)

我预计当clientno在 client 中更改表，相同的内容将在 complaince 中更新和 task表。

我想我遇到了 InnoDB 引擎的一个已知限制。这不允许对 FK 进行级联更新。如果这是真的，那么用新的 clientno 更新 3 个表的解决方案是什么？ ？

编辑 1: 正如@pankaj 所指出的，如何克服

If ON UPDATE CASCADE recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE operations. This is to prevent infinite loops resulting from cascaded updates.

编辑 2:

create table client
(
  officeid     char(6)                 not null,
  clientno     char(10)                not null,
  fname        varchar(40)             not null,
  primary key (officeid, clientno)
);

create index officeid_clientno
  on client (officeid, clientno);


create table compliance
(
  officeid    char(6)                   not null,
  id          smallint(5) unsigned      not null,
  clientid    char(10)                  not null,
  primary key (officeid, id),
  constraint compliance_ibfk_2
  foreign key (officeid, clientid) references client (officeid, clientno)
    on update cascade
    on delete cascade
);

create index officeid_clientid
  on compliance (officeid, clientid, id);


create table task
(
  officeid        char(6)                      not null,
  taskno          char(10)                     not null,
  clientid        char(10)                     not null,
  taskname        varchar(50)                  not null,
  complianceid    smallint(5) unsigned         null,
  primary key (officeid, taskno),
  constraint task_ibfk_1
  foreign key (officeid, clientid) references client (officeid, clientno)
    on update cascade,
  constraint task_ibfk_4
  foreign key (officeid, clientid, complianceid) references compliance (officeid, clientid, id)
    on update cascade
);

create index officeid_clientid_complianceid
  on task (officeid, clientid, complianceid);

仅供引用:我在 mariadb 10.3 和 mysql 8.0 中试过

Answer 1

问题与声明关系的方式有关。

首先，正如@Nick 所评论的，task 之间不需要关系。和 client , 因为这已经包含在与 compliance 的关系中.注释这个多余约束的声明足以使错误消失，如您在 this db fiddle 中所见。 .

create table task
(
  officeid        char(6)                      not null,
  ...
  primary key (officeid, taskno),
  -- constraint task_ibfk_1
   -- foreign key (officeid, clientid) references client (officeid, clientno)
   -- on update cascade,
  constraint task_ibfk_4
  foreign key (officeid, clientid, complianceid) references compliance (officeid,     clientid, id)
    on update cascade
); 

另一个建议是在所有表中使用自动递增的主键(您可以使用 UNIQUE 索引来强制执行复合引用完整性规则)。这是处理 MySQL 的最常用方法，使用它处理关系非常简单。