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!
If there are multiple primary keys, will it affect the speed of query execution ?
@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.
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.
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