2

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,

Eric J.
  • 147,927
  • 63
  • 340
  • 553
asifa
  • 771
  • 1
  • 28
  • 63

2 Answers2

6

You can create a multi column unique constraint like this

ALTER TABLE MyTable ADD UNIQUE (Type, Name)

That will enforce the rules you describe.

Eric J.
  • 147,927
  • 63
  • 340
  • 553
0

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

Jigar Pandya
  • 6,004
  • 2
  • 27
  • 45
  • This does not work I want the name to be unique within each type. The name can be repeated in different types. The above method does not allow duplicate enteries for different type. – asifa Jul 09 '12 at 06:02
  • 1
    I would **really** not suggest having two VARCHAR(255) as the primary key of any table... – Eric J. Jul 09 '12 at 16:00