Can I create a unique constraint on 2 fields and a condition on a boolean field

Possible duplicate:
Simple CHECK constraint is not easy

we have a table with roles for the user. It looks like this:

Id, RoleId, PersonId, Active

      

The active column is used for softdelete functionality: when you delete a role for a user, you actually set active

to false

. This way you can have multiple lines with the same combination roleid-personid-active

, but only when the active bit is set to false. So this is valid data:

Id   RoleId   PersonId   Active
1    1        1          false
2    1        1          false
3    1        1          false
4    1        1          false

      

but this is not the case because you can only have one active role at any time (so record 3 should not be inserted):

Id  RoleId  PersonId  Active
1   1       1         false
2   1       1         true
3   1       1         true

      

My question is, can I create a unique constraint on RoleId, PersonId and Active

, where active

is equal true

?

ps. Sql server version - 2005

+3


source to share


1 answer


You can use a unique constraint on RoleId

, PersonId

and a calculated column that contains Id

for all inactive roles and null

for all active roles.



create table Roles
(
  Id int identity primary key,
  RoleId int not null,
  PersonId int not null,
  Active bit,
  ActiveInt as case Active when 0 then Id end,
  constraint UQ_RolePersonActive unique (RoleId, PersonId, ActiveInt)
)

      

+2


source







All Articles