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.

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.

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

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

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