Primary Key based on two field combo?

[TruckStuff]

Say I have field A and field B. Field A and Field B may contain duplicate values, however, I want to enture that the COMBINATION of field A and field B is unique. How can I accomplish this?

Example valid data:
A B
ABC 123
ABC 456
XYZ 123
XYZ 456

BTW, I tried searching but I didn't have any idea what to search for. Sorry if this is a covered topic. :o

[nnichols]

You need create a unique index based on the two columns -

ALTER TABLE tbl_name ADD UNIQUE index_name (col1,col2)

Hope this helps 😉

[Sxooter]

There are two ways of doing this, the first, as nnichols mentioned is to create a unique multi-column index, the other way is to declare a multi-key primary key when creating the table.

The difference is semanitc but important. A primary key is always considered the "standard" way you access the table, and some database tools are programmed to look for a primary key. A multi-column unique index that is not the primary key may not get used by some of these tools.

The syntax is:

create table test (field1 type, field2 type, field3 type, primary key(field1, field2));