From: Martijn Tonies
Date: April 26 2005 3:21pm
Subject: Re: why NOT NULL in PRIMARY key??
List-Archive: http://lists.mysql.com/mysql/183173
Message-Id: <060701c54a73$91ffebd0$3802a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit


> > >I would understand it if it would mean that the key as a whole could
not
> be
> > >NULL, but the restriction that each column that is part of a PRIMARY
KEY
> > >must have the NOT NULL constraint is not logical.
> >
> > Sure it is.  If any part could be NULL, then it could contain duplicate
> > NULL values, thus compromising uniqueness of that part, and of the
entire
> > key as well.
> >
> > Suppose you have a two part key on columns a and b, with b allowed to
> > be NULL.  Then you could have these values:
> >
> > x y
> > x NULL
> > x z
> > x NULL
> >
> > Now, how do you uniquely identify the 2nd and 4th rows?
> >
> 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?

Yes well, exactly the point: this is not possible in a primary key :-)

With regards,

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