CODEWITHSUNDEEP
sql-servert-sqlcomposite-key
Darkside
Darkside

Reputation: 469

Create a composite key

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

Upvotes: 4

Views: 127

Answers (3)

Allon Guralnek
Allon Guralnek

Reputation: 16121

You're trying to create a relationship between two entities using data instead of an actual relation. Each person has one primary phone number and multiple non-primary phone numbers. Instead of describing the primary phone number relationship using data (in your case, the IsPrimary column), it should instead be a foreign key in the Persons table:

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

This way, a person can only have one primary phone. Also, if someone switches their primary phone number, you only have to update one row. If you hold a IsPrimary column, you'll have to update two rows (setting the old primary to 0, then setting the new primary to 1), and make sure both updates happen in the same transaction, otherwise you might lose the primary altogether if the second row fails to update.

Upvotes: 4

gvee
gvee

Reputation: 17161

Filtered indexes!

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

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

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can try creating a unique filtered index:

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

Upvotes: 9

Related Questions