List:General Discussion« Previous MessageNext Message »
From:Tim Gustafson Date:February 23 2005 1:10pm
Subject:RE: UNIQUE Key Allowing Duplicate NULL Values
View as plain text  
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/ 



-----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

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