Search code examples
sqlsql-serverunique-constraintcheck-constraints

SQL Server Unique Combination check constraint

Possible Duplicate:
Unique Constraint, excluding NULL values

Lets say i have a table with addresses, and i want an owner to have only One main address. Fist do i define a index/key or a check constraint? What's the expression for this?

TABLE_Address
--OwnerId
--IsMain    (not null able)
--City etc....

So i need a Unique combination of ( IsMain ==true AND ownerId)

Solution

  • Create a filtered unique index on ownerId with condition IsMain = 1. This index will a) enforce this condition and b) help your application to the pre-insert validation query.