強制“兩個表之外”的約束

我在用 SQL 建模電氣原理圖時遇到了一些麻煩。我想捕捉的結構是

 part ←────────── pin
  ↑                ↑
part_inst ←───── pin_inst

其中“inst”是“instance”的縮寫。

例如，我可能有一個帶有1OUT、1IN-、1IN+、GND、2IN+、2IN-、2OUT 和 V CCpart的 LM358 運算放大器。然後我可能會將這部分放在原理圖上，創建 a和 8 s。pin``part_inst``pin_inst

忽略數據欄位，我最初的模式嘗試是

create table parts (
   part_id bigserial primary key
);
create table pins (
   pin_id bigserial primary key,
   part_id bigint not null references parts
);
create table part_insts (
   part_inst_id bigserial primary key,
   part_id bigint not null references parts
);
create table pin_insts (
   pin_inst_id bigserial primary key,
   part_inst_id bigint not null references part_insts,
   pin_id bigint not null references pins
);

這種模式的主要問題是 apin_inst可能與 a 綁定part_inst，part_id=1但它的pinhas part_id=2。

我想在數據庫級別而不是應用程序級別避免這個問題。所以，我修改了我的主鍵來強制執行。我用 標記了更改的行--。

create table parts (
   part_id bigserial primary key
);
create table pins (
   pin_id bigserial,                                          --
   part_id bigint not null references parts,
   primary key (pin_id, part_id)                              --
);
create table part_insts (
   part_inst_id bigserial,                                    --
   part_id bigint not null references parts,
   primary key (part_inst_id, part_id)                        --
);
create table pin_insts (
   pin_inst_id bigserial primary key,
   part_inst_id bigint not null,                              --
   pin_id bigint not null,                                    --
   part_id bigint not null references parts,                  --
   foreign key (part_inst_id, part_id) references part_insts, --
   foreign key (pin_id, part_id) references pins              --
);

我對這種方法的抱怨是它污染了主鍵：在我提到 a 的任何地方part_inst，我都需要跟踪 the part_inst_id和part_id. 有沒有另一種方法可以 在不過於pin_inst.part_inst.part_id = pin_inst.pin.part_id冗長的情況下強制執行約束？

最小的解決方案

一種激進的解決方案可能是pin_inst完全刪除：

 part ←────────── pin
   ↑               ↑
part_inst ←───── pin_inst

您的問題中沒有任何內容表明您實際上需要冗餘表。對於pin關聯到 a的 s part_inst，查看關聯的pins part。

這會將程式碼簡化為：

create table part (    -- using singular terms for table names
   part_id bigserial primary key
);
create table pin (
   pin_id bigserial primary key,
   part_id bigint not null references part
);
create table part_inst (
   part_inst_id bigserial primary key,
   part_id bigint not null references part
);

但你的評論清楚地表明我們不會逃脫懲罰……

pin_inst需要時替代

像您所做的那樣包括part_id是具有外鍵約束的最簡單的解決方案。您不能引用帶有外鍵約束的“兩個表之外”的表。

但是您至少可以在不“污染”主鍵的情況下做到這一點。添加UNIQUE約束。

create table part (
   part_id bigserial primary key
);
create table pin (
   pin_id bigserial primary key,
   part_id bigint not null references part,
   **unique(part_id, pin_id)**         -- note sequence of columns
);
create table part_inst (
   part_inst_id bigserial primary key,
   part_id bigint not null references part,
   **unique(part_id, part_inst_id)**
);
create table pin_inst (
   pin_inst_id bigserial primary key,
   part_inst_id bigint not null,
   pin_id bigint not null,
   part_id bigint not,
   foreign key (part_id, pin_id) references pin,
   foreign key (part_id, part_inst_id) references part_inst
);

我把part_id唯一的約束放在第一位。這與參照完整性無關，但對性能很重要。主鍵已經為 pk 列實現了索引。最好在實現唯一約束的多列索引中首先讓*另一列。*這些相關問題下的詳細資訊：

• PostgreSQL中索引的工作
• 複合索引是否也適用於第一個欄位的查詢？
• 多對多和弱實體

關於 SO 的相關問題：

• 多欄位 UNIQUE 約束可以包含來自多個表的欄位嗎？
• 某些列值位於其他表中的外鍵約束

觸發器的替代方案

您可以使用更靈活的觸發器函式，但更複雜、容易出錯且不那麼嚴格。好處：你可以沒有part_inst.part_id和pin.part_id……

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