On Wednesday, February 7, 2018, Rafal Pietrak <
r.pietrak@sm.strop.com.pl> wrote:
Hi,
I've bumped onto the following problem:
---------------------screenshot----------------------------
ztk=# create table test (a int, b int, c int, d bool, e int, primary key
(a,b,c,d));
CREATE TABLE
ztk=# create unique index leftone on test (a,b) where d is true;
a unique index is there to indicate exactly one row as target
for the FK. This is what LEFTONE index does.
It is quite possible in your schema that (a,b) = (1,2) can appear three times (or at least twice) in your table...once where d is true, once where d is false, and once where d is null. The FK references the columns of the table, not a named index. So any index that doesn't cover the whole table cannot be used.
If you want to enforce something like this you can use triggers.
David J.