is id and phone number enough to uniquely identify a row in phone numbers Was designing a table a customer_phone_numbers table a while back, which only purpose was to allow us to store as many phone numbers as a user wanted to have on file. I originally came to the conclusion that customer_id + phone_number was unique, but then it occurred to me, it is possible for the same user to have 2 extensions at the same number. I am not sure how likely that is though, and it creates another issue.

phone_number ext
555-555-555, NULL
555-555-555, NULL


becomes legal, unless I find a good way to constrain ext against null. What is the best way to normalize and constrain phone numbers?
There are probably ways to solve this (e.g. an instead of trigger rather than an explicit constraint, or a clever filtered index if on 2008+), but why?

As an example, you can do this with two filtered indexes in 2008+:

CREATE TABLE #cpn(c INT, pn VARCHAR(32), x VARCHAR(8000));

CREATE UNIQUE INDEX x ON #cpn(c, pn) WHERE x IS NULL;
CREATE UNIQUE INDEX y ON #cpn(c, pn, x) WHERE x IS NOT NULL;

INSERT #cpn(c, pn   ) SELECT 1, '555-5555';        -- OK
INSERT #cpn(c, pn, x) SELECT 1, '555-5555', '345'; -- OK
INSERT #cpn(c, pn, x) SELECT 1, '555-5555', '555'; -- OK
GO
INSERT #cpn(c, pn   ) SELECT 1, '555-5555';        -- fails on index x
GO
INSERT #cpn(c, pn, x) SELECT 1, '555-5555', '345'; -- fails on index y


But I fail to see the point. This seems like unnecessary flexibility. If I'm going to call the office and try to reach Bob at extension 55, why would I be any more likely to reach him at extension 67? Does this seem like a feasible, real-world scenario to anyone?

I suggest you make it a business rule that a user can only have one extension at the same number. This way you can keep your constraint on customer id and phone number, and allow extension to be optional.

Also, assuming you are storing phone number and extensions as strings, you can go the other way and let people enter them together instead of separate. If they want to enter 15 different extensions at the same number, why stop them? I also think it's silly to allow an extension of 0, but this would allow that also.