CODEWITHSUNDEEP
sqlsql-server-2005sql-server-2008
Brian Webster
Brian Webster

Reputation: 30855

sql - How do I enforce a two-column constraint?

I have a CustomerID column and an EffectiveDate column in a table.

I need the combination of these two to be unique.

However, I already have a primary key on an auto-numbered integer column.

What is a good way to accomplish my goal?

Thanks

Upvotes: 0

Views: 88

Answers (4)

Tom H
Tom H

Reputation: 47444

CREATE UNIQUE INDEX Some_Index_Name ON My_Table (CustomerID, EffectiveDate)

Upvotes: 3

SQLDev
SQLDev

Reputation: 206

CREATE TABLE MyTable
(
<columns here>
CONSTRAINT U_ConstraintName UNIQUE (CustomerID, EffectiveDate)
)

Upvotes: 1

Matthew Jones
Matthew Jones

Reputation: 26190

Try creating a UNIQUE index on the two columns.

CREATE TABLE Example
(Col1 int NOT NULL,
Col2 int NOT NULL,
UNIQUE (Col1, Col2)
)

Example taken from this thread.

Upvotes: 1

Thomas
Thomas

Reputation: 64635

Simply add a unique constraint:

Alter Table TableName
  Add Constraint UC_TableName_Col1Col2 Unique ( Col1, Col2 )

SQL Server creates a unique index when you create a unique constraint. If there is already a clustered index, then the above will create that index as nonclustered. However, you can be explicit like so:

Alter Table TableName
  Add Constraint UC_TableName_Col1Col2 Unique Nonclustered ( Col1, Col2 )

Upvotes: 7

Related Questions