Date: Tue, 1 Apr 2025 12:18:30 +0200
From: Christoph Berg <myon@debian.org>
To: Luca Vallisa <luca.vallisa@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Self referential foreign keys in partitioned table not working
 as expected
Message-ID: <Z-u9dhA5ikSgOul9@msg.df7cb.de>
References: 
 <CAAT=myvsiF-Attja5DcWoUWh21R12R-sfXECY2-3ynt8kaOqjw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAAT=myvsiF-Attja5DcWoUWh21R12R-sfXECY2-3ynt8kaOqjw@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/Z-u9dhA5ikSgOul9%40msg.df7cb.de>
Precedence: bulk

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