Monday, June 4, 2012

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'.

9 comments:

  1. Hello, i read your blog and i would like to tell you that this blog is great and helpful to create unique index. Keep sharing these kind of blogs. I also have my own asp.net development company in india. We are a leading provider of asp.net development solutions in India.

    ReplyDelete
  2. Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like this.
    Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like this.
    Selenium training in Chennai

    Selenium training in Bangalore


    Selenium training in Pune
    Selenium Online training

    ReplyDelete
  3. Thank you for taking the time and sharing this information with us. It was indeed very helpful and insightful while being straight forward and to the point.
    python Training in Pune
    python Training in Chennai
    python Training in Bangalore

    ReplyDelete
  4. Great post. keep sharing such a worthy information.
    Google Ads Online Course

    ReplyDelete
  5. Thank you for sharing your awesome and valuable article this is the best blog for the students they can also learn.

    ReplyDelete

Thanks for visiting my blog.
However, if this helped you in any way, please take a moment to write a comment.

Thanks
Nirman