FK v.s unique indexes

From: Rafal Pietrak <rafal(at)ztk-rp(dot)eu>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: FK v.s unique indexes
Date: 2018-07-03 07:30:45
Message-ID: d9f85c60-3cdd-2504-cc6e-83fee0e19384@ztk-rp.eu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Łukasz Jarych 2018-07-03 08:11:42 Re: Cloning schemas
Previous Message Marco Fochesato 2018-07-03 05:29:40 Re: Not able to update some rows in a table