Add unique constraint depending on value in SQL

Question

i have a situation where i want to add unique constaint only when there is a certain value in other field for example the table is ID CategoryName Name Value CategoryID

the constraint will be on ID, CategoryName and Name only is CategoryID is 0

is it possible?

Answer 1

CREATE UNIQUE NONCLUSTERED INDEX [UQ_ID_CategoryName_Name, CategoryName, Name] ON TableName 
(
    ID, CategoryName, Name
)
WHERE (CategoryID = 0)