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;
————————————————–
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;
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.
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.