0

I have a 'Users' table where I want to make the 'Phone' column unique. Currently the 'PhoneNumber' column allow null. I'm using the following script.

ALTER TABLE dbo.Users
ADD CONSTRAINT Unique_PhoneNumber 
UNIQUE NONCLUSTERED (PhoneNumber)

The problem is, there is 300+ rows in the 'Users' table. Half of them have a Null value. When I run the script it says complete successfully. However after refreshing the table constraints in Management studio, the constraint did not appear and is not applied to that column when adding a new user.

Note: If I try to run the script again, I get a duplicate error, like the constraint already exist.

Anyone know what I need to do in order to get the column unique?

AdamL
  • 12,421
  • 5
  • 50
  • 74
Mario
  • 335
  • 3
  • 11
  • 1
    If 2008+ use a unique filtered index. If 2000-2005 use an indexed view. – Martin Smith Dec 19 '13 at 15:35
  • http://technet.microsoft.com/en-us/library/ms175132(v=sql.105).aspx – Joe Taras Dec 19 '13 at 15:43
  • Martin, thank you for your suggestion. Sorry I should have mentionned I use MSSQL 2008 R2. I will try the filtered index suggestion. – Mario Dec 19 '13 at 16:20
  • 1
    ...Why aren't users allowed to share a phone number? My two parents live in one house, with one physical land-line (phone number). What happens if they both register? – Clockwork-Muse Dec 20 '13 at 06:22

1 Answers1

0

Take a look at "indexes" in sp_help procedure (assuming SQL Server): sp_help 'dbo.Users'. "Unique constraint" is actually unique index. If you create ordinary unique index, then max one null value is allowed in the field. To exclude nulls from the check, you need to create unique filtered index, like so:

create unique index Unique_PhoneNumber on dbo.Users(PhoneNumber) where (PhoneNumber is not null)

AdamL
  • 12,421
  • 5
  • 50
  • 74
  • Thanks for your solution frikozoid. When I execute the command, I get the same result about duplicates :( – Mario Dec 19 '13 at 17:08
  • @Mario It's because you already have `Unique_PhoneNumber` on this table. Drop it first. – AdamL Dec 19 '13 at 17:14
  • I did drop the index then ran the script to create the index again. Here is the error message: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Users' and the index name 'idx_NetworkUserLogin_notnull'. The duplicate key value is (). The table was badly designed having a varchar(50) as PK could it be just that? – Mario Dec 19 '13 at 18:19