NULL values in unique constraints

Question

I have been asked several times in interviews this question.

Why an unique key allows one null value?

I searched for the answers but I couldn't find one. Please help me on this

Answer 1

• Because Microsoft have implemented UNIQUE constraints to behave like this, apparently. This is documented here (quote)

...UNIQUE constraints allow for the value NULL. However, as with any value participating in a UNIQUE constraint, only one null value is allowed per column ...

Apparently, this is not the behaviour as defined in the ANSI SQL standard documents (as Martin Smith's link suggests).

Other database servers behave differently in this respect. E.g. PostgreSQL allows you to do this:

create table t ( id int unique ) ;

insert into t (id) values (1),(2),(3),(null),(null),(null) ;

-- 6 rows affected

See dbfiddle.

When working with Oracle, you can do this (or something similar), too -

create table t ( id int unique ) ;

begin
  insert into t (id) values (1);
  insert into t (id) values (2);
  insert into t (id) values (3);
  insert into t (id) values (null);
  insert into t (id) values (null);
  insert into t (id) values (null) ;
end;
/

select * from t;

ID
----
1
2
3
null
null
null

Dbfiddle here.