CODEWITHSUNDEEP
sql-server
asifa
asifa

Reputation: 771

How do I apply unique constraint on two columns SQL Server?

I have two columns type and name in the database. I want to apply a unique constraint where the name is unique within each type.

Type   Name
 A     ABC
 R     ABC
 B     ABC

should be allowed whereas 

Type  Name
 A    ABC
 A    ABC

should not be allowed

How to enforce this constraint where name is unique within each type?

Thanks,

Upvotes: 2

Views: 5741

Answers (2)

Jigar Pandya
Jigar Pandya

Reputation: 5987

While creating table you can do 

CREATE TABLE YourTable(
  Name VARCHAR(255) NOT NULL,
  Type   VARCHAR(255) NOT NULL,
  PRIMARY KEY (Type, Name)
)

Or you can alter your table with 

ALTER TABLE YourTable ADD PRIMARY KEY (Type, Name)

After that I executed following four.. (last one failed)

insert into YourTable values('AB','ABD')
insert into YourTable values('AA','ABD')
insert into YourTable values('AA','ABC')
insert into YourTable values('AA','ABD')

Thanks, Jigar

Upvotes: 0

Eric J.
Eric J.

Reputation: 150108

You can create a multi column unique constraint like this

ALTER TABLE MyTable ADD UNIQUE (Type, Name)

That will enforce the rules you describe.

Upvotes: 6

Related Questions