How can I create a SQL unique constraint based on 2 columns?
Sql ServerSql Server-2005Sql Server-2008ConstraintsUnique ConstraintSql Server Problem Overview
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?
Sql Server Solutions
Solution 1 - Sql Server
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]
Solution 2 - Sql Server
You can add unique constraint tou your fields:
ALTER TABLE YourTable
ADD CONSTRAINT UQ_UserId_ContactID UNIQUE(UserId, ContactID)
Solution 3 - Sql Server
You can try ALTER TABLE [TABLE_NAME] ADD UNIQUE (column1,column2,column3 ...columnN)
.
Hope this helps cheers.
Solution 4 - Sql Server
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
)
)
Solution 5 - Sql Server
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