具有附加約束的外鍵？

有一個名為Item(id, name, cost)and的表Orders(id, bill_id, item_id, units)，用於跟踪下達的訂單，其中相同的 bill_id表示它屬於單個訂單。

如果需要將項目作為item_id添加到Order表中，如何在 DB 中施加額外的約束，表明*Item應該是“可用的”（在那個時間點）？*項目手動確定為“可用”，在此場景中無法從數據庫中的其他欄位派生。

一種模式設計（我更喜歡）是添加一個具有“可用”和“不可用”欄位的類型列。但是如何檢查外鍵約束item_id不僅應該是Item表中的主鍵，它的類型也應該是“可用”？

這個Stack Overflow使用檢查約束的答案似乎很接近，但這是唯一的方法嗎？我覺得這對於 RDBMS 來說是一件微不足道的事情，還是這不是一個規範化的數據？

另一種模式設計（我不喜歡）是有一個名為“菜單”的表，它只能有可用的項目*。*問題是這張桌子在本質上會變得非常動態，並且它會根據物品的可用性不斷變化。而且，我只是根據其狀態從 Items 中創建一個子表，這似乎不是一個好主意。

以程式方式很容易做到這一點；但是，我如何在 RDBMS 中實現這一點？我喜歡數據庫足夠智能來處理這個問題的想法。

**原來的答案是錯誤的！**有關更正的版本，請參見編輯 1。

---

原始答案

一個了不起的解決方案需要視圖或繼承表中的列的外鍵，但不幸的是 PostgreSQL（我想那是你的 RDBMS，因為標籤）沒有這個（還）。

我認為對組織數據的方式進行簡單的更改就足夠了：創建一個類似ItemsAvailableQuantity的表，將Item與其可用性連接起來，這將是訂單中的引用。當一個項目不再可用時，DELETE它來自它。

CREATE TABLE Item (
   id   serial  NOT NULL
 , name text    NOT NULL
 , cost numeric

 , PRIMARY KEY (id)
 , CONSTRAINT positive_cost
       CHECK (cost > 0)
   );

CREATE TABLE ItemAvailableQuantity (
   id       serial  NOT NULL
 , item_id  integer NOT NULL
 , quantity integer NOT NULL

 , PRIMARY KEY (id)
 , FOREIGN KEY (item_id)
       REFERENCES Item (id)
       ON UPDATE CASCADE
       ON DELETE CASCADE
 , CONSTRAINT postive_quantity -- This constraint is the same as
       CHECK (quantity > 0)    -- checking something like `available = TRUE`.
   );

CREATE TABLE ItemOrder (       -- Changed the name from `Order` because
   id      serial  NOT NULL   -- PostgreSQL refuses that name, somehow
 , bill_id integer NOT NULL
 , item_id integer NOT NULL
 , units   integer NOT NULL

 , PRIMARY KEY (id)
 , FOREIGN KEY (item_id)
       REFERENCES ItemAvailableQuantity (id)
       ON UPDATE CASCADE
       ON DELETE CASCADE
-- Uncomment when `Bill` table is ready
--  , FOREIGN KEY (bill_id)
--        REFERENCES Bill (id)
--        ON UPDATE CASCADE
--        ON DELETE CASCADE
 , CONSTRAINT positive_units
       CHECK (units > 0)
   );

**注意！**當您的軟體減少單位並達到 0 時，約束positive_units可能會導致問題。如果需要，請使其類似於CHECK >= 0，或者添加一個觸發器，當每個or上的單位達到 0（或更少）DELETE時自動 -s 行。這將保留表ItemAvailableQuantity以僅包含實際可用的項目，這是我們希望從表ItemOrder中引用的內容。INSERT``UPDATE

這應該可以解決您的問題。這不是您問題的確切答案。這將涉及觸發器或CHECK呼叫您提供的連結中的函式。

然後，要輕鬆查看項目的數量，只需創建一個連接ItemAvailableQuantity和Item的視圖。如果你真的想要那麼，用觸發器使它成為INSERT-able （見黃框警告）。

---

編輯 1

實際上Order（又名ItemOrder）應該引用Item而不是ItemAvailableQuantity以避免在Item目前不可用時出現任何問題，如評論中所述。

這建議我們應該刪除整個表ItemAvailableQuantity並且只在Item上添加一個列available_quantity。

CREATE TABLE Item (
   id                 serial NOT NULL
 , name               text   NOT NULL
 , cost               numeric
 , available_quantity integer NOT NULL

 , PRIMARY KEY (id)
 , CONSTRAINT positive_cost
       CHECK (cost > 0)
 , CONSTRAINT non_negative_quantity
       CHECK (quantity >= 0)
   );

然後，為了確定只將可用項目插入到訂單中，我們可以執行

INSERT INTO ItemOrder (bill_id, item_id, units) VALUES
   (SELECT id FROM Bill WHERE condition = something -- customize at will
   , SELECT id FROM Item WHERE available_quantity >= wanted_quantity
       AND other_condition = something
   , wanted_quantity)
   ;

wherewanted_quantity是您的軟體傳遞給查詢的參數。

仍然，解決了問題，但不是問題的直接答案。

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