ALTER TABLE to add a composite primary key

Question

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

User · Answer

ALTER TABLE table name DROP PRIMARY KEY ADD PRIMARY KEY  col name1  col name2

User · Answer

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

User · Answer

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

User · Answer

alter table table name add primary key  col name1  col name2

User · Answer

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

User · Answer

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

[mysql] ALTER TABLE to add a composite primary key

Examples related to mysql

Examples related to sql

Examples related to primary-key

Examples related to alter-table

Examples related to composite-primary-key