外鍵處理錯誤

如何NULL在收到外鍵約束錯誤時設置 FK 值？

考慮一個簡單的表 a

CREATE TABLE test
(
   id int(11) NOT NULL AUTO_INCREMENT
   ex_key int(11),
   FOREIGN KEY(ex_key) REFERENCES ex_table(ex_key) ON DELETE SET NULL,
   PRIMARY KEY (id)
)

如果接收到無效的 FK 錯誤ex_key

INSERT INTO test (id, ex_key) VALUES ('22', '22');

我們可以通過設置NULL外鍵的值來插入

INSERT INTO test (id, ex_key) VALUES ('22', NULL);

它可以通過一個IF語句來完成，但是有沒有辦法在同一個查詢中這樣做？

我希望找到一個類似於可ON DUPLICATE KEY UPDATE用於UNIQUE列的解決方案。

因此，當您使用無效的 ex_key 值（不在被引用表的被引用列中的值）插入一行到 test 時，您希望該插入成功，而不是失敗，並且您希望將無效值插入為 NULL而不是您嘗試插入的值。正確的？

如果 ex_key 不是合法值，則“插入前”觸發器應該允許您通過查找並將 ex_key 的值更改為 NULL 來完成此操作。

當然，處理任何觸發器都會產生一些成本，但如果您需要此功能，這可能是最輕鬆的方式。

此外，如果您在此表上已經有一個“插入前”觸發器，您只需將 IF … END IF 塊添加到現有觸發器的主體。

DELIMITER //

CREATE TRIGGER test_bi BEFORE INSERT ON test FOR EACH ROW
BEGIN
   IF (SELECT ex_key FROM ex_table WHERE ex_table.ex_key = NEW.ex_key) IS NULL THEN
       SET NEW.ex_key = NULL;
   END IF;
END;

//

DELIMITER ;

我用 MySQL 5.5.27 建構了一個概念證明，這裡：http ://sqlfiddle.com/#!2/d0a8e/1/0

注意，這也完全防止了外鍵錯誤的發生，因為在 InnoDB 根據外鍵約束檢查值的有效性之前，觸發器將 NEW.ex_key 的傳入值替換為 NULL（在適當的時候）。

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