On Thursday, July 5, 2018, Rafal Pietrak <
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>> 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.