How can I create a SQL unique constraint based on 2 columns?

Question

I have a Table like this one:

|UserId   |  ContactID |  ContactName 
---------------------------------------
| 12456   |  Ax759     |  Joe Smith
| 12456   |  Ax760     |  Mary Smith
| 12458   |  Ax739     |  Carl Lewis
| 12460   |  Ax759     |  Chuck Norris
| 12460   |  Bx759     |  Bruce Lee

I need to add a constraint to this table so that no user can have duplicate contact id's. The users are importing data from various external systems so ContactId will not be unique across the board but will be unique on a per user basis.

I know how to create Unique and Non-Null contraints based on single columns but how can I create a unique contraints across 2 columns?

Answer 1

You can try this:

CREATE UNIQUE CLUSTERED INDEX index_name ON TABLE (col1,col2)

or

CREATE UNIQUE NONCLUSTERED INDEX index_name ON TABLE (col1,col2)

or

ALTER TABLE [dbo].[TABLE] ADD CONSTRAINT
    UNIQUE_Table UNIQUE CLUSTERED
    (
       col1,
       col2
    ) ON [PRIMARY]