Oh well, thanks anyhow. If I could put in a feature suggestion, it would be a flag that I could set to disallow duplicate nulls. :)
Tim Gustafson MEI Technology Consulting, Inc [EMAIL PROTECTED] (516) 379-0001 Office (516) 480-1870 Mobile/Emergencies (516) 908-4185 Fax http://www.meitech.com/ -----Original Message----- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 23, 2005 8:14 AM To: Gustafson, Tim; mysql@lists.mysql.com Subject: RE: UNIQUE Key Allowing Duplicate NULL Values 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 >[EMAIL PROTECTED] >(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:[EMAIL PROTECTED] >Sent: Tuesday, February 22, 2005 3:13 PM >To: Gustafson, Tim; mysql@lists.mysql.com >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
smime.p7s
Description: S/MIME cryptographic signature
