從多個表中級聯刪除

我有一個相當標準的餐廳模型，restaurant在 PostgreSQL 數據庫中有一張桌子。所有餐廳都有評級（由和組成） average，但為了避免重複這種評級模式，例如，我將它們移動到單獨的表中並且只儲存在.vote_count``vote_sum``pictures``rating``rating_id``restaurant

我知道 1 個評分只會被整個數據庫中的另外 1 行使用。刪除orrating中的行時，如何將刪除級聯到？restaurant``picture

我一直在環顧四周，但我能找到的只是設置 a REFERENCES，但為此我需要知道哪些表數據將被刪除。

我知道觸發器可以完成這項工作，我只是希望有更優雅的東西。

一些澄清：

• REFERENCES**FOREIGN KEY**是用於約束的關鍵字（允許級聯DELETE或UPDATE）。
• 您的數據庫設計似乎存在邏輯缺陷。rating似乎是主表的細節restaurant。由於您有 1:1 的關係，您可以只在主表中包含“評級”列。如果您需要一個單獨的表格，您將restaurant_id在rating表格中包含 a 而不是相反。
• 您的“評級”列average, vote_count and vote_sum表示另一個表vote，這些值是派生的聚合。AMATERIALIZED VIEW將是典型的解決方案。作為單獨rating的表或與每個主表中的列組合……

乾淨的方法是rating為每個主表設置一個單獨的表。然後你可以對每個使用 FK 約束ON DELETE CASCADE。

如果您仍然需要目前的設計，我有兩個想法：

rating1.一張表有多個FK列

當 FK 約束反向指向正確的方向時，表格可能如下所示：

CREATE TABLE rating (
 rating_id  serial PRIMARY KEY
, vote_count int
, vote_sum   int
, average    float8
, restaurant_id int UNIQUE REFERENCES restaurant(restaurant_id)
                   ON UPDATE CASCADE ON DELETE CASCADE
, picture_id int UNIQUE REFERENCES picture(picture_id)
                ON UPDATE CASCADE ON DELETE CASCADE
-- more references to other tables
);

約束強制執行您的UNIQUE1:1 要求。每個主表中的每一行最多可以有一行rating。還會自動創建非常有用的索引。

或者，如果您有多個主表，您可能希望創建部分唯一索引，而不是從每個索引中排除不相關的行：

CREATE UNIQUE INDEX rating_restaurant_id ON rating (restaurant_id)
WHERE restaurant_id IS NOT NULL;
-- etc.

要強制每行只有一個 FK 列是NOT NULL：

ALTER TABLE rating ADD CONSTRAINT exactly_one_fk CHECK (
     (restaurant_id IS NOT NULL)::int
   + (picture_id    IS NOT NULL)::int = 1);  -- add more ...

有幾個主表，可能看起來浪費了很多儲存空間，但實際上並非如此。一堆 NULL 列幾乎沒有任何成本。NULL 儲存非常便宜：

• 大表中的完全空列如何影響性能？

2.繼承

由於您的動機是to avoid repeating this rating schema，因此繼承可能是您的一個很好的解決方案。表繼承的一個限制是父表的外鍵不會被繼承 - 這對您的情況來說是一個優勢：

CREATE TABLE rating (
 rating_id  serial PRIMARY KEY
, vote_count int
, vote_sum   int
, average    float8
);

CREATE TABLE restaurant_rating (
  restaurant_id int PRIMARY KEY REFERENCES restaurant(restaurant_id)
                                ON UPDATE CASCADE ON DELETE CASCADE
) INHERITS (rating);

CREATE TABLE picture_rating (
  picture_id int PRIMARY KEY REFERENCES picture (picture_id)
                             ON UPDATE CASCADE ON DELETE CASCADE
) INHERITS (rating);

-- more?

• 現在您只需定義一次基本模式並從中繼承。
• 對於帶有單個附加序列的所有評級，您仍然有一個共同的 PK 列。
• 每個子表都將主表的 ID 添加為 PK 和 FK，從而強制執行您的 1:1 關係並自動提供列上的重要索引。
• 您可以查詢主表以一次獲取所有評級。如果您需要知道每行來自哪個子表：

SELECT tableoid::regclass::text AS origin, *
FROM   rating;

• 獲取表的所有分區名稱
• 從多個模式中檢索所有記錄

• 您可能想要添加規則或觸發器以禁止直接插入主表rating。

SQL小提琴。

有關的：

• 在繼承的表上使用觸發器來替換外鍵
• 數據庫設計——兩個項目應該共享同一張表嗎？

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