Postgres Pro
Downloads
Home   >   mailing lists

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

Self referential foreign keys in partitioned table not working as expected

From
Luca Vallisa
Date:
As mentioned at https://www.postgresql.org/message-id/18156-a44bc7096f0683e6%40postgresql.org this is a regression introduced in version 15.X and still present in 17.4.

I'm running the postgres:17.4-alpine docker official image.

-------------------------------------------------------------------------------------
REPRO
-------------------------------------------------------------------------------------

drop table if exists test;

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

create table test_1 partition of test for values in (1);
create table test_9 partition of test for values in (9);

insert into test values (1, 1, null), (1, 2, 1);

-- doesn't trigger an error
-- delete from test where (id_1, id_2) = (1, 1);

-- doesn't trigger an error
-- update test set id_1 = 9 where (id_1, id_2) = (1, 1);

-------------------------------------------------------------------------------------

Both deletion and update runs smoothly.
Also, potential cascade delete and cascade update rules are ignored.





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

From
Christoph Berg
Date:
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

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

From
Luca Vallisa
Date:
Thanks for the reply.

I've realized I messed up with the script.

Please refer to the following one.

---------------------------------------------------------------------------------------

drop table if exists test;
create table test (
    id_1 int4 not null,
    id_2 int4 not null,
    parent_id_2 int4 null,
    primary key (id_1, id_2),
    foreign key (id_1, parent_id_2) references test (id_1, id_2)
)
--partition by list (id_1); /** uncomment this line **/
--create table test_1 partition of test for values in (1); /** uncomment this line **/
--create table test_9 partition of test for values in (9) /** uncomment this line **/
;

insert into test values (1, 1, null), (1, 2, 1);
delete from test where (id_1, id_2) = (1, 1);
--update test set id_2 = 9 where (id_1, id_2) = (1, 1);
select * from test;
drop table if exists test;

---------------------------------------------------------------------------------------

The provided version throws an error.
If you uncomment the 3 lines (working with partition) the error is not thrown during the delete (or the update).

Luca

Il giorno mar 1 apr 2025 alle ore 12:18 Christoph Berg <myon@debian.org> ha scritto:
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

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

From
Christoph Berg
Date:
Re: Luca Vallisa
> The provided version throws an error.

Ok, I can confirm this.

This throws an error like it should:

create table test (
    id_1 int4 not null,
    id_2 int4 not null,
    parent_id_2 int4 null,
    primary key (id_1, id_2),
    foreign key (id_1, parent_id_2) references test (id_1, id_2)
);
insert into test values (1, 1, null), (1, 2, 1);
delete from test where (id_1, id_2) = (1, 1);

On a partitioned table, it does not throw the error:

create table test (
    id_1 int4 not null,
    id_2 int4 not null,
    parent_id_2 int4 null,
    primary key (id_1, id_2),
    foreign key (id_1, parent_id_2) references test (id_1, id_2)
) partition by list (id_1);
create table test_1 partition of test for values in (1);
insert into test values (1, 1, null), (1, 2, 1);
delete from test where (id_1, id_2) = (1, 1);

Christoph