W dniu 05.07.2018 o 15:18, David G. Johnston pisze: > On Thursday, July 5, 2018, Rafal Pietrak <rafal@xxxxxxxxx > <mailto:rafal@xxxxxxxxx>> wrote: > > W dniu 04.07.2018 o 00:55, David G. Johnston pisze: > > On Tuesday, July 3, 2018, Rafal Pietrak <rafal@xxxxxxxxx > <mailto:rafal@xxxxxxxxx> > > <mailto:rafal@xxxxxxxxx <mailto:rafal@xxxxxxxxx>>> wrote: > > > > > > ERROR: there is no unique constraint matching given keys for > referenced > > table "test2" > > ---------------------------- > > > > I cannot see any reasons why this functionality is blocked. > > > > In particular, contrary to what the ERROR says, the target > table *does > > have* a "unique constraint matching given keys", admittedly only > > partial. > > > > > > You are making the common error of confusing the distinct concepts of > > constraints and indexs. Table constraints cannot be partial by > > definition, and are a logical concept constraining the data model. > > Hmmm.. > > This does not match "my reality". Naturally I may be wrong, but the > example I've posted reflects my actual data I'm putting into the RDBMS. > That is: > 1. the data has unique constraint on (load,a,b,c) > 2. and the data have additional unique constraints on (load,a), provided > c is true, and (load,b) whenever c is false. > > > I probably generalized too much, especially since exclusion constrains > can be partial, so I'll be specific here. Foreign Keys don't have where > clauses and thus cannot target partial constraints. If you want to > overcome that limitation you can write a custom trigger. Actually. It looks like I'm a really really slow learner :( Only now I realized how should I code the scenario in question. For those interested, I'm currently implementing it like this: Instead of bool column "C", I'm putting there a column, which will keep a *copy* of value from column A or B depending on "traditional value" (true/false) of earlier column C. Now I can have a plain ordinary unique index over (load,C), and have it as FK target for other tables. win-win. I haven't realized it for years (this is how long the design stays with me to this day). > > I'm sure at least some of this is simply due to desirability as opposed > to some fundamental limitation, but in the end that is how the system > works today. Integer-table FK relationships are defined over the entire > PK table, not a subset. In my simplistic view of postgresql internal, I was thinking, that the engine (e.g. the set of internal triggers maintaining all FK consistency as layed down by application schema), having a row of data (freshly inserted or updated) just looks up an index it has associated with that particilar FK, and uses it to see if the other end "is comliant", or "has to change, too", or whatever else. So I was thinking, that when FK has an index to use, it shouldn't matter if it's complete or partial. I was thinking, that when "add constraint" cannot choose appropriate index, may be some explicit help (like ... using <index_name>;) would be due. But form the above explanation I fear that there is significantly more to the full picture than I though. I only king of hoped those "fundamental limitations" would be something I could grasp. Anyway, although indireclty, this thread brought me a solution. This is good. thenx, -R
