From: Martijn Tonies
Date: April 26 2005 2:42pm
Subject: Re: why NOT NULL in PRIMARY key??
List-Archive: http://lists.mysql.com/mysql/183158
Message-Id: <059d01c54a6e$275a8970$3802a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit


> > A primary key absolutely forbids duplicate values.
> >
> > Indexes created with the UNIQUE keyword do not allow duplicates, except
> > for the special case that multiple NULL values are allowed.
>
> I realise that it may (and is) defined in such a way, but it still does
not
> explain *why* part of a PRIMARY key might not be NULL.

Because a value in a PK should uniquely identify a row.

Using one or more NULLs negates this...

The best systems don't use NULLs at all. NULL is an abomination.

You should only store "values" that are "true".

Eg: employee with number 1 has a name of "Martijn".

If "salary" is optional, do not make it nullable, but create a separate
table "employee_salaries".

This way, there's never NULL confusion either.

With regards,

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