From: Martijn Tonies
Date: April 27 2005 11:39am
Subject: Re: why NOT NULL in PRIMARY key??
List-Archive: http://lists.mysql.com/mysql/183222
Message-Id: <02ff01c54b1d$d49386a0$3802a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit




> > [snip]
> > The same is true for any other value... Now that the columns have a NOT
> > NULL
> > constraint the records that previously contained NULL now hold '0'.
> >
> > x y
> > x 0
> > x z
> > x 0
> >
> > Now, how do you uniquely identify the 2nd and 4th rows?
> > [/snip]
> >
> > The database would have thrown an error when you tried to create row 4.
>
> No.  NULL is not a value.  It is a lack of value.  Kind of a special
> symbol, like infinity in mathematics.  You cannot say that

Actually, it's not even the "lack of value".

NULL is a state. A column can have two states: NULL or NON NULL.

Hence:

WHERE mycolumn IS NOT NULL
or
mycolumn IS NULL

Besides a non-null state, it can have many values :-)

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com