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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
