Create a composite key

Question

I have a table like:

Id   PersonId     Phone   IsPrimary
-----------------------------------
1     1          12345        1
2     1          55555        0
3     2          66666        1
4     3          77777        1
5     3          88888        0
6     3          99999        0

How can I create constraint that will allow insert into this table only one IsPrimary = 1 per PersonId. For all PersonId there should be only one with IsPrimary = 1. So, in a result I won't be able to insert the next record:

  Id   PersonId     Phone   IsPrimary
  -----------------------------------
  1     1          00000        1

Answer 1

You can try creating a unique filtered index:

CREATE UNIQUE INDEX UQ_Person_isPrimary
ON Person (PersonId, IsPrimary)
WHERE IsPrimary = 1