Re: it's a feature, but it feels like a bug

Rafal Pietrak <r(dot)pietrak(at)sm(dot)strop(dot)com(dot)pl> writes:
> 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;
> CREATE INDEX
> ztk=# create unique index rightone on test (b,c) where d is false;
> CREATE INDEX
> ztk=# alter table ONLY test ADD CONSTRAINT e2b_fk FOREIGN KEY (a,e)
> REFERENCES test(a,b) ON UPDATE CASCADE;
> ERROR: there is no unique constraint matching given keys for referenced
> table "test"

> And it is sort of "couterintuitive" - as you can see, there is a UNIQUE
> index for test(a,b) target; admitedly partial, but .... why should that
> matter?

Because the index fails to guarantee uniqueness of (a,b) in rows where d
isn't true. There could be many duplicates in such rows, possibly even of
(a,b) pairs that also appear --- though only once --- in rows where d is
true.

If there were a way to say that the FK is only allowed to reference rows
where d is true, then this index could support an FK like that. But
there's no way to express such a thing in SQL.

Personally I'd think about separating your rows-where-d-is-true into
their own table, which could have a normal PK index. You could still
create a union view over that table and the one with the other rows
to satisfy whatever queries want to think the two kinds of rows
are the same thing. But I'd offer that if one set of rows has (a,b)
as a PK and the other does not, they are not really the same kind
of thing.

regards, tom lane