List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:February 23 2005 1:14pm
Subject:RE: UNIQUE Key Allowing Duplicate NULL Values
View as plain text  
At 8:10 -0500 2/23/05, Gustafson, Tim wrote:
>Is there any flag I can set on the column or key to not allow duplicate
>nulls?
>
>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/

No, you'd have to use a BDB table, because only BDB allows a single
NULL per UNIQUE index.


>
>
>
>-----Original Message-----
>From: Paul DuBois [mailto:paul@stripped]
>Sent: Tuesday, February 22, 2005 3:13 PM
>To: Gustafson, Tim; mysql@stripped
>Subject: Re: UNIQUE Key Allowing Duplicate NULL Values
>
>
>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