List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:February 22 2005 8:12pm
Subject:Re: UNIQUE Key Allowing Duplicate NULL Values
View as plain text  
At 15:00 -0500 2/22/05, Gustafson, Tim wrote:
>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?

Not for NULL values, no.  See the description for UNIQUE indexes here:

http://dev.mysql.com/doc/mysql/en/create-table.html

-- 
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
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