Re: foundations of relational theory? - are primary keys optional?

Anthony W. Youngman wrote:
> And aren't PKs optional in relational theory, anyway?

There are two view-points here - both sensible, and actually equivalent. It just sounds like they differ.

View A: No, primary keys are mandatory. There has to be at least one combination of the attributes in a relvar that uniquely identify each tuple, and one such combination of attributes is designated as the primary key. The other possible combinations are regarded as alternate keys - AKs - though 'alternative keys' would be better use of English.

View B: Yes, primary keys are optional. There must be at least one candidate key, but even if there is more than one CK, it is not necessary to arbitrarily designate one of them as 'the' primary key.

View A could be described as the older view point and View B as the more recent. The net result is the same: there is at least one combination of the attributes in the relvar which uniquely identifies each tuple. Note that the combination might be the empty set of attributes, in which case the relvar contains at most one tuple.

Note too that neither view says anything about indexes - they are irrelevant (even though most systems implement unique constraints - aka candidate keys - using an index).

I personally subscribe to View B because it does away with the need for AKs (and PKs, come to that). Occam's Razor: Entia non sunt multiplicanda praeter necessitatem. If you have CK's, PK's and AK's are unnecessary.

See C J Date: Datebase - Selected Writings (probably the 1989-1991 volume, but conceivably the 1991-1994 volume).

SQL DBMS do not mandate that each SQL table has a candidate key. This is widely regarded (especially in c.d.t) as a defect in the SQL standard and hence in the DBMS that implement an approximation to the SQL standard.

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/