From: "Paul DuBois"
> >I would understand it if it would mean that the key as a whole could not
> >NULL, but the restriction that each column that is part of a PRIMARY KEY
> >must have the NOT NULL constraint is not logical.
> Sure it is. If any part could be NULL, then it could contain duplicate
> NULL values, thus compromising uniqueness of that part, and of the entire
> key as well.
> Suppose you have a two part key on columns a and b, with b allowed to
> be NULL. Then you could have these values:
> x y
> x NULL
> x z
> x NULL
> Now, how do you uniquely identify the 2nd and 4th rows?
The same is true for any other value... Now that the columns have a NOT NULL
constraint the records that previously contained NULL now hold '0'.
Now, how do you uniquely identify the 2nd and 4th rows?
> > > If your PRIMARY KEY would allow NULL values, it would not be possible
> >> to address these rows with NULL values (*) and therefore it would not
> >> be a real primary key, by definiton. It would be a unique key.
> >That would be true for the entire key, but not for each part of the
> You have it backwards. Any individual column in a primary key might
> contain duplicates. But the combination of values in all of the columns
> must be unique.
Which is the case! The same key definition would be valid as a UNIQUE key
plus no combined key value is NULL.
It just seems a matter of a definition in the SQL standard...