FK v.s unique indexes

Поиск
Список
Период
Сортировка
От Rafal Pietrak
Тема FK v.s unique indexes
Дата
Msg-id d9f85c60-3cdd-2504-cc6e-83fee0e19384@ztk-rp.eu
обсуждение исходный текст
Ответы Re: FK v.s unique indexes
Re: FK v.s unique indexes
Re: FK v.s unique indexes
Список 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


В списке pgsql-general по дате отправления:

Предыдущее
От: Marco Fochesato
Дата:
Сообщение: Re: Not able to update some rows in a table
Следующее
От: Łukasz Jarych
Дата:
Сообщение: Re: Cloning schemas