At 16:47 +0200 4/26/05, Jigal van Hemert wrote:
>From: "Dawid Kuroczko"
>> > It can't have anything to do with the 'uniqueness' of the data, since I
>> > have a lot of 'zero'-values in the column, as long as the combination of
>> > columns in the PRIMARY key results in unique values.
>> Because it is a PRIMARY KEY. I mean phrase 'PRIMARY KEY' means "a key
>> with which each row can be explicitly addressed". So if you have 2000
>> rows in a table, you can write 2000 SELECT statemens which will use
>> columns in primary key and each of these SELECT statements will
>> return exactly one (different) row.
>With the NULL values included it will still uniquely identify each row...
>I would understand it if it would mean that the key as a whole could not be
>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:
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 key...
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.
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com