CODEWITHSUNDEEP
sqlsql-server
vmb
vmb

Reputation: 3028

Alter table to add unique index based on combination with where clause-SQL SERVER

I have table with following columns

   ID         int               IDENTITY(1, 1) NOT NULL,
   Default    bit               NOT NULL,
   [Status]   varchar (20)      --possible values 'Open','Deleted'& 'Suspended'

I need a alter table script to restrict only one row which is having Status = 'Open' and Default = 1

I tried this,

ALTER TABLE [TABLE1]
ADD UNIQUE INDEX UK_TABLE1 
ON TABLE1(Default,[Status])
WHERE [Default]= 1 AND [Status] = 'Open'

Its not working,and gettting below exception

Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

can anyone help on this.ALSO IS IT POSSIBLE SAME USING A TRIGGER ??

Upvotes: 1

Views: 981

Answers (3)

Chendur
Chendur

Reputation: 1159

create unique index UK_TABLE1 
ON TABLE1(default,status)
WHERE default= 1 AND status = 'Open'

Upvotes: 0

Mitan Shah
Mitan Shah

Reputation: 344

create table Table1 (
  ID         int               IDENTITY(1, 1) NOT NULL,
   [Default]    bit               NOT NULL,
   [Status]   varchar (20)      )


--ALTER TABLE [TABLE1]
create UNIQUE INDEX UK_TABLE1 
ON Table1([Default],[Status])
WHERE [Default]= 1 AND [Status] = 'Open'

just remove alter table and replace add with create

Upvotes: 1

xLiSPirit
xLiSPirit

Reputation: 390

You should use create instead of alter:

CREATE UNIQUE INDEX UK_TABLE1 
ON TABLE1([Default],[Status])
WHERE [Default]= 1 AND [Status] = 'Open'

Upvotes: 2

Related Questions