Create Unique Index with IGNORE_DUP_KEY ON/ OFF on SQL Server column

Unique index/ constraint in SQL Server is used to enforce uniqueness over an SQL Column. That is, if a particular column(s) is defined with UNIQUE constraint/ index, user cannot insert duplicate values in that column.

We can have only one NULL value for that column. means, not more than one row can contain NULL value in that column.

While creating a UNIQUE index, we also can specify how to handle duplicate violation. I.e. whether to throw an error, or silently ignore adding a row having a value getting duplicated for that column.

Try following example -

create table #Countries (Id int, Name varchar(20))
GO
ALTER TABLE #Countries
ADD CONSTRAINT IX_Countries
UNIQUE (Name)
WITH (IGNORE_DUP_KEY = ON)
GO
insert into #Countries (Id, Name)
select 1, 'India'
union
select 10, NULL
union
select 11, NULL
union
select 2, 'Australia'
union
select 3, 'India'
GO
select * from #Countries

drop table #Countries

This query inserts only 3 rows in the table

10    NULL
2    Australia
1    India

But it does not throw any error, because we have configured to ignore duplicate rows.

In the same query if you set IGNORE_DUP_KEY =OFF, it will give you following message:

Violation of UNIQUE KEY constraint 'IX_Countries'. Cannot insert duplicate key in object 'dbo.#Countries'.