Sql-server – NULL values in unique constraints

sql serverunique-constraint

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

Best Answer

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.

Related Solutions

Sql-server – UNIQUE index key violation

There is a lot to grasp if you want to fully understand all of the intricacies of locking and isolation levels in SQL Server, and your question is essentially leading you in this direction.

All of the information is available at http://msdn.microsoft.com/en-us/library/ms173763.aspx, although it's not an easy read. An easier read is available at http://databases.about.com/od/sqlserver/a/isolationmodels.htm - pay particular attention to the "phantom reads" section.

For what you are trying to do you would need BOTH transactions running with an isolation level of "SERIALIZABLE". What this basically means is that they will run "as if" one was run after the other - that they were actually serialized.

What is happening is that BOTH the transactions decide that the same record needs to be inserted, and then BOTH try to insert that same record. The lock that is taken by the test logic is separate from the lock that is taken for the insert, which allows this to happen. SERIALIZABLE will stop this from happening with it's "Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes" clause. Another way to think of this is that the read locks from the test need to be held until the whole transaction completes. This can be specified using a HOLDLOCK query hint; but I have to say that I've not personally played with using HOLDLOCK, but just with the "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE" version.

Sql-server – Unique Constraint with multiple null columns

I think your database is suffering from a bad data model. You might want to rethink it a little, judging from the tables you've mentioned the model isn't well normalized and you're trying to enforce a foreign key reference to a table column which isn't really a key to anything. One way to go is something like this :

CREATE TABLE EMP (ID INT PRIMARY KEY IDENTITY
                 ,EmpName VARCHAR(100) NULL)

CREATE TABLE PCTypes (id INT PRIMARY KEY IDENTITY
                     ,PcTypeName VARCHAR(100) NULL)

CREATE TABLE PC (Id INT PRIMARY KEY IDENTITY
                ,EmpId INT NULL
                ,PCName VARCHAR(100) NULL
                ,HostName VARCHAR(100) NULL
                ,PhysicalLocation VARCHAR(100) NULL
                ,PCType INT NULL)

ALTER TABLE PC ADD CONSTRAINT FK_PC_EmpId FOREIGN KEY (empId) REFERENCES Emp(id)
ALTER TABLE PC ADD CONSTRAINT FK_PC_PCType FOREIGN KEY (PCType) REFERENCES PCTypes(id)

CREATE TABLE PCNetwork (Id INT PRIMARY KEY IDENTITY
                       ,NetworkName VARCHAR(100) NULL
                       ,EmpId INT NULL
                       ,PhysicalLocation VARCHAR(100) NULL)

ALTER TABLE PCNetwork ADD CONSTRAINT FK_PCNetwork_EmpId FOREIGN KEY (empId) REFERENCES Emp(id)

CREATE TABLE PCNetwork_PCs (PCNetworkId INT NOT NULL
                           ,PCId INT NOT NULL
                           ,IPAddr NVARCHAR(20) NULL)

ALTER TABLE PCNetwork_PCs ADD CONSTRAINT FK_PCNetwork_PCs_Network FOREIGN KEY (PCNetworkId) REFERENCES PCNetwork(id)
ALTER TABLE PCNetwork_PCs ADD CONSTRAINT FK_PCNetwork_PCs_PC FOREIGN KEY (PCId) REFERENCES PC(id)
CREATE UNIQUE INDEX uq_pcnetworks ON PCNetwork_PCs(PCNetworkId, PCId)
CREATE UNIQUE INDEX uq_pcnetworks_ip ON PCNetwork_PCs(IPAddr)

Best Answer

Related Solutions

Sql-server – UNIQUE index key violation

Sql-server – Unique Constraint with multiple null columns

Related Question