I decided to insist in arguing
<statement to be proven>
In Oracle, one could implement the natural key of an entity with
A. an unique index
B. an unique constraint
are either both TRUE of both FALSE.
</statement to be proven>
Ill only do the both FALSE part to keep it short.
If you argue the above statement does not reflect what has been stated so far in this thread then Ill agree to disagree (since we are totally disconnected) and call it quits.
You valuated A as being FALSE and B as being TRUE
and the only obvious objective criteria you could have used is B is more complete than A.
If you disagree with this criteria then let me know what was your criteria
if it is because I said so or Oracle says so than Ill call it quits.
Based on this valuation system I could come up with option C and argue C is the most complete
hence B is FALSE; or I could prove B cannot be TRUE (without having to even state C
I can show there has to be something more than B states).
When it comes to ER modeling, Oracle provides plenty of features (it is an RDBMS after all)
for the primary key ER concept, Oracle provides the primary key constraint construct
for the natural key ER concept, Oracle does not provide a construct (Im not aware of any natural/candidate/alternate key constraint
and, as I am out to prove, the unique constraint construct is not it either).
So, what is the difference between the primary key and natural key ER concepts?
none whatsoever except for an arbitrary choice of which one should be the primary
that is the one that usually gets migrated to related entities (in theory, both can be migrated to different dependent entities). The primary and natural words are really used to provide nuances to essentially the same thing
a key of an Entity
which specifies that each instance of the Entity can be uniquely identified by the values in the key. In fact, they dont talk about natural keys, but candidate keys which, after the selection process, become primary and alternate key
but we can stick with the natural.
Let us assume, we have an entity E=(x1,x2,x3,x4,
) with both K1=(x1,x2) and K2=(x3,x4) as ER keys
they are also natural because we didnt artificially create X1, X2, X3, X4 or derived them from other attributes
they are all natural attribute of the entity E. Is it conceivable such entity exists in real life? I would say, Yes.
Having identified K1 and K2 let me decide on the primary
well the candidate keys have already been established
the only thing Im doing now is toss a coin
the decision of which one is primary doesnt have anything to do with the existence or fabric of the keys. Let us assume we pick K2 as the primary.
We implement E as Oracle table T, K1 as an Oracle unique constraint and K2 as an Oracle primary key constraint.
insert into T (x1,x2,x3,x4,
) values (NULL,v2,v3,v4,
where v3, v4, v5 are NOT NULL.
Question: can I now reverse my toss of coin and pick K1 as the primary?
a totally arbitrary event has introduced differences between the candidate keys.
Of course, that NULL into x1 did it
which goes to prove, when implementing a candidate/natural key, one also has to say something about the nullability of the attributes making up the key. The unique constraint Oracle construct, as opposed to the primary key one
does not make any provisions regarding the nullability of the columns included in its definition. Hence there has to be more than what B states in order to be complete. Hence, based on the completeness valuation system, B is FALSE
in fact, both A and B are FALSE.
If you find flaws in my little Boolean excursion please point them out. If you think all of this has no value whatsoever then yes, let us call it quits.
So what should that option C be in order to evaluate to TRUE? Well, in Oracle, in order to preserve this little bit of logic above (and if no flaws in it, of course), one should implement a candidate key of an entity with an Oracle unique constraint and NOT NULL constraints for all component columns
or as a primary key constraint of course (but only one such PK constraint can exist for a table).
I find this a bit strong since the only problem posed by NULLs when implementing a key of an Entity is NULL-tuples. All values of type (v1,v2), (v1,NULL), (NULL,v2) can uniquely identify each instance of an Entity
. the only problem is the (NULL,NULL) pair. One could then argue, that including the NOT NOLL constraints in the Oracle primary key construct is too strong.
But this is an opinion
things are and will stay the way they are. And thats perfectly OK with me. Practicality always wins.