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:

    ON Person (PersonId, IsPrimary)
    WHERE IsPrimary = 1

No comments:

Post a Comment

Featured Post

Store the result of a select query into an array variable

Popular Posts