List:General Discussion« Previous MessageNext Message »
From:Tim Gustafson Date:February 22 2005 8:00pm
Subject:UNIQUE Key Allowing Duplicate NULL Values
View as plain text  
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
(516) 379-0001 Office
(516) 480-1870 Mobile/Emergencies
(516) 908-4185 Fax
http://www.meitech.com/ 


Attachment: [application/x-pkcs7-signature] smime.p7s
Thread
UNIQUE Key Allowing Duplicate NULL ValuesTim Gustafson22 Feb
  • Re: UNIQUE Key Allowing Duplicate NULL ValuesPaul DuBois22 Feb
RE: UNIQUE Key Allowing Duplicate NULL ValuesTim Gustafson23 Feb
  • RE: UNIQUE Key Allowing Duplicate NULL ValuesPaul DuBois23 Feb
RE: UNIQUE Key Allowing Duplicate NULL ValuesTim Gustafson23 Feb
  • Re: UNIQUE Key Allowing Duplicate NULL ValuesMartijn Tonies23 Feb
RE: UNIQUE Key Allowing Duplicate NULL ValuesTim Gustafson23 Feb
  • Re: UNIQUE Key Allowing Duplicate NULL ValuesMartijn Tonies23 Feb
  • Re: UNIQUE Key Allowing Duplicate NULL ValuesHarald Fuchs23 Feb
Re: UNIQUE Key Allowing Duplicate NULL ValuesMartijn Tonies23 Feb