Error while creating Unique Index in SQL Server: Incorrect syntax near 'NOT'

sw
sw
Member
170 Points
7 Posts

I'm trying to create unique key constraint with some condition like following:

 CREATE UNIQUE NONCLUSTERED INDEX UQ_MyTable_TypeKey ON [dbo].[MyTable]([TypeKey] ASC)
WHERE [TypeKey] NOT IN(55555,75555)

But it's showing run time error as 

Incorrect syntax near 'NOT'

Although working fine without NOT as:

 CREATE UNIQUE NONCLUSTERED INDEX UQ_MyTable_TypeKey ON [dbo].[MyTable]([TypeKey] ASC)
WHERE [TypeKey] IN(55555,75555)

Any one has any clue here?

 

Views: 432
Total Answered: 1
Total Marked As Answer: 0
Posted On: 19-Apr-2024 03:57

Share:   fb twitter linkedin
It seems something related to sql version and constraints.
 - Rahul Maurya  19-Apr-2024 20:14
Answers
sw
sw
Member
170 Points
7 Posts
         

Resolved by self by changing NOT IN to individual AND operation:

 CREATE UNIQUE NONCLUSTERED INDEX UQ_MyTable_TypeKey ON [dbo].[MyTable]([TypeKey] ASC)
WHERE [TypeKey] <> 55555 AND [TypeKey] <> 75555)
Posted On: 21-Apr-2024 22:02
 Log In to Chat