Re: What is the use of a primary key?

Juan Pardillos wrote:
> I've got the following doubt: why is necessary to define a primary
> key?. I mean, the candidate keys must be defined as not null and
> unique (just the same that with the primary key), so... what's the
> difference?. Why is it necessary to consider a primary key instead of
> simply defining a set of candidate keys?.

C J Date now says that a single primary key is only a guideline, not a necessity. As you say, the candidate keys all have to have NOT NULL constraints (if they don't, they are no longer a candidate key), so any of them can be used as a primary key.

As an extreme example, consider:

CREATE TABLE ChemicalElements
(

	AtomicNumber	INTEGER NOT NULL UNIQUE,
	Name	VARCHAR(20) NOT NULL UNIQUE,
	Symbol	CHAR(2) NOT NULL UNIQUE,
	AtomicWeight	DECIMAL NOT NULL

Which of those three unique properties should be used as 'the' primary key? Any of them could be used. In a table of Isotopes, it probably makes most sense to use the AtomicNumber; in a table describing ChemicalCompounds, probably the Symbol would be most familiar.

(Of course, the name is marginally controversial: the English at least would say sulphur, whereas the standard name is sulfur; the US Americans would say aluminum, whereas the standard name is aluminium.

   I assume that languages other than English have their own versions of the element names - but they all use the same symbols.)

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