CODEWITHSUNDEEP
sqlsql-server
li-raz
li-raz

Reputation: 1696

Add unique constraint depending on value in SQL

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?

Upvotes: 3

Views: 3206

Answers (3)

Khan
Khan

Reputation: 18152

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

Upvotes: 6

usr
usr

Reputation: 171178

Create a filtered unique index on Name for CategoryID = 0.

Upvotes: 0

Laurence
Laurence

Reputation: 10976

One way to achieve this would be to have two tables, one for categoryId 0 and one for the rest. You can define the uniqueness constraint on the first table, and use a view and union any time you want to use them as a single table.

Upvotes: 0

Related Questions