List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:February 23 2005 3:06pm
Subject:Re: UNIQUE Key Allowing Duplicate NULL Values
View as plain text  
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

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