Horton
Menu

Re: Self referential foreign keys in partitioned table not working as expected

Christoph Berg <myon@debian.org>

View thread
From: Christoph Berg <myon@debian.org>
To: Luca Vallisa <luca.vallisa@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Date: 2025-04-01T10:18:30Z
Lists: pgsql-bugs, pgsql-hackers 
Re: Luca Vallisa
> create table test (
>     id_1 int4 not null,
>     id_2 int4 not null,
>     parent_id_1 int4 null,
>     primary key (id_1, id_2),
>     foreign key (parent_id_1, id_2) references test(id_1, id_2)
> ) partition by list(id_1);
> 
> insert into test values (1, 1, null), (1, 2, 1);

Multi-column foreign keys where one column is NULL effectively disable
the FK, this is not specific to partitioned tables. They works as
designed, but best avoid them.

Christoph

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Handle self-referencing FKs correctly in partitioned tables

  2. Restructure foreign key handling code for ATTACH/DETACH

  3. Fix self-referencing foreign keys with partitioned tables