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');


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.

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.