Selectively check for duplicates in SQL Server

Suppose we have a table in our relational database that stores Person phone numbers, like:

Id	PersonId	Phone	IsPrimary
1	1	2610-9346551	1
2	1	2610-8346552	0
3	2	2610-7346553	1
4	3	2610-6346544	1
5	3	2610-7346555	0
6	3	2610-5346556	0

IsPrimary is a bit field that determines the primary number of each person. We want to add a constraint to this table that ensures each person has at most 1 primary number.

So, after adding the constraint, an attempt to insert the following record to the table:

Id	PersonId	Phone	IsPrimary
7	1	2610-3376551	1

will fail, whereas an attempt to insert this record:

Id	PersonId	Phone	IsPrimary
7	1	2610-3376551	0

will be carried out without any problem.

SQL Server, starting with version 2008, has a feature that makes implementing this kind of selective unique contraint very easy. It is called a Unique Filtered Index and it is implemented as simply as:

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