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'.
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'.
Thanks for sharing.
ReplyDeleteHello, 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
ReplyDeleteIt is very good blog and useful for students and developer ,Thanks for sharing
.Net Online Training
Dot Net Online Training Bangalore
.Net Online Training
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.
ReplyDeleteYour 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
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.
ReplyDeletepython Training in Pune
python Training in Chennai
python Training in Bangalore
Great post. keep sharing such a worthy information.
ReplyDeleteGoogle Ads Online Course
Mindblowing blog very useful thanks
ReplyDeleteSEO Training in Velachery
SEO Training in Chennai
Thank you for sharing your awesome and valuable article this is the best blog for the students they can also learn.
ReplyDeleteThank u for sharing such an great information.
ReplyDeleteSQL Course in Pune