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;
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"
ztk=#
-----------------------------------------------------------
BTW: the "rightone" index above only "reflects my actual schema" and
does not give much to the axample.
Now I know it's a "feature".
But it really hurts my dataset/schema "coexistence".
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? a unique index is there to indicate exactly one row as target
for the FK. This is what LEFTONE index does. Surely, some part of the
TEST table will never be accesable for TEST(A,E) as FK targets; but that
(in the case of my dataset) is exactly what is expected.
Thus, It feels like a bug.
So my questions are:
1. is there a well known reason for not allowing it? (i.e. ignoring
partial unique indexes when looking up FK support index)
2. I hope that there isn't; So: would it break things if I try to patch
sources to allow for it?
3. I admit I'm not so much of a hacker, but at this point I'm so
desperate to have it done, that I'll try anyway. So if the above is
doable at all, where should I start reading sources?
Thank you,
-R