SQL : Is it possible to store duplicate null values in a column with unique constraint

Question

How do you define unique constraint in terms of nulls ? Is there any standard followed by all the databases to allow nulls in unique column.

If you want nulls then why be unique? If you want it unique then add a default value. — Giannis Paraskevopoulos, Aug 05 '14 at 07:47
Microsoft SQL Server? http://stackoverflow.com/questions/767657/how-do-i-create-a-unique-constraint-that-also-allows-nulls — Jørn Schou-Rode, Aug 05 '14 at 07:47
MySQL? http://stackoverflow.com/questions/21695885/mysql-falsely-allowing-duplicate-entries-when-one-of-the-fields-involved-is-null — Jørn Schou-Rode, Aug 05 '14 at 07:48
@GiannisParaskevopoulos I want only unique but Its not always me to insert values. Why is database allowing null over unique — Vivek, Aug 05 '14 at 08:31

score 1 · Answer 1 · answered Aug 05 '14 at 08:14

Quoted from a blog, here is the amount of NULL values allowed in a column with a UNIQUE constraint, in a few DBMS :

What other DBMSs do

Trudy Pelzer and I wrote this on page 260 of our book, SQL Performance Tuning: DBMS Maximum number of NULLs when there is a UNIQUE constraint

IBM (DB2) One

Informix One

Ingres Zero

InterBase Zero

Microsoft (SQL Server) One

MySQL Many [although the BDB storage engine was an exception]

Oracle Many

Sybase One

Trying the following code in Sql Server (2012 Express, at least) fails, as expected :

DECLARE @T TABLE
(
    value int unique
)
INSERT INTO @T VALUES(1)
INSERT INTO @T VALUES(2)
INSERT INTO @T VALUES(NULL)
INSERT INTO @T VALUES(NULL)

The article mentions that the correct rule about handling null values in a column with a unique constraint should be that the value of the unique column of any two rows, if both are not NULL, should not be equal.

In other words, non null values must be unique, and multiple null values are allowed.

But not all DBMS seems to apply that rule, so it's better to confirm it with a simple test.

András Aszódi · Answer 2 · 2014-08-05T07:59:17.160

0

"...a UNIQUE column can have multiple NULLs in a row unless you explicitly add a NOT NULL constraint to each column."

Joe Celko's "SQL for Smarties", 2nd edition, Page 16.

For some fun with NULL-s, try these:

SELECT 1 = NULL (returns NULL)

SELECT 1 IS NULL (returns FALSE)

edited Aug 05 '14 at 07:59

answered Aug 05 '14 at 07:53

András Aszódi

8,948
5
48
51

score 0 · Answer 3 · answered Aug 05 '14 at 08:06

It depends on the database engine. MySQL allow null duplicates but SQL Server doesn't.

You can try this on SQLFiddle. MySQL return an exception for the user 'ddd' while MySQL return an exception for the user 'ccc'

CREATE TABLE users (
  username varchar(20) NOT NULL,
  id int unique
);

insert into users values ('aaa', null);
insert into users values ('bbb', 1);
insert into users values ('ccc', null);
insert into users values ('ddd', 1);

SQL : Is it possible to store duplicate null values in a column with unique constraint

3 Answers3