Re: FK v.s unique indexes

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

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.

David J.