MySQL使用索引二級columum的AUTO_INCREMENT，同時使用FOREIGN KEY

我想創建一對具有以下結構的表：

CREATE TABLE tableA (
   ext_id bigint NOT NULL,
   local_id int AUTO_INCREMENT NOT NULL, /* <-- Requires MyISAM (InnoDB doesn't support this) */
   PRIMARY KEY (ext_id, local_id),
   );

CREATE TABLE tableB (
   ext_id bigint NOT NULL,
   local_id int NOT NULL,
   CONSTRAINT FOREIGN KEY(ext_id, local_id) REFERENCES tableA(ext_id, local_id) ON DELETE CASCADE ON UPDATE CASCADE /* <-- Requires InnoDB (MyISAM doesn't support this) */
   );

然而，正如評論中所述，這些特性根據支持它們的數據庫引擎是互斥的，即 InnoDB 不支持 tableA 上 local_id 的 auto_increment，而 MyISAM 不支持 tableB 中引用 tableA 的外鍵。

我正在尋找一種方法來模擬其中一個數據庫引擎上缺少的功能。

我可以使用觸發器來模擬外鍵的 ON DELETE/UPDATE CASCADE 部分，但我不相信這會在插入到 tableB 時強制 tableA 中存在 (ext_id, local_id) 。除此之外，我不知道如何解決這個問題。

經過進一步的研究和一些實驗，我找到了一種使用 MyISAM 中的觸發器模擬外鍵的方法

CREATE TRIGGER tableB_before_insert BEFORE INSERT ON tableB FOR EACH ROW BEGIN
   IF NOT EXISTS(SELECT * FROM tableA WHERE ext_id = NEW.ext_id AND local_id = NEW.local_id) THEN
       SIGNAL SQLSTATE '40000' SET MESSAGE_TEXT = "Entry dosen't exist in tableA";
   END IF;
END

CREATE TRIGGER tableB_before_update BEFORE UPDATE ON tableB FOR EACH ROW BEGIN
   IF NOT EXISTS(SELECT * FROM tableA WHERE ext_id = NEW.ext_id AND local_id = NEW.local_id) THEN
       SIGNAL SQLSTATE '40000' SET MESSAGE_TEXT = "Entry dosen't exist in tableA";
   END IF;
END

CREATE TRIGGER tableA_after_delete AFTER DELETE ON tableA FOR EACH ROW BEGIN
   DELETE FROM tableB WHERE ext_id = OLD.ext_id AND local_id = OLD.local_id;
END

CREATE TRIGGER tableA_after_update AFTER UPDATE ON tableA FOR EACH ROW BEGIN
   UPDATE tableB SET ext_id = NEW.ext_id, local_id = NEW.local_id WHERE ext_id = OLD.ext_id AND local_id = OLD.local_id;
END

注意：觸發器需要在 tableB 上的觸發器之前觸發，以便應該是 40xxx 的信號將中止操作，MESSAGE_TEXT 可以是錯誤消息中顯示的文本。雖然觸發器需要在 tableA 上觸發這種方式，如果操作失敗，觸發器不會被呼叫，正確的語法'tableB field name' = OLD.'tableA field name'在 where 子句中，但是NEW在 set 子句中搜尋 tableA 中行的原始值並刪除或更新新值。

我在這裡討論這個問題，包括幾個解決方法。

簡單的解決方法是local_id保留AUTO_INCREMENT並放棄對從 開始的值的要求1。

請記住，這ROLLBACK 可能會導致您失去 ID。

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