檢查鍵是否存在於其他沒有 fk 約束的表中

可以說我有：

table a (id_a int);
table b (id_b int, id_a int);

我想在插入“b”中的新行之前檢查“a”中是否存在 b.id_a。通常我只會在 b.id_a 上添加一個 fk 約束。問題是我不允許將 pk 或 uq 約束添加到“a.id_a”以引用 b.id_a 上的 fk。

如果有人為我提供解決方案，那就太好了。

編輯：為什麼不使用 fk 約束 - 我的模型由一個帶有空間表和簡單 postgres 表的 ESRI Arc SDE 組成。一些 pg 表引用了一個 sde uq 標識符列。預設情況下，SDE 不會向 postgres 中的空間表添加 pk（在 sql server 中它不會問我為什麼）。但是，如果 SDE 預設添加一個 pk，它根本不會幫助我，因為我必須將其更改為另一個 uq 列，因為預設的 pk 列鍵將來可能會更改（= WTF）。ATM 我剛剛將 SDE 表的 pk 添加或更改為我的 uq 標識符列，但是由於我在恢復數據庫時一開始遇到錯誤（現在它暫時有效），ESRI 支持告訴我我更改或添加了 pk列可能會導致這些錯誤，或者將來可能會導致一些錯誤（因為 SDE 不會知道這個 pk）。在我看來，pk 應該無關緊要（因為 SDE 不會知道也不會使用它）這就是為什麼我添加它 atm 並且它似乎可以工作。但是對於未來，我想安全一點，所以我必須找到另一種可能性。

我想要的是：擁有與 fk 約束相同的行為會很好 - 但我想類似的行為是不可能的，因為這就是 fks 所做的。

我曾嘗試編寫一個插入前觸發器，但我認為這是錯誤的方法，因為我似乎總是必須返回 new.row 並且我不希望在檢查要插入的值時插入一些東西不要’存在。

我也可以簡單地不使用任何關係或檢查 fk，但我認為這將是在 tbl 中有錯誤值的高風險。

謝謝你幫我…

可能的解決方案：

我試圖添加一個 fk 約束，它在具有 uq 索引的列上引用並且它可以工作（感謝 ypercube）。我查看了文件，但我找不到有關在僅具有 uq 索引（無 pk 或 uq 約束）的列上添加 fk 約束引用的資訊。至少我在一個教程中找到了一個可能的解釋，它說

唯一約束實際上在 PostgreSQL 中實現為唯一索引，就像在許多數據庫中一樣。

但是也

該索引不是外鍵的理想候選者，但它確實說明瞭如何創建可以使用外鍵引用的唯一索引。

我只是不明白為什麼它不應該是 fk 約束的“好候選”，當在該列上使用 uq 約束或 uq 索引授予您唯一值時。

來自文件 pg uq 約束：

唯一約束確保一列或一組列中包含的數據對於表中的所有行都是唯一的。

來自文件 pg uq 索引：

使系統在創建索引時（如果數據已存在）和每次添加數據時檢查表中的重複值。嘗試插入或更新會導致重複條目的數據將產生錯誤。

我同意 Aaron 的觀點，即不創建 PK（或唯一約束）是一個非常奇怪的要求

但你可以這樣做：

insert into b (id_b, id_a)
select 1, 2 
where exists (select 1 from a where id_a = 2);

或者：

insert into b (id_b, id_a)
select 1, id_a 
from a where id_a = 2

如果要插入多行，可以使用 values 子句：

insert into b (id_b, id_a)
select * 
from (
  values 
     (1,2), (2,3), (3,4)
) as t(id_b, id_a) 
where exists (select 1  
             from a 
             where a.id_a = t.id_a);

---

這只會確保此特定插入將插入正確的數據。它不會阻止不遵循此模式的其他人插入無效數據。並且在不同事務同時執行插入的多使用者環境中是不安全的**。**

問題是我不允許將 pk 或 uq 約束添加到“a.id_a”以引用 b.id_a 上的 fk。

如果該列是唯一的，那麼您遇到的是管理問題而不是技術問題。任何技術解決方案都不是最理想的，因為不允許您使用為此目的而設計的最佳方法。

您可以在業務邏輯層中處理它，但這是有風險的：第一段程式碼沒有執行檢查，沒有正確執行（是否都確保設置了正確的隔離級別？），或者有阻止檢查工作的其他一些錯誤是可能會破壞該數據或導致死鎖的第一段程式碼，這可能會導致使用者出現功能錯誤。

另一種選擇是在每個表上使用觸發器來進行相關檢查並根據需要引發錯誤，但是如果您無法添加唯一約束，那麼您不太可能能夠添加觸發器。如果您能夠添加觸發器，那麼在該列上沒有索引的情況下效率會非常低，並且如果您不允許添加唯一約束（由於空間或寫入器速度問題），那麼您將無法添加索引。這仍然是在您自己的業務邏輯中處理它，而不是讓內置的數據庫構造完成所有操作或您（重新發明輪子），但至少這意味著您只需在一個地方而不是每個地方實現登錄曾經在受影響的表中添加/更新/刪除行。

如果列中的值不應該是唯一的，那麼情況會發生一些變化- 您不能將列聲明為唯一的主鍵，而不是簡單地不允許這樣做，因此不能對相關項使用外鍵約束桌子。在這種情況下，觸發器選項可能很有用，儘管您需要一個覆蓋列的非唯一索引以使觸發器完全有效。

當然，如果值不是唯一的，那麼可能存在建模問題 - 您是否需要使用複合鍵/索引並將 fk 關係連結到那個？

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