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 to create a restriction that allows you to insert only one IsPrimary = 1 per PersonId into this table. For all, there should be only one PersonId with IsPrimary = 1. So, as a result, I cannot insert the following 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

Answer 2

, . . ( IsPrimary) :

Persons table
=============

PersonId   Name      PrimaryPhoneId
-----------------------------------
1          Alice     1
2          Bob       3
3          Charlie   4



Phones table
============

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

, . , - , . IsPrimary, ( 0, 1) , , , .

Answer 3

!

CREATE UNIQUE NONCLUSTERED INDEX uniquey_thingumyjig
  ON table_name (PersonId)
    WHERE  IsPrimary = 1

https://msdn.microsoft.com/en-us/library/ms188783.aspx