Self-referential record, potentially referencing the same row, with a uuid pkey and a not null and foreign key constraint?

Question

Scenario

A table holds records that represent a chain (or, more accurate yet, any number of independent chains, respectively).
In addition to referencing the direct ancestor in the chain, a record should also reference the first record inserted, marking the beginning of a particular chain.

Problem Statement

• the table is only ever inserted into, the user writing to it does not have update privileges
• ideally, the column holding the reference to the first record of a chain would be a self-referential foreign key as well as not null constraint
• the primary key is a uuid, hence potential solutions accessing the latest_value() of a sequence (serial) do not apply

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
);

Question

In modern postgres (14), can this be done?
Does a way exist to insert a new immutable record, that references itself, in a single insert statement, while keeping all constraints in place?

Answer 1

You can use a data modifying common table expression:

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;

Answer 2

In additionally to @a_horse_with_no_name's answer.

From the description, I infer that you want to store an hierarchical structure (tree) using the transitive closure model, i.e. storing not only the direct ancestor but also the top ancestor.

A naive implementation would be using 2 foreign key constraints:

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
);

The above however does not forbid storing non-tree structures, i.e. a cycle with: dbfiddle.uk (bad)

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 ;

---

A different approach would be to create a two-column constraint where besides every record referencing the direct ancestor, it ensures that both (the record and its direct ancestor) have the same top-ancestor.
See fiddle.uk (good):

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)
);

(Correction. The above does not forbid cycles either. It only ensures that all items that are connected have the same top-ancestor (and that can even be in a different connected component): https://dbfiddle.uk/Wx--mXHR
I'll update with a more restrictive design later.)