UNIQUE Key Allowing Duplicate NULL Values

[Tim Gustafson]

Hi there!

I have a table, defined as follows:

CREATE TABLE `WebSiteDomainNames` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `WebSite` int(10) unsigned NOT NULL default '0',
  `DomainName` int(10) unsigned NOT NULL default '0',
  `Alias` char(16) default NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `DomainName` (`DomainName`,`Alias`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The way I read this definition, it should be impossible for someone to
put in two rows with the same DomainName and Alias, however, right now I
have the following rows in the table:

+-----+---------+------------+-------+
| ID  | WebSite | DomainName | Alias |
+-----+---------+------------+-------+
| 543 |    1086 |       1334 | NULL  |
| 545 |    1086 |       1334 | NULL  |
| 509 |    1086 |       1334 | *     |
+-----+---------+------------+-------+

And I can insert even more NULL rows if I want to.  Shouldn't the UNIQUE
key prevent this from happening?

Tim Gustafson
MEI Technology Consulting, Inc
tjg@stripped
[phone] Office
[phone] Mobile/Emergencies
[phone] Fax
http://www.meitech.com/ 


Attachment: [application/x-pkcs7-signature] smime.p7s