RE: UNIQUE Key Allowing Duplicate NULL Values
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/ -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
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UNIQUE Key Allowing Duplicate NULL Values
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
Re: UNIQUE Key Allowing Duplicate NULL Values
Tim, 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. :) What for? NULL is not equal to NULL. Period. If you don't want NULLs, make the column not null. The specification is correct. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UNIQUE Key Allowing Duplicate NULL Values
Martijn, The problem is that I don't want more than one row in the table that has a null value in the column. As you've pointed out in your e-mail, there's a difference between NULL and BLANK. It's not that I don't want NULL values, it's that I don't want MORE THAN ONE. 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: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 23, 2005 8:37 AM To: Gustafson, Tim; Paul DuBois; mysql@lists.mysql.com Subject: Re: UNIQUE Key Allowing Duplicate NULL Values Tim, 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. :) What for? NULL is not equal to NULL. Period. If you don't want NULLs, make the column not null. The specification is correct. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com smime.p7s Description: S/MIME cryptographic signature
Re: UNIQUE Key Allowing Duplicate NULL Values
The problem is that I don't want more than one row in the table that has a null value in the column. As you've pointed out in your e-mail, there's a difference between NULL and BLANK. It's not that I don't want NULL values, it's that I don't want MORE THAN ONE. I can easily continue arguing about this ... :-) NULL is not a value. There's no such thing as a null value. NULL is a state. NOT NULL is a state. That's the two possible states of a column. Why do you allow NULL, in this case? And what does it mean? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UNIQUE Key Allowing Duplicate NULL Values
Tim, The table in question is used to store aliases to web sites. That is, meitech.com is the main web site, and www.meitech.com is the alias. So, the first column is the domain name in question, and the second on is the host name part of the alias, in this example www. Now, I also have mrpc.com, and I want the web site to respond on both www.mrpc.com and mrpc.com, so I add two more aliases - one with the alias set to NULL and one with the alias set to www. This causes my Apache configuration script to add the proper lines to my httpd.conf file. Storing it this way doesn't make sense then ... If you need to be able to store multiple aliasses, use a parent-child relationship. Now, suppose I try to add an alias to another web site - mrpcusa.net. Because you can have duplicate null values right now, I can add another mrpc.com alias and the database will allow it, but it confuses Apache and sometimes you'll get the correct web site, and sometimes you do not. One could argue that I could just use a blank value instead of null, but I have a function right now that I use to escape all user-inputted strings before I add them to the database, and that function is set to return NULL if the user-inputted string is empty. I use this function all over the place, and I can't just rewrite the function to return a blank string in the case that the user-inputted string is empty, because I don't know what it might break elsewhere. In general, think of not storing NULLs at all if you don't have to. There's no point in storing NULLs, as this is the _absence_ of data, not data in itself. What point is there in storing nothing or unknown? NULL means unknown. Not empty. If the input is empty, store empty. All IMO, of course, but avoiding NULL usually helps avoiding many other problems as well :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UNIQUE Key Allowing Duplicate NULL Values
In article [EMAIL PROTECTED], Gustafson, Tim [EMAIL PROTECTED] writes: Martijn, The problem is that I don't want more than one row in the table that has a null value in the column. As you've pointed out in your e-mail, there's a difference between NULL and BLANK. It's not that I don't want NULL values, it's that I don't want MORE THAN ONE. You really should change your requirements. Since NULL != NULL, every DB enforcing a single NULL row by a unique index would not be SQL. You seem to want some special value to occur only once, but NULL is no value at all. Can't you make 0 or (the empty string) that special value? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
