Hi, For some time now, I'm withholding new features in my DB application as I wasn't able to have unique constraints on partitioned tables. PG-v11 now has it and I've given it a try, but to my surprise it does not give it fully to the application. Those indexes don't support FK! At this point I've also checked partial indexes to see if they could support a sort of "FK duality" I have in my datasets, but they don't either (see below EXPLAINING). I'd like to understand why. I'd appreciate it if somebody could shred some light on the technical reasons/background behind those restrictions. EXPLAINING: ---------------------------------- psql (11beta2 (Debian 11~beta2-1)) Type "help" for help. tst=# create table test1(load bigint, a int, b int, c bool) partition by list (c); CREATE TABLE tst=# create table test1_true partition of test1 for values in (true); CREATE TABLE tst=# create table test1_false partition of test1 for values in (false); CREATE TABLE tst=# create unique index load ON test1 (load,a,b,c); CREATE INDEX tst=# create table info_text1 (load text, a int, b int, c bool, info text, foreign key (load,a,b,c) references test1(load,a,b,c)) ; ERROR: cannot reference partitioned table "test1" ---------------------------------- Why is this forbidden? For my application I could live without partitioning. Just using partial indexes would be enough. Still, this does not work either: ------------------------------- tst=# create table test2(load bigint, a int, b int, c bool) ; CREATE TABLE tst=# create unique index test2_true ON test2 (load,a) where c is true ; CREATE INDEX tst=# create unique index test2_false ON test2 (load,b) where c is false; CREATE INDEX tst=# create table info_text2 (load text, a int, info text, more_info text, foreign key (load,a) references test2(load,a)) ; ERROR: there is no unique constraint matching given keys for referenced table "test2" ---------------------------- I cannot see any reasons why this functionality is blocked. In particular, contrary to what the ERROR says, the target table *does have* a "unique constraint matching given keys", admittedly only partial. Yet, why should that matter at all? A unique index, partial or not, always yield a single row, and that's all what matters for FK. Right? I would very much like to understand the reasoning behind the above restrictions (on the use of indexes for FK targets), as this would probably help me avoid poor decisions in my database schema design. So I would appreciate it very very much if somebody could point me to reasoning behind such implementation. Regards, -R
