ALTER TABLE to add a composite primary key

mysql sql primary-key alter-table composite-primary-key
David542 picture David542 · Jan 14, 2012 · Viewed 341.1k times · Source

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?

Answer

Adrian Cornish picture Adrian Cornish · Jan 14, 2012 
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);

Related questions

How to 'insert if not exists' in MySQL?

I started by googling, and found this article which talks about mutex tables. I have a table with ~14 million records. If I want to add more data in the same format, is there a way to ensure the record I …

Read More
difference between primary key and unique key

I'm using mysql database. I have a confusion between primary key and unique key. Please help me where should I create primary and unique key. I mean in which situation we create unique key or primary key .

Read More
Remove Primary Key in MySQL

I have the following table schema which maps user_customers to permissions on a live MySQL database: mysql> describe user_customer_permission; +------------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+---------+------+-----+---------+…

Read More