尽管外键中存在 ON UPDATE CASCADE，MySQL 仍不允许更新

Question

我在 MySQL 上遇到了一个非常奇怪的问题，其中 ON UPDATE CASCADE 的行为就像 ON UPDATE RESTRICT。

我有两台 MySQL 服务器，一台旧的 Windows Server 2012 和一台新的 Ubuntu 16.04 LTS。

在两台服务器上，我都有三个表——item、invoice 和invoice_box

CREATE TABLE `item`  (
  `id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `idInvoice` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `boxNumber` tinyint(4) NULL DEFAULT NULL,

  CONSTRAINT `fk_gauge_item_invoice` FOREIGN KEY (`idInvoice`) REFERENCES `invoice` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_gauge_item_invoice_box` FOREIGN KEY (`idInvoice`, `boxNumber`) REFERENCES `invoice_box` (`idInvoice`, `boxNumber`) ON DELETE SET NULL ON UPDATE CASCADE
)

CREATE TABLE `invoice_box`  (
 `idInvoice` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
 `boxNumber` tinyint(4) NOT NULL,

  CONSTRAINT `fk_gauge_box_invoice` FOREIGN KEY (`idInvoice`) REFERENCES `invoice` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `invoice`  (
 `id` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
)

在我的旧 Windows 服务器上，如果我更新发票 ID，更新会成功级联到 item 和 Invoice_box 表。

在 Ubuntu 服务器上，只有在以下情况下才有效

1. 如果item表中没有条目，则它会正确级联到invoice_box表。
2. 如果invoice_box表中没有条目，则它会正确级联到item表。

3. 如果 item 和 invoice_box 表中都有条目，则会失败并显示以下消息。

   1452 - 无法添加或更新子行；外键约束失败(product.item CONSTRAINT fk_item_invoice FOREIGN KEY (idInvoice) REFERENCES Invoice(id) ON DELETE SET NULL ON UPDATE CASCADE)

我不明白为什么这在两台服务器上的工作方式不完全相同。我从 Windows 服务器进行了转储以迁移到 Ubuntu 服务器，如果这有什么区别的话。

Answer 1

代码的工作方式与删除fk_gauge_item_invoice后的代码相同

drop table if exists i;
drop table if exists ib;
drop table if exists inv;
CREATE TABLE `inv`  (
 `id` varchar(11)  CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
 primary key (id)
);

CREATE TABLE `ib`  (
 `idInvoice` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
 `boxNumber` tinyint(4) NOT NULL,
  key ib1(idinvoice,boxnumber),
  CONSTRAINT `fk_gauge_box_invoice` FOREIGN KEY (`idInvoice`) REFERENCES `inv` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE `i`  (
  `id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `idInvoice` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `boxNumber` tinyint(4) NULL DEFAULT NULL,

  #CONSTRAINT `fk_gauge_item_invoice` FOREIGN KEY (`idInvoice`) REFERENCES `inv` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_gauge_item_invoice_box` FOREIGN KEY (`idInvoice`, `boxNumber`) REFERENCES `ib` (`idInvoice`, `boxNumber`) ON DELETE SET NULL ON UPDATE CASCADE
);

insert into inv values (1),(2),(3);
insert into ib values (1,1),(1,2),(2,1),(2,2),(2,3);
insert into i values (1,1,1),(1,1,2),(2,1,1),(2,2,2),(2,2,3);

MariaDB [sandbox]> update inv set id = 10 where id = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> select * from inv;
+----+
| id |
+----+
| 10 |
| 2  |
| 3  |
+----+
3 rows in set (0.00 sec)

MariaDB [sandbox]> select * from ib;
+-----------+-----------+
| idInvoice | boxNumber |
+-----------+-----------+
| 10        |         1 |
| 10        |         2 |
| 2         |         1 |
| 2         |         2 |
| 2         |         3 |
+-----------+-----------+
5 rows in set (0.00 sec)

MariaDB [sandbox]> select * from i;
+----+-----------+-----------+
| id | idInvoice | boxNumber |
+----+-----------+-----------+
| 1  | 10        |         1 |
| 1  | 10        |         2 |
| 2  | 10        |         1 |
| 2  | 2         |         2 |
| 2  | 2         |         3 |
+----+-----------+-----------+
5 rows in set (0.00 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> update ib set idinvoice = 20 where idinvoice = 10;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`sandbox`.`ib`, CONSTRAINT `fk_gauge_box_invoice` FOREIGN KEY (`idInvoice`) REFERENCES `inv` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

所以我不清楚你的问题是什么 - 如果你可以设置一个 sql fiddle 来演示你的问题那么会有帮助。