2

I have a table to store person's phone numbers. There is a flag (IsDefault) to mark a phone as the default one. There should be only one phone per person flagged as default phone. Is it possible to create a check constraint to only allow one combination of PersonId and IsDefault = True?

Oscar
  • 13,594
  • 8
  • 47
  • 75
  • 2
    Check constraint? No. You're wanting to assert something about *multiple* rows (some people attempt to do this using UDFs but they often mis edge cases). Indexed view or filtered index, Yes. – Damien_The_Unbeliever Nov 20 '15 at 11:50

1 Answers1

2

Use a filtered index, something like this:

CREATE TABLE PhoneNumber (
ID int primary key,
PhoneNumber nvarchar(20),
PersonID int,
IsDefault bit
)
GO

CREATE UNIQUE INDEX UX_Default_PhoneNumber
ON PhoneNumber(PersonID)
WHERE IsDefault = 1
GO

This enforces a unique PersonID only where IsDefault is true.

andyb
  • 770
  • 5
  • 11