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
No comments:
Post a Comment