4

Possible Duplicate:
Simple CHECK Constraint not so simple

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

Id, RoleId, PersonId, Active

The active column is used for softdelete functionallity: when you delete a role for a user, you actually set active to false. So you can have multiple rows with the same roleid-personid-active combination, 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 isn't, because you can have only one active role on any moment (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 equals to true?

ps. Sql server version is 2005

Community
  • 1
  • 1
Michel
  • 23,085
  • 46
  • 152
  • 242

1 Answers1

2

You can use a unique constraint on RoleId, PersonId and a computed column that holds 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)
)
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281