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

One approach is to only allow updates to this table via a [stored procedure](https://msdn.microsoft.com/en-us/library/ms190782.aspx?f=255&MSPPError=-2147217396). You can use the SP to enforce your business logic. — David Rushton, Nov 23 '16 at 10:29
Creating a instead-of trigger during insert will allow you to check this condition before inserting the value — krish, Nov 23 '16 at 10:34

score 9 · Accepted Answer · answered Nov 23 '16 at 10:35

9

You can try creating a unique filtered index:

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

answered Nov 23 '16 at 10:35

Giorgos Betsos

71,379
9
63
98

Didn't know SQL Server had this, I only thought Oracle had it. Do you know what's the first version this was available in? – Radu Gheorghiu Nov 23 '16 at 10:36
@RaduGheorghiu It's SQL Server 2008. – Giorgos Betsos Nov 23 '16 at 10:37
Didn't know it either , +1 . – sagi Nov 23 '16 at 10:38
This is a really cool feature that I didn't know about, but I feel like in this case, it's just a way to cover up a bad table design. Great answer, but perhaps not the best advice for the person who asked the question (and a bit overkill for such a simple problem). – Allon Guralnek Nov 23 '16 at 10:46

Allon Guralnek · Answer 2 · 2016-11-23T10:42:17.767

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.

score 2 · Answer 3 · answered Nov 23 '16 at 10:35

2

Filtered indexes!

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

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

answered Nov 23 '16 at 10:35

gvee

16,732
35
50

Create a composite key

3 Answers3