同一表中不同列的外鍵兩次？

伺服器：MariaDB 10.3.21

客戶端：MariaDB 10.4.12

給定以下結構/數據：

DROP TABLE IF EXISTS `main`;
CREATE TABLE `main` (
 `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
 `description` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

LOCK TABLES `main` WRITE;
INSERT INTO `main` VALUES ('bar','this is another test');
INSERT INTO `main` VALUES ('foo','this is a test');
UNLOCK TABLES;

DROP TABLE IF EXISTS `referenceData`;
CREATE TABLE `referenceData` (
 `primaryName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
 `secondaryName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
 PRIMARY KEY (`primaryName`),
 KEY `referenceData_FK_1` (`secondaryName`),
 CONSTRAINT `referenceData_FK` FOREIGN KEY (`primaryName`) REFERENCES `main` (`name`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `referenceData_FK_1` FOREIGN KEY (`secondaryName`) REFERENCES `main` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

LOCK TABLES `referenceData` WRITE;
INSERT INTO `referenceData` VALUES ('bar','bar');
INSERT INTO `referenceData` VALUES ('foo','bar');
UNLOCK TABLES;

可以很好地更新/刪除 table 中main的第 1 行，並且 CASCADE 在 table 上按預期執行referenceData。但是，當嘗試更新表中的第 2 行時main（例如 via UPDATE main SET name = 'bar2' WHERE name = 'bar'），將返回以下錯誤：

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`testdb`.`referenceData`, CONSTRAINT `referenceData_FK_1` FOREIGN KEY (`secondaryName`) REFERENCES `main` (`name`) ON DELETE CASCADE ON UPDATE CASCADE)

但是，如果嘗試通過 刪除DELETE FROM main WHERE name = 'bar'，它可以正常工作。

坦率地說，是什麼？為什麼在更新期間兩列的值相同時會出現此錯誤，為什麼刪除不受影響？

DROP TABLE IF EXISTS `main`;
CREATE TABLE IF NOT EXISTS `main` (
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

LOCK TABLES `main` WRITE;
INSERT INTO `main` VALUES ('bar','this is another test');
INSERT INTO `main` VALUES ('foo','this is a test');
UNLOCK TABLES;

DROP TABLE IF EXISTS `referenceData`;
CREATE TABLE IF NOT EXISTS `referenceData` (
  `primaryName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `secondaryName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`primaryName`, `secondaryName`),
  CONSTRAINT `referenceData_FK_primary` FOREIGN KEY (`primaryName`) REFERENCES `main` (`name`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `referenceData_FK_secondary` FOREIGN KEY (`secondaryName`) REFERENCES `main` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

LOCK TABLES `referenceData` WRITE;
INSERT INTO `referenceData` VALUES ('bar','bar');
INSERT INTO `referenceData` VALUES ('foo','bar');
UNLOCK TABLES;

SELECT * FROM main;
SELECT * FROM referenceData;

姓名 | 描述 
:--- | :-------------------
酒吧 | 這是另一個測試
富 | 這是一個測試 

主要名稱 | 次要名稱
:---------- | :------------
酒吧 | 酒吧 
富 | 酒吧 

db<>在這裡擺弄

只referenceData編輯了 DDL - 看。

---

當嘗試更新主表中的第 2 行時（例如，通過

UPDATE main SET name = 'bar2' WHERE name = 'bar')

，返回以下錯誤：

錯誤 1452 (23000)：無法添加或更新子行：外鍵約束失敗 ( testdb. referenceData, CONSTRAINT referenceData_FK_1FOREIGN KEY ( secondaryName) REFERENCES main( name) ON DELETE CASCADE ON UPDATE CASCADE) 如果嘗試通過 DELETE FROM main WHERE name = ’ 進行刪除bar’，但是，它工作正常。

出現此問題的原因是兩者primaryName和secondaryName具有相同值的記錄。級聯更新不是“通過記錄”而是“通過引用”執行的 - 即將執行 2 次單獨的級聯操作嘗試。並且當（通過一個欄位）執行第一個級聯操作時，對另一個欄位的檢查失敗（main表有一個值，而編輯的記錄需要兩個不同的值——舊的和新的——同時）。而且我沒有看到解決這個問題的方法（除了使用 insert into main- update referenceData- remove from main）……

引用自：https://dba.stackexchange.com/questions/261245