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
