Unique constraint over 2 columns in MS SQL

[agr8lemon]

Hello!

I'm hoping that you can help me Alter a SQL table to provide a unique constraint over 2 columns.

For instance let's say I have a departments table called Dpt_tbl with two columns that I want to be unique

Name
Location

So I want to make sure that you could not have

NAME | Location
-----------------------
HR | Loc1
HR | Loc1

but you could have

HR | Loc1
HR | Loc2

These would both be varchar columns.

Any help would be great.

Thanks.

[pclfw]

This is very simple to do. Books On Line CREATE TABLE or ALTER TABLE will give you all of the information you need together with examples.

[samaru]

This looks like a composite key setup:

CREATE TABLE Dpt_tbl
	(
	Name varchar(50) NOT NULL,
	Location varchar(50) NOT NULL
	)  
GO

ALTER TABLE Dpt_tbl ADD CONSTRAINT
	PK_Dpt_tbl PRIMARY KEY CLUSTERED 
	(
	  Name,
	  Location
	) 	
GO

[pclfw]

Why use the tables single clustered index when multi-column unique constraints are available? That doesn't make sense to me.

[achied]

CREATE TABLE T
{
   Name varchar(10) ,
   Location varchar(10) ,
         CONSTRAINT U_NameLocation UNIQUE (Name,Location) 
}

use Unique constraint. Primary key column is unique too, but you can have more than one Unique constraint in one table