> On Jul 5, 2018, at 1:30 AM, 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.
>
> Pls consider in real life: load (a person), can have either a (a kind of
> brest cancer); or b (a kind of prostrate) - this is only a cooked
> example attemping to illustrate, that one may need to put additional
> constraints on the entire dataset.
>
It’s difficult enough to define a unique person (without mother and father) and certainly this weeks definition of burden is not likely to help matters. If you’re main worry is data consistency you might be better off normalizing your structure - either with separate tables per cancer type (person id, cancer specifics; unique on person) or in a single table one per line (person id, cancer type, cancer description; unique on person). You can reconstitue person,breast,prostate from either of those. We won’t quibble on one person having both (though remotely possible, men do get breast cancer).