Unique Constraint in SQL Server

Unique Constraint

• Allow us to store unique values in a one column or two or more of columns.

• No duplicate records are allowed to insert in column

• Null value allowed to insert but only one null value present in the column

Set unique Constraint to single column 

First we delete our already created customer table.

drop table customer

To our customers mobile_no and email are unique. So let's create a customer table and set mobile_no as a unique constraint. We will set email as a  unique after table creation using alter command

create table customer (id int Primary Key,name varchar(50),mobile_no varchar(50) unique,email varchar(50))

insert into customer values(1,'chaitanya patil','8380955493','[email protected]')

able to insert null value but only one record (we get a error for 2nd statement)

insert into customer values(2,'Sarthak shaha',null,'[email protected]')

insert into customer values(3,'Shubham Jashav',null,'[email protected]')//Violation of UNIQUE KEY constraint 'UQ__customer__D7B19EFAFC020F36'. Cannot insert duplicate key in object 'dbo.customer'. The duplicate key value is (<NULL>).

Add a unique constraint to a existing column in table

If there are already some duplicate values present in a column and we tried to add a unique constraint on this column then we will get an error.

To resolve errors first we need to remove duplicate records and after that need to add constraint.

Update duplicates record

update customer set email = '' WHERE id = 3

Then add CONSTRAINT

alter table customer add CONSTRAINT unique_customer_unique UNIQUE(email)

Delete the unique Constraint

alter table customer drop constraint unique_customer_unique