From: "Paul DuBois"
> 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
> 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
> 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
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.