0

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.

4
Contributors
4
Replies
5
Views
7 Years
Discussion Span
Last Post by achied
0

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.

0

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
0

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

0
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

Edited by achied: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.