UNIQUE clause and NULL in SQL Server 2012

In this topic You find out how work UNIQUE constraint for column with or without NOT NULL constraint.

Begin with  creating in SQL Server database TestDB. Create test table with only one column of name name.

CREATE DATABASE TestDB;
GO
USE TestDB;

CREATE TABLE test (
name varchar (100) UNIQUE
);

Select all data from table.

select * from Test;

It is empty table.
aa1

————————————————–

And insert one record with John name.

insert into test values('John');

aa2
It is OK because it is only one value in this column.

————————————————–

Try insert another record with the same name John.

insert into test values('John');

In result window will be display information like that:
Violation of UNIQUE KEY constraint 'UQ__test__72E12F1B6A9B7AD6'. Cannot insert    duplicate key in object 'dbo.test'. The duplicate key value is (John).
The statement has been terminated.

If You select all records, in table will be only one record with John name.

select * from Test;

aa2
So the UNIQUE work correctly stopping duplicate values.

————————————————–

And try insert NULL value in the table.

insert into test values(null);

Select all records from table.
aa3
The UNIQUE allows to set NULL value in the column.

————————————————–

And try insert into table second NULL value.

insert into test values(null);

You see the information:
Violation of UNIQUE KEY constraint 'UQ__test__72E12F1B6A9B7AD6'. Cannot insert duplicate key in object 'dbo.test'. The duplicate key value is ().
The statement has been terminated.

————————————————–

At the end select all records from test table.
aa3

As You see the UNIQUE allows for insert only one NULL value. If You would like to constraints this field for NOT NULL, You may add NOT NULL to column with UNIQUE clause.

2 thoughts on “UNIQUE clause and NULL in SQL Server 2012

  1. Sander Deryckere

    Thanks for showing the situation as-is in SQL server 2012, but this behavior does break the SQL specification where NULL is never considered equal to anything. So it should be possible to duplicate NULL values.

    This means that this behaviour can change with future versions of SQL server (and I thought it did, but perhaps it’s a setting somewhere)

    Reply

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.