自引用記錄，可能引用同一行，具有 uuid pkey 和非 null 和外鍵約束？

設想

表包含表示鏈的記錄。除了引用鏈中的直接祖先之外，記錄還應該引用插入的第一條記錄，標記鏈的開始。

問題陳述

• 該表只被插入，寫入它的使用者沒有UPDATE權限
• 理想情況下，包含對鏈的第一條記錄的引用的列將是自引用外鍵和 not null約束
• 主鍵是 a uuid，因此訪問latest_value()序列的解決方案不適用

create table chained_records (
  id uuid not null default gen_random_uuid(),
  -- in the case, where an inserted record *is* the first of a chain,
  -- this column would reference itself (i.e. the above `id` column) 
  first_in_chain_id uuid not null references chained_records(id),
  -- snip
);

問題

在現代 postgres (14) 中，可以這樣做嗎？

是否存在一種方法來插入一個新的不可變記錄，該記錄在單個insert語句中引用自身，同時保持所有約束到位？

您可以使用數據修改公用表表達式：

with new_id (id) as (
 values (gen_random_uuid())
)
insert into chained_records (id, some_text, some_number, first_in_chain_id)
select id, 'some text', 42, id
from new_id;

除了@a_horse_with_no_name 的回答。

從描述中，我推斷您想使用傳遞閉包模型儲存層次結構（樹），即不僅儲存直接祖先，還儲存頂級祖先。

一個簡單的實現將使用 2 個外鍵約束：

create table chained_records (
  id uuid not null primary key default gen_random_uuid(),
  -- direct ancestor FK
  direct_ancestor_id uuid not null references chained_records(id),
  -- in the case, where an inserted record *is* the first of a chain,
  -- this column would reference itself (i.e. the above `id` column) 
  first_in_chain_id uuid not null references chained_records(id),
  -- snip
);

然而，上述內容並不禁止儲存非樹結構，即帶有以下內容的循環：dbfiddle.uk（壞）

with new_id (aid, bid) as (
 values (gen_random_uuid(), gen_random_uuid())
)
insert into chained_records (id, direct_ancestor_id, first_in_chain_id)
select aid, bid, aid from new_id
union all
select bid, aid, bid from new_id ;

---

另一種方法是創建一個兩列約束，其中除了引用直接祖先的每條記錄外，它還確保兩者（記錄及其直接祖先）具有相同的頂級祖先。

見fiddle.uk（好）：

create table chained_records (
  id uuid not null primary key default gen_random_uuid(),
  -- direct ancestor
  direct_ancestor_id uuid not null,
  -- top ancestor
  first_in_chain_id uuid not null,

  -- UNIQUE constraint needed for the following FK
  constraint transitive_closure_UQ
  unique 
      (id, first_in_chain_id),

  -- transitive closure FK
  constraint transitive_closure_FK 
  foreign key 
      (direct_ancestor_id, first_in_chain_id)
  references chained_records
      (id, first_in_chain_id)
);

（更正。上述內容也不禁止循環。它只確保所有連接的項目具有相同的頂級祖先（甚至可以在不同的連接組件中）：https ://dbfiddle.uk/Wx– mXHR 稍後

我將使用更嚴格的設計進行更新。）

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