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.

5 comments.

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

  2. If there are multiple primary keys, will it affect the speed of query execution ?

  3. @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.

  4. 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.

  5. Hi Benj,

    you might want to take a look at this article:

    http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.sqls.doc/ids_sqs_0535.htm