How to create Composite Unique Constraint in SQL Server 2005

In SQL Server Management Studio

  • goto the Object Explorer
  • pick your table and open its designer (can't remember what it was called in 2005 - Modify Table or something?)
  • in the table designer, pick the "Manage Indexes and Keys" icons from the toolbar (the table with the little key)
  • in there, add a new index and give it a name, click it's "Unique" setting

alt text

  • open the list of columns in the index definition and add your columns you want to thave in the index

alt text

That's it! :)


Try this:

ALTER TABLE dbo.YourTableName 
ADD CONSTRAINT
ConstraintName UNIQUE NONCLUSTERED
(
    Column01,
    Column02,
    Column03
)

I use business names for constraints so that if it is violated and an exception bubbles up, I get "Only one Dept per Employee violation" in my error message rather than "ConstraintXXX violation".

Tags:

Sql Server 2005

Constraints

Recent Posts