Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to create Composite Unique Constraint in SQL Server 2005

Preferably I would like to know how to do it using the SQL Server Management Studio interface but that isn't completely necessary. If you simply have a script to add one after a table is made that would be fine.

like image 518
Joe Phillips Avatar asked Nov 03 '09 23:11

Joe Phillips


People also ask

How do I add a composite unique constraint in SQL?

To define a composite unique key, you must use table_constraint syntax rather than column_constraint syntax. To satisfy a constraint that designates a composite unique key, no two rows in the table can have the same combination of values in the key columns.

Can a unique key be composite?

A composite unique key designates a combination of columns as the unique key. To satisfy a unique constraint, no two rows in the table can have the same value for the unique key. However, the unique key made up of a single column can contain Nulls.

How do I create a composite unique key in SQL Server 2012?

Use SQL Server Management StudioOn the Table Designer menu, select Indexes/Keys. In the Indexes/Keys dialog box, select Add. In the grid under General, select Type and choose Unique Key from the drop-down list box to the right of the property, and then select Close. On the File menu, select Save table name.

How do I create a unique constraint on multiple columns in SQL Server?

To define a UNIQUE constraint, you use the UNIQUE keyword followed by one or more columns. You can define a UNIQUE constraint at the column or the table level. Only at the table level, you can define a UNIQUE constraint across multiple columns.


1 Answers

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".

like image 87
Raj More Avatar answered Oct 08 '22 16:10

Raj More



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!