如何保證所有表格中的庫存編號都是唯一的？

我有各種需要清點的物理對象，每種類型的對像都有自己的表。對於兩個範例對象，讓我們對包含要盤點的不同劍的桌子使用“劍”，對不同的黃油使用“黃油”。顯然，這兩個對像在屬性方面幾乎沒有任何共同點，只是它們都需要在清單中進行說明。這就是為什麼他們每個人都會有自己的桌子。

我需要為每個庫存對象分配一個唯一的整數 ID。因此，庫存編號“5”可能是一把劍，而“6”可能是黃油。

我相信我需要的是一個單獨的表，它只跟踪庫存編號“inv_nums”。然後我會有一個外鍵約束在每個“劍”和“黃油”表中引用該表。對象表和“inv_nums”表之間的關係是 1:1。

如何保證庫存編號不會被多個對象表無意或以其他方式使用？

我在想也許我需要在呼叫函式或過程（不確定哪個）的插入上使用觸發器來創建並返回下一個連續的庫存編號，或者確保插入語句中提供的那個不在使用（它在“inv_num”表中尚不存在）。

我在正確的軌道上嗎？我確信在某個地方一定有一個設計模式，但我的Google搜尋到目前為止還不是很成功。我當然可以在應用程序層完成所有這些，但我寧願在數據庫本身中強制執行。

我正在 MySQL/MariaDB 中實現它，但我認為無論（關係）數據庫如何，這背後的概念都應該是相同的。

首先在包含AUTO_INCREMENT PRIMARY KEY. 使用 獲取 id LAST_INSERT_ID()。

然後將您需要的任何行插入到其他表中。使用第一步中的 id 作為唯一 id。

這適用於 MySQL 和 MariaDB。其他數據庫有 a 的概念SEQUENCE。這可以在 MySQL/MariaDB 中模擬，主要是使用我給你的“第一步”。因此，如果您真的需要在某種程度上獨立於數據庫，請從SEQUENCE仿真開始。

（我的觀點：RDBMS 供應商之間存在如此多的差異，以至於試圖為所有人提供一個程式碼是愚蠢的。）

下面的程式碼是一個（簡化的）範例，說明了我如何滿足為每種物品類型（例如槍支、黃油等）提供單獨表格的要求，並且每個物品，無論類型如何，都有一個唯一的庫存編號分配給它。即使項目位於不同的表中，項目也不得共享相同的庫存編號。

該解決方案遵循繼承模型，在 ER 建模術語中稱為泛化/專業化。在 OOP 中，您可以將其稱為類/子類或父/子模型。我專門為此解決方案使用“類表繼承”。

我從一張item_types桌子開始。這個type_id表中的 也可以認為是表id；它本質上是跟踪已將庫存編號分配給哪個表。我插入的範例數據將為所有槍支分配一個 type_id 1，為所有黃油分配一個 2。

CREATE TABLE `item_types` (
 `type_id` INT NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(10) NOT NULL,
 PRIMARY KEY (`type_id`)
) ENGINE = InnoDB;

INSERT INTO `item_types` (`name`) VALUES ('guns');
INSERT INTO `item_types` (`name`) VALUES ('butters');

使用 OO 術語，該items表是所有項目的“子級”的父級“類”。請注意此表中的複合索引，它將在子表中發揮作用。

CREATE TABLE `items` (
 `inv_num` INT NOT NULL AUTO_INCREMENT,
 `type_id` INT NOT NULL,
 PRIMARY KEY (`inv_num`),
 CONSTRAINT `fk_items_item_types`
 FOREIGN KEY (`type_id`)
 REFERENCES `item_types`.`type_id`,
 INDEX `idx_inv_num_type_id` (`inv_num`, `type_id`)
) ENGINE = InnoDB;

為每個項目類型創建的items表通過讓它們與 共享inv_num主鍵而成為該表的子表items。這確保了該inv_num值必須存在，items然後才能在此處分配。inv_num通過使用and創建複合外鍵type_id，我們現在可以控制允許哪個表使用表中的inv_num值items。這樣可以保證同一個inv_num值不能同時出現在guns表和butters表中。請注意，InnoDB 要求復合外鍵中的引用列在父級中建立索引。

CREATE TABLE `guns` (
 `inv_num` INT NOT NULL,
 `type_id` INT NOT NULL DEFAULT 1,
 `name` VARCHAR(20) NOT NULL,
 PRIMARY KEY (`inv_num`),
 CONSTRAINT `fk_guns_items`
 FOREIGN KEY (`inv_num`, `type_id`)
 REFERENCES `items` (`inv_num`, `type_id`)
) ENGINE = InnoDB;

CREATE TABLE `butters` (
 `inv_num` INT NOT NULL,
 `type_id` INT NOT NULL DEFAULT 2,
 `name` VARCHAR(20) NOT NULL,
 PRIMARY KEY (`inv_num`),
 CONSTRAINT `fk_butters_items`
 FOREIGN KEY (`inv_num`, `type_id`)
 REFERENCES `items` (`inv_num`, `type_id`)
) ENGINE = InnoDB;

此時，仍然有可能將組合inv_num+輸入type_id到錯誤的表中。例如，庫存編號為 3，在butters表中鍵入 id 為 1（代表槍支）。要強制type_id在特定表中只允許特定的內容，該表必須在插入數據時執行 CHECK。不幸的是，MySQL 不支持 CHECK 操作，因此您必須使用 TRIGGER 代替。

DELIMITER //
CREATE TRIGGER trg_guns_ins
 BEFORE INSERT ON guns
 FOR EACH ROW
BEGIN
 DECLARE msg VARCHAR(128);
 IF NEW.type_id != 1 THEN
   SET msg = concat('trg_guns_ins error: Trying to insert wrong card_type'
                    'value in guns: ',
                    cast(NEW.type_id as char));
   SIGNAL SQLSTATE '45000'
   SET MESSAGE_TEXT = msg;
 END IF;
END //
DELIMITER ;

DELIMITER //
CREATE TRIGGER trg_butters_ins
 BEFORE INSERT ON butters
 FOR EACH ROW
BEGIN
 DECLARE msg VARCHAR(128);
 IF NEW.type_id != 2 THEN
   SET msg = concat('trg_butters_ins error: Trying to insert wrong card_type'
                    'value in butters: ',
                    cast(NEW.type_id as char));
   SIGNAL SQLSTATE '45000'
   SET MESSAGE_TEXT = msg;
 END IF;
END //
DELIMITER ;

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