Re: FK v.s unique indexes

W dniu 05.07.2018 o 10:11, Rob Sargent pisze:
[---------------]
>>
>> 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).
>

No, no.

This was just cooked example, my reality (messaged between parties,
whose identity is stored in columns A and B, and column C keeping a flag
indicating who was the sender). I've just used gender example to limit
the discussion of whether A can be linked to something, or if B cannot
be linked to something else. It cannot, the constraints I described are
as real, as prostrate cancer never happening to a women. I tried to
eliminate from the discussion all but mathematical relations between
values of those columns. Which are:

(load,a,b,c) is unique
(load,a,true) is unique
(load,b,false) is unique

Nothing else matters.

And I need FK to such dataset. Partial.

There is one table with data only referencing (load,a, true)... and I'd
prefere that table NOT NEED to have the spurous column (c) containing
value "true" for every row in that table.

Same goes for (load,b,false).

Now it occured to me, I can do:
create table (load text, a int, b int, c int);
instead of...
create table (load text, a int, b int, c bool);

With the new layout I'll just do:
insert (load,a,b,c) values (<load>, <a>, <b>, <a>); for the cases of
"true", and...
insert (load,a,b,c) values (<load>, <a>, <b>, <b>); for other cases

(load,c) will be unique over the entire dataset.

Now I can FK to (a,c) from whenever I want. A computed column (d bool) =
(a = c) could be helpfull, but I don't think it'll be indispensable.

Sorry to mislead you.

-R