53

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.

Peter Hall
  • 53,120
  • 14
  • 139
  • 204
Josh Brittain
  • 2,162
  • 7
  • 31
  • 54

3 Answers3

86

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)
);
Peter Hall
  • 53,120
  • 14
  • 139
  • 204
Raphaël Althaus
  • 59,727
  • 6
  • 96
  • 122
  • 11
    +1 for specifying the term composite key , Which helps people like me to learn about this new feature – Aravind.HU Oct 06 '12 at 20:55
  • 1
    Could you also add an example how this is done in `CREATE TABLE` statement? – Tomáš Zato Sep 06 '13 at 09:06
  • @RaphaëlAlthaus how would i do this so that I dont get an error when I try to add a duplicate and instead just skip over the duplicate record im trying to add and go to the next one? –  Mar 24 '16 at 18:50
10

Try this: ALTER TABLE table_name ADD CONSTRAINT uc_name UNIQUE (col1,col2)

Przemek Lewandowski
  • 516
  • 2
  • 6
  • I was thinking is is a '*better way*'; mysql at least thwarts the intention and transforms this into a `UNIQUE KEY`... Same result as: `ALTER TABLE panel ADD UNIQUE KEY name_label_UNIQUE (name, label);`. Also, I found that you need to `DROP KEY` when you add: `CONSTRAINT ...UNIQUE` *ho hum* – will Mar 23 '16 at 05:20
  • @will I wouldn't say that adding two unique keys is the same as a unique composite. Two unique columns would accept <01>, <02> and then subsequently reject <01>, <03> and reject <03>, <02>, while a composite key would accept all three items. Point of interest - both would still accept <02>, <01> if items were attempted to be added in the order given. – Watachiaieto Mar 23 '20 at 22:14
0

add primary key (col1, col2) to your create table definition

jspcal
  • 50,847
  • 7
  • 72
  • 76