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?

score 75 · Accepted Answer · edited Sep 16 '21 at 08:18

75

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]

edited Sep 16 '21 at 08:18

Konstantin A. Magg

1,106
9
19

answered Jul 10 '09 at 13:47

Jonathan

11,809
5
57
91

5

What is the difference between the two methods? Is there any certain cases where one is preferred over the other? Will the index approach be faster on a large data set? – Zapnologica Jul 07 '15 at 06:32
1

@Zapnologica Please check this other question about this specific topic: http://dba.stackexchange.com/questions/144/when-should-i-use-a-unique-constraint-instead-of-a-unique-index – Jonathan Jul 08 '15 at 06:44

score 48 · Answer 2 · edited Jul 05 '11 at 20:33

48

You can add unique constraint tou your fields:

ALTER TABLE YourTable
ADD CONSTRAINT UQ_UserId_ContactID UNIQUE(UserId, ContactID)

edited Jul 05 '11 at 20:33

Emre Yazici

10,136
6
48
55

answered Jul 10 '09 at 13:49

AdaTheDev

142,592
28
206
200

Thank's! Works fine – TheBosti Feb 26 '17 at 15:49

score 7 · Answer 3 · edited Jul 05 '11 at 20:33

7

You can try ALTER TABLE [TABLE_NAME] ADD UNIQUE (column1,column2,column3 ...columnN).

Hope this helps cheers.

edited Jul 05 '11 at 20:33

Emre Yazici

10,136
6
48
55

answered Jul 05 '11 at 07:13

Tamseyc

445
3
8
19

score 3 · Answer 4 · answered Jul 10 '09 at 13:51

CREATE TABLE [LineItems](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [OrderID] [int] NOT NULL,
    [LineItemNumber] [int] NOT NULL,
 CONSTRAINT [PK_LineItems] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
),
 CONSTRAINT [UC_LineItems] UNIQUE NONCLUSTERED 
(
    [OrderID] ASC,
    [LineItemNumber] ASC
)
)

score 3 · Answer 5 · answered Jul 10 '09 at 13:55

Here is the syntax for creating a unique CONSTRAINT as opposed to a unique INDEX.

ALTER TABLE publishers 
  ADD CONSTRAINT uqc_pub_name 
  UNIQUE (pub_name)

It is important to note that there are subtle differences dependent on which method you use to enfore the uniqueness of a column.

See the following MSDN reference for an interesting walkthrough of these:

http://msdn.microsoft.com/en-us/library/aa224827(SQL.80).aspx

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

5 Answers5

Linked