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?

0

2 Answers 2

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.