互斥的多對多關係
我有一個
containers可以與多個表建立多對多關係的表，假設它們是plants,animals和bacteria. 每個容器可以包含任意數量的植物、動物或細菌，並且每個植物、動物或細菌可以在任意數量的容器中。到目前為止，這非常簡單，但我遇到的問題是每個容器應該只包含相同類型的元素。例如同時包含植物和動物的混合容器在數據庫中應該是違反約束的。
我的原始架構如下：
containers ---------- id ... ... containers_plants ----------------- container_id plant_id containers_animals ------------------ container_id animal_id containers_bacteria ------------------- container_id bacterium_id但是有了這個模式，我想不出如何實現容器應該是同質的約束。
有沒有辦法通過參照完整性來實現這一點，並在數據庫級別確保容器是同質的？
我為此使用 Postgres 9.6。
如果您同意為其引入一些冗餘，則有一種方法可以僅在不改變目前設置的情況下以聲明方式實現這一點。接下來的內容可以被認為是RDFozz 建議的發展，儘管在我閱讀他的答案之前這個想法已經在我腦海中形成（而且它的不同足以保證它自己的答案文章）。
執行
這是你要做的，一步一步：
containerTypes按照 RDFozz 的回答中建議的行創建一個表：CREATE TABLE containerTypes ( id int PRIMARY KEY, description varchar(30) );使用每種類型的預定義 ID 填充它。出於此答案的目的，讓它們與 RDFozz 的範例相匹配：1 代表植物，2 代表動物，3 代表細菌。 2. 添加一
containerType_id列containers並使其不可為空和外鍵。ALTER TABLE containers ADD containerType_id int NOT NULL REFERENCES containerTypes (id);
- 假設該
id列已經是 的主鍵containers，在 上創建唯一約束(id, containerType_id)。ALTER TABLE containers ADD CONSTRAINT UQ_containers_id_containerTypeId UNIQUE (id, containerType_id);這就是裁員的開始。如果
id被聲明為主鍵，我們可以放心它是唯一的。如果它是唯一的，那麼id和另一列的任何組合也必然是唯一的，而無需額外聲明唯一性——那麼，有什麼意義呢？關鍵是通過正式聲明列對是唯一的，我們讓它們是可引用的，即成為外鍵約束的目標，這就是這部分的內容。 4.containerType_id向每個聯結表 (containers_animals,containers_plants, )添加一列containers_bacteria。使其成為外鍵是完全可選的。關鍵是確保所有行的列具有相同的值，每個表的值不同：1 代表containers_plants，2 代表containers_animals，3 代表containers_bacteria，根據 中的描述containerTypes。在每種情況下，您還可以將該值設為預設值以簡化插入語句：ALTER TABLE containers_plants ADD containerType_id NOT NULL DEFAULT (1) CHECK (containerType_id = 1); ALTER TABLE containers_animals ADD containerType_id NOT NULL DEFAULT (2) CHECK (containerType_id = 2); ALTER TABLE containers_bacteria ADD containerType_id NOT NULL DEFAULT (3) CHECK (containerType_id = 3);
- 在每個聯結表中，使這對列
(container_id, containerType_id)成為外鍵約束引用containers。ALTER TABLE containers_plants ADD CONSTRAINT FK_containersPlants_containers FOREIGN KEY (container_id, containerType_id) REFERENCES containers (id, containerType_id); ALTER TABLE containers_animals ADD CONSTRAINT FK_containersAnimals_containers FOREIGN KEY (container_id, containerType_id) REFERENCES containers (id, containerType_id); ALTER TABLE containers_bacteria ADD CONSTRAINT FK_containersBacteria_containers FOREIGN KEY (container_id, containerType_id) REFERENCES containers (id, containerType_id);如果
container_id已定義為對 的引用containers，請隨意從每個表中刪除不再需要的該約束。這個怎麼運作
通過添加容器類型列並使其參與外鍵約束，您準備了一種防止容器類型更改的機制。
containers僅當使用子句定義外鍵時才能更改類型中的類型DEFERRABLE，而在此實現中它們不應該存在。即使它們是可延遲的，更改類型仍然是不可能的，因為在
containers-junction 表關係的另一端存在檢查約束。每個聯結表只允許一種特定的容器類型。這不僅可以防止現有引用更改類型，還可以防止添加錯誤的類型引用。也就是說，如果您有一個類型 2（動物）的容器，您只能使用允許類型 2 的表向其中添加項目，即containers_animals，並且將無法添加引用它的行，例如，containers_bacteria接受只輸入 3 個容器。最後，您自己決定為 、 和 設置不同的表，
plants並為每種實體類型設置不同的聯結表，這已經使容器不可能擁有多種類型的項目。animals``bacteria因此，所有這些因素結合在一起，以純粹的聲明方式確保您的所有容器都是同質的。
一種選擇是將 a 添加
containertype_id到Container表中。使列 NOT NULL 和表的外鍵，該ContainerType表將包含可以放入容器中的每種類型的項目的條目：containertype_id | type -----------------+----------- 1 | plant 2 | animal 3 | bacteria要確保容器類型無法更改，請創建一個更新觸發器來檢查是否
containertype_id已更新，並在這種情況下回滾更改。然後，在容器連結表上的插入和更新觸發器中，根據該表中的實體類型檢查 containertype_id，以確保它們匹配。
如果您放入容器中的任何內容都必須與類型匹配，並且類型無法更改，那麼容器中的所有內容都將是相同的類型。
注意：由於連結表上的觸發器將決定匹配的內容，因此如果您需要一種可以在其中包含植物和動物的容器，您可以創建該類型，將其分配給容器，然後檢查該類型. 因此，如果事情在某些時候發生了一些變化（例如，您獲得類型“雜誌”和“書籍”……），您將保持靈活性。
注意第二點：如果容器發生的大部分事情是相同的，不管它們裡面有什麼，那麼這是有道理的。如果您根據容器的內容髮生了非常不同的事情（在系統中，而不是在我們的物理現實中），那麼 Evan Carroll 為不同的容器類型創建不同的表的想法非常有意義。此解決方案確定容器在創建時具有不同的類型，但將它們保存在同一個表中。如果每次對容器執行操作時都必須檢查類型，並且您執行的操作取決於類型，那麼單獨的表實際上可能更快更容易。