0

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

3
  • 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.