At 16:25 +0200 4/26/05, Jigal van Hemert wrote:
>From: "Paul DuBois"
>
>Hi Paul,
>
>> A primary key absolutely forbids duplicate values.
>>
>> Indexes created with the UNIQUE keyword do not allow duplicates, except
>> for the special case that multiple NULL values are allowed.
>
>I realise that it may (and is) defined in such a way, but it still does not
>explain *why* part of a PRIMARY key might not be NULL. If the combination of
>parts in the PRIMARY key is such that it can uniquely identify a record it
>would be sufficient for a primary key IMHO. It could well be a UNIQUE index
>with the restriction that the complete key (the parts combined) may not be
>NULL...
I'm not sure I understand what you are saying. But if part of a PRIMARY KEY
could be NULL, then it _wouldn't_ uniquely identify records.
>
>> Such a unique index therefore cannot be a primary key. However, for the
>> purposes you describe above, it sounds like the solution is simply to
>> define your columns as allowing NULL, and to create your index using
>UNIQUE
>> rather than PRIMARY KEY.
>
>Unfortunately ther is a big difference in performance between the primary
>and secudary indexes in InnoDB. We made (secundary) indexes and didn't have
>a primary index at all (so MySQL used a 64-bit integer as primary key).
>After we changed the index to primary the performance increased
>considerably.
>Some queries turned out to be quite slow and we found that these relied on
>NULL values. Converting the index to primary silently converted all "NULL"
>constraints to "NOT NULL" for the columns that are part of the primary key
>and converted all NULL values in the db to the appropriate default values
>for the various column types.
>
>Regards, Jigal.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
