unique index is not enforced if IsActive column is false

Asked Oct 22 '15 at 16:11

Active Oct 22 '15 at 16:11

Viewed 65 times

I have an accountBranchMap table which has three columns:

AccountId, BranchId, IsActive.

There is an unique index on AccountId to ensure that each account can only map to one branch.

Is there a way the unique index will only be checked on the records with IsActive = 1 ? e.g If there a row of (AccountId = 1, BranchId = 1, IsActive = 0 ), I can still insert (1, 2, 1) into the table. There can exist multiple duplicated AccountId as long as its IsActive value is false.

I am using SQL-Server 2008R2 SP1

Thank you.

asked Oct 22 '15 at 16:11

Sean

Filtered index. See the second answer in the dupe. – Martin Smith Oct 22 '15 at 16:14
@Sean See http://stackoverflow.com/questions/32473877/partial-value-constraint-of-sql-server-table/32473929#32473929 – Lukasz Szozda Oct 22 '15 at 18:31

unique index is not enforced if IsActive column is false

0 Answers0