206

I have a table called provider. I have three columns called person, place, thing. There can be duplicate persons, duplicate places, and duplicate things, but there can never be a dupicate person-place-thing combination.

How would I ALTER TABLE to add a composite primary key for this table in MySQL with the these three columns?

LihO
  • 37,789
  • 9
  • 89
  • 156
David542
  • 96,524
  • 132
  • 375
  • 637

6 Answers6

446
ALTER TABLE provider ADD PRIMARY KEY(person,place,thing);

If a primary key already exists then you want to do this

ALTER TABLE provider DROP PRIMARY KEY, ADD PRIMARY KEY(person, place, thing);
Adrian Cornish
  • 20,973
  • 11
  • 50
  • 74
  • 17
    @David542 No it doesn't - you can have only 1 primary key. – Adrian Cornish Jan 14 '12 at 01:30
  • 37
    @David: it's a single primary key composed of multiple fields, aka a composite key. – Marc B Jan 14 '12 at 01:33
  • 3
    @David542 Of course you can - this is a composite primary key consisting of 3 fields. The combination of the 3 fields must be unique. – Adrian Cornish Jan 14 '12 at 01:35
  • 2
    thanks for posting -- really got me out of battling with the ui – plditallo Aug 31 '13 at 03:41
  • 1
    one of the most precious answers in SO :) – alwbtc Sep 09 '13 at 07:01
  • 1
    do you know if the second statement is atomic? – kunigami Sep 25 '14 at 01:19
  • 1
    @AdrianCornish Mysql Version is 5.6.28. The above statement is throwing an error: Incorrect table definition; there can be only one auto column and it must be defined as a key. – Richie Jun 09 '16 at 07:44
  • 1
    @Richie if modifying an existing table, you may need to remove the auto increment property from the offending column, drop the existing primary key on that column, create a new primary key, then readd the auto increment and adjust the starting value as required. Sounds like a different question/configuration that needs more details to be answered. – cs_alumnus Nov 09 '16 at 19:41
  • beautiful. i wish i was better at MySQL – oldboy Jul 24 '19 at 01:24
23

@Adrian Cornish's answer is correct. However, there is another caveat to dropping an existing primary key. If that primary key is being used as a foreign key by another table you will get an error when trying to drop it. In some versions of mysql the error message there was malformed (as of 5.5.17, this error message is still

alter table parent  drop column id;
ERROR 1025 (HY000): Error on rename of
'./test/#sql-a04_b' to './test/parent' (errno: 150).

If you want to drop a primary key that's being referenced by another table, you will have to drop the foreign key in that other table first. You can recreate that foreign key if you still want it after you recreate the primary key.

Also, when using composite keys, order is important. These 

1) ALTER TABLE provider ADD PRIMARY KEY(person,place,thing);
and
2) ALTER TABLE provider ADD PRIMARY KEY(person,thing,place);

are not the the same thing. They both enforce uniqueness on that set of three fields, however from an indexing standpoint there is a difference. The fields are indexed from left to right. For example, consider the following queries:

A) SELECT person, place, thing FROM provider WHERE person = 'foo' AND thing = 'bar';
B) SELECT person, place, thing FROM provider WHERE person = 'foo' AND place = 'baz';
C) SELECT person, place, thing FROM provider WHERE person = 'foo' AND place = 'baz' AND thing = 'bar';
D) SELECT person, place, thing FROM provider WHERE place = 'baz' AND thing = 'bar';

B can use the primary key index in ALTER statement 1
A can use the primary key index in ALTER statement 2
C can use either index
D can't use either index

A uses the first two fields in index 2 as a partial index. A can't use index 1 because it doesn't know the intermediate place portion of the index. It might still be able to use a partial index on just person though.

D can't use either index because it doesn't know person.

See the mysql docs here for more information.

cs_alumnus
  • 1,419
  • 15
  • 23
19

You may simply want a UNIQUE CONSTRAINT. Especially if you already have a surrogate key. (example of an already existing surrogate key would be a single column that is an AUTO_INCREMENT )

Below is the sql code for a Unique Constraint

ALTER TABLE `MyDatabase`.`Provider`
    ADD CONSTRAINT CK_Per_Place_Thing_Unique UNIQUE (person,place,thing)
;
granadaCoder
  • 21,474
  • 7
  • 81
  • 117
  • Thank you, a constraint is what I wanted, I didn't know what to ask for in this initial post. Thanks for adding this to the thread. – ZaneDarken Dec 12 '14 at 21:47
  • I typically use a surrogate key......then add a unique constraint. This way....if the "uniqueness" changes down the road, it is not much drama to tweak the constraint, vs messing with the primary-key. And if you have child tables that foreign-key references this table, you only have to FK the surrogate-key, not all 3 columns. – – granadaCoder Dec 23 '14 at 14:42
3
alter table table_name add primary key (col_name1, col_name2);
Naveen Kumar Alone
  • 7,142
  • 5
  • 32
  • 50
1

It`s definitely better to use COMPOSITE UNIQUE KEY, as @GranadaCoder offered, a little bit tricky example though:

ALTER IGNORE TABLE table_name ADD UNIQUES INDEX idx_name(some_id, another_id, one_more_id);

Arthur Kushman
  • 2,809
  • 9
  • 44
  • 64
0

ALTER TABLE table_name DROP PRIMARY KEY,ADD PRIMARY KEY (col_name1, col_name2);

Lucky
  • 14,677
  • 16
  • 104
  • 145