MySQL Multiple Primary Key Columns In A Table

Some database management tools refuse to admit you can add multiple primary key columns to one table, so you might have to do it with direct sql syntax ( deep intake of breath ).

Multiple primary keys make ‘ON DUPLICATE KEY UPDATE column = column + 100’ type queries a shed load more flexible.

Anyway, on with the code:

ALTER TABLE tablename ADD PRIMARY KEY (column_one, column_two)

And there you have it. Robert’s your fathers brother.

  1. Sweet – thanks for the tip, wish I would have found this before wasting an hour of time!

  2. @Senthil – pretty sure that primary keys will slow down insert and update calls but increase the speed of selects. It’s a trade off that you must consider with each different scenario you come across.

  3. I am aware that you can create more than one primary key in a single MySql table, but my problem lies in the establishment of master-detail relationship. I want another table which accesses my second primary key. In effect this will become my foreign key. When I try to establish a foreign key in another table (in Myql Query Browser), I get an error.

Comments are closed.