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 thelatest_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?