On Wed, Feb 07, 2018 at 10:26:50PM -0500, Tom Lane wrote: > Rafal Pietrak <r.piet...@sm.strop.com.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