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

Question

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

Answer 1

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 )

Answer 2

CREATE UNIQUE INDEX Some_Index_Name ON My_Table (customer_id, effective_date)

Answer 3

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.

Answer 4

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