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

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(at)debian(dot)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
>