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

On Wed, Feb 07, 2018 at 10:26:50PM -0500, Tom Lane wrote:
> 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.

There will be as soon as we implement ASSERTIONs.

> 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.

Another way might be to partition the table on the boolean and make a
foreign key to the "true" partition, e.g.:

CREATE TABLE foo(b BOOLEAN, i INTEGER NOT NULL, t TEXT NOT NULL) PARTITION BY LIST (b);
CREATE TABLE foo_true PARTITION OF foo (PRIMARY KEY(i, t)) FOR VALUES IN ('true');
CREATE TABLE bar(foo_i INTEGER NOT NULL, foo_t TEXT NOT NULL, FOREIGN KEY(foo_i, foo_t) REFERENCES foo_true);

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate