主页 / dba / 问题 / 316992
Accepted
Johannes Pille
Asked: 2022-09-17 03:44:33 +0800 CST

自引用记录，可能引用同一行，具有 uuid pkey 和非 null 和外键约束？

  • 772

设想

一个表包含代表一个链（或者，更准确地说，分别是任意数量的独立链）的记录。
除了引用链中的直接祖先之外，记录还应该引用插入的第一条记录，标记特定链的开始。

问题陈述

  • 该表只被插入，写入它的用户没有update权限
  • 理想情况下，包含对链的第一条记录的引用的列将是自引用外键以及 not null约束
  • 主键是 a ，因此访问序列 ( ) 的uuid潜在解决方案不适用latest_value()serial
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语句中引用自身，同时保持所有约束到位？

postgresql foreign-key

2 个回答

  1. Best Answer

    您可以使用数据修改公用表表达式：

    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;
    • 4

  2. 除了@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 稍后
    我将使用更严格的设计进行更新。）

    • 3

相关问题