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
source to share
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)
)
source to share