MySQL - Make a pair of values unique

mysql composite-key unique-key
Josh Brittain picture Josh Brittain · Oct 6, 2012 · Viewed 32.3k times · Source

I have a table with two int values that are IDs. On their own these IDs can show up any number of times in the table, but together they should only ever appear once.

Is there a way to make a pair of values unique and still allow the individual values to show up multiple times?

As a follow up, if this is possible can the pair of values be used as a key? I currently have a 3rd column for a unique auto increment value for my key.

Answer

Raphaël Althaus picture Raphaël Althaus · Oct 6, 2012

It's called a composite key.

If you want to change your actual PK to a composite one, use

Alter table <your table> drop PRIMARY KEY;
Alter table <your table> drop COLUMN <your autoincremented column>;

Alter table <your table> add [constraint <constraint name>] PRIMARY KEY (<col1>, <col2>);

You can also just add a unique constraint (your PK will be the same, and unique pairs... will have to be unique).

alter table <your table> add [constraint <constraint name>] unique index(<col1>, <col2>);

Personnally, I would recommend the second solution (simple PK + unique constraint), but that's just a personal point of view. You can google for pros and cons arguments about composite keys.

The part between [] are optional.

EDIT

If you wanna do this in the create table statement

For a composite pk

CREATE TABLE Test(
    id1 int NOT NULL, 
    id2 int NOT NULL,
    id3 int NOT NULL,
    PRIMARY KEY (id1, id2)
);

For an unique index

CREATE TABLE Test1(
    id1 int NOT NULL AUTO_INCREMENT, 
    id2 int NOT NULL,
    id3 int NOT NULL,
    PRIMARY KEY (id1),
    UNIQUE KEY (id2, id3)
);

Related questions

How do I specify unique constraint for multiple columns in MySQL?

I have a table: table votes ( id, user, email, address, primary key(id), ); Now I want to make the columns user, email, address unique (together). How do I do this in MySql? Of course the example is just... an example. …

Read More
How to properly create composite primary keys - MYSQL

Here is a gross oversimplification of an intense setup I am working with. table_1 and table_2 both have auto-increment surrogate primary keys as the ID. info is a table that contains information about both table_1 and table_2. table_1 (id, field) …

Read More
How to make composite primary key on phpmyadmin

How to make a composite primary key on phpmyadmin. such that student id is combination of year and roll number. sid = 112132 , 11 of year 2011 and 2132 is roll number?? anybody help..

Read More