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?

  • Is your goal to allow a customer to use the same number but different extensions, or the same number and maybe NULL for one extension and a valid extension for another? Jun 10, 2012 at 23:25

3 Answers 3


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));


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
INSERT #cpn(c, pn   ) SELECT 1, '555-5555';        -- fails on index x
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.

  • people consider storing them as some sort of INT? phone numbers are not numbers, but what I call "numeric codes" which means strings that consists of only digits, in which no math really makes sense. Jun 10, 2012 at 23:29
  • 1
    note varchar 4 may be insufficient for extensions, because some ridiculous softwares allow extensions up to 11 digits long, just saying. Jun 10, 2012 at 23:30
  • @xenoterracide yes, especially extensions. A lot of places you'll find use a separate column for area code, city code, local part, extension, etc. Makes validation easier. Jun 10, 2012 at 23:30
  • @xenoterracide ok, I was just making up something quickly for demonstration, I have absolutely no way to guess your data model. You get the point, right? Jun 10, 2012 at 23:31
  • to some degree, but if you're an international company I never wanted to deal with that complexity, 5 digit country code + 15 digit? phone number (I'd have to look) + 11 digit extension was enough not to worry about how the locals my store the local part. Jun 10, 2012 at 23:33

Just throwing it out there: you could instead put your PK on { CustomerID, PhoneType }. You'd be storing a little context on what each phone number means, which could be useful.

If you make PhoneType a free-text string rather than an FK to a PhoneTypes dimension table, you allow an indefinite number of phone numbers per customer (or you could make it an FK but allow users to create new phone types, though I wouldn't take it far). By all means, encourage users to pick from a list of common phone types (work, home, cell, fax) first, though.

  • 1
    Interesting, this method would also allow a voice number to be the same as a fax number (a legitimate scenario). Jun 11, 2012 at 17:13

You cannot create a UNIQUE constraint and allow NULL in sql server, so i guess you can store default value 0 in the ext, and when displaying it you use case to display ext as empty value

ext = case when ext = 0 then '' else ext  
  • 3
    You mean that only one NULL is allowed when there is a UNIQUE constraint - in SQL-Server. Jun 10, 2012 at 21:11
  • 1
    but what if 0 is the valid extension... nothing prevents one from using 0 as extension (AFAIK though convention is an operator) Jun 10, 2012 at 23:02
  • 2
    Guys you can also get around the 0/1/many NULLs problem using a unique filtered index. I don't think it helps in this specific case, but I didn't want to leave it hanging here that you can't have NULLs in a unique constraint in SQL Server (which is false) or that you can't have multiple NULLs in a unique index in SQL Server (which is also false - a filtered index allows compliance with the ANSI version of a unique constraint, which unfortunately wasn't how Microsoft initially chose to implement it). Jun 10, 2012 at 23:22
  • Aaron is correct. A partial Unique index can overcome this limitation now. Jun 11, 2012 at 7:56
  • It doesn't look like the OP ever said they were using SQL Server. Jun 11, 2012 at 17:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.