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.
Sweet – thanks for the tip, wish I would have found this before wasting an hour of time!
Posted by brian on May 29th, 2009.
If there are multiple primary keys, will it affect the speed of query execution ?
Posted by Senthil on August 31st, 2009.
@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.
Posted by Harry on August 31st, 2009.
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.
Posted by Benj on August 16th, 2010.
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
Posted by Harry on August 16th, 2010.