Unique on non null entries but allowing multiple nulls

Hi,

If I use "NULL UNIQUE" when I create a table, it seems that only one
NULL entry is allowed. Since NULL could mean unknown, in this case,
two unknowns are not the same and I want to allow multiple nulls but I
still want non null entries be unique. Is there a construct in mysql
that can create a table column like this?

--
Regards,
Peng

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Unique on non null entries but allowing multiple nulls

Hi,

On 17-10-2011 15:39, Peng Yu wrote:
> If I use "NULL UNIQUE" when I create a table, it seems that only one
> NULL entry is allowed. Since NULL could mean unknown, in this case,
> two unknowns are not the same and I want to allow multiple nulls but I
> still want non null entries be unique. Is there a construct in mysql
> that can create a table column like this?

From the documentation:
A UNIQUE index creates a constraint such that all values in the index
must be distinct. An error occurs if you try to add a new row with a key
value that matches an existing row. For all engines, a UNIQUE index
permits multiple NULL values for columns that can contain NULL.

Only for 5.0 there is the exception that the BDB storage engine does not
allow multiple NULL values in a column with a UNIQUE index.

--
Kind regards / met vriendelijke groet,

Jigal van Hemert.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org