In SQL, how to prevent duplicate values for a user in a table, not for a whole table?

I would like to create a table with T-SQL. User is a foreign key from a users table. One user can’t have two same phone numbers in ‘phone1’ and ‘phone2’ fields. However, it is allowed that two users have the same phone number. How do I create such field, to be unique only for the same userid foreign key?

English is not my strong side. I know it works for all rows. I need it only to work with rows that have the same userid foreign key. So I heard that my design could be improved by making check constrained and making foreign key a primary key or to normalize a table adding a table for phone numbers.

Let’s say there is a ‘users’ table and this would be ‘contacts’ table. So in contacts userid is foreign key and can be imported more then once.

CREATE TABLE Users
(
    UserId int NOT NULL PRIMARY KEY IDENTITY(1,1),
    -- ...
)

CREATE TABLE Contacts
(
    ContactId int NOT NULL PRIMARY KEY IDENTITY(1,1)
    UserId    int NOT NULL FOREIGN KEY FK_Users_Contacts REFERENCES Users ( UserId )
    Phone1    varchar(20),
    Phone2    varchar(20)
)

Answer

You’ll want a CHECK CONSTRAINT:

ALTER TABLE [tableName]
    ADD CONSTRAINT CK_DiffPhones CHECK ( Phone1 <> Phone2 )

The syntax for your specific DBMS may differ.

That said, I think your table design can be improved. You should normalize Phone Numbers to their own table.

Advertisement

Answer