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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
