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