CODEWITHSUNDEEP
sqlsql-servert-sqlunique-constraint
Khayam Khan
Khayam Khan

Reputation: 73

Unique key constraint in sql server for new values in a column

I want to implement unique key constraint on my table for new values, previous data is duplicate e.g

declare @temp table
(
id int identity,
PolicyNO varchar(30)
)
insert into @temp values('abc')
insert into @temp values('abc')
insert into @temp values('abc')

now I want to add a unique constraints on @temp table. The new record on id=3 or greater will have to be unique and here third record should be restricted for insertion

ALTER TABLE @TEMP   
ADD CONSTRAINT Unique_PolicyNo UNIQUE (PolicyNO ) where id>3

some thing like that I am facing trouble in syntax any help will be appreciated.

Upvotes: 3

Views: 314

Answers (1)

gotqn
gotqn

Reputation: 43636

Try this:

CREATE TABLE dbo.temp 
(
id int identity,
PolicyNO varchar(30)
)
insert into  dbo.temp  values('abc')
insert into  dbo.temp  values('abc')
insert into  dbo.temp  values('abc')


CREATE UNIQUE NONCLUSTERED INDEX [IX__tem] 
ON  dbo.temp  (PolicyNO)
WHERE id>3

Then try to insert one new value twice.

or 

CREATE UNIQUE NONCLUSTERED INDEX [IX__tem] 
ON  dbo.temp  (PolicyNO)
WHERE id>3 AND PolicyNO IS NOT NULL

in order to allow insertion of null values.

Upvotes: 1

Related Questions