W dniu 08.02.2018 o 04:26, Tom Lane pisze: > Rafal Pietrak <r.piet...@sm.strop.com.pl> writes: [--------------------] > >> 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.
I sort of knew/expected that. But I'd like to approach the sources anyway. > > 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. Actually, they are. the explanation of my schema would be lengthy, but in showt, lets say, I'm talking of a mail-hub, where : A=mbox-owner-id, B=message-UNIQUE-id, C=the-other-entity-id, D=flas-inbox-outbox'; the table contains every message anyone send or received. only sender assigns ID to a message. So: all outgoing messages have unique (A,B), and D=true all received messages have unique (B,C), and D=false those messages are parsed, digested, and they update columns of their respective rows. ... the tricky part is, that some of them must form explicit lists. This is column (E). This is why I need to have an FK (E,A) --> (B,A). Currently, to use FK in this dataset I have the main table split into: inbox, and outbox. Unfortunately this fires back as the entire schema effectively has to have twice the number of relations, and FK interlinking it growing almost as O(2) with tables. At the point that I am, this is already unmanagable. So I'm quite desperate to "do it some other way". Like patching postgresql. I was thinking, that: an attempt to "alter table add constraint .. foreign key..." could: a) identify if the target table has ANY sort of UNIQUE index covering provided list of columns (even if it is a partial index) b) if that index is only partial, locate the condition and use it during insert/update/etc.... and retrieval of target row. c) if that index is functional index, locate that function and use it during insert/update/etc. So I'd appreciate some guidence which part of the sources I should study first. regards, -R
