Re: FK v.s unique indexes

W dniu 05.07.2018 o 15:18, David G. Johnston pisze:
> On Thursday, July 5, 2018, Rafal Pietrak <rafal@ztk-rp.eu
> <mailto:rafal@ztk-rp.eu>> wrote:
> 
>     W dniu 04.07.2018 o 00:55, David G. Johnston pisze:
>     > On Tuesday, July 3, 2018, Rafal Pietrak <rafal@ztk-rp.eu
>     <mailto:rafal@ztk-rp.eu>
>     > <mailto:rafal@ztk-rp.eu <mailto:rafal@ztk-rp.eu>>> 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