2

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?

Improve this question
0

2 Answers 2

Reset to default
4

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;
Improve this answer
2
  • 1
    Yes, this is the way to insert in tables with such self-referential FKs. @JohannesPille see my answer for a different issue with the method (that I think) you are using. Commented Sep 16, 2022 at 12:59
  • The transitive closure is beyond the shadow of a doubt highly interesting as well, @ypercubeᵀᴹ. Whether it applies to my case, I have to think about. Thanks for the additional input, +1 Commented Sep 16, 2022 at 13:03
3

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

Improve this answer
0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.