MySQL Bugs: #49308: Checking multiple PK columns causes exiting PK to be dropped

Bug #49308	Checking multiple PK columns causes exiting PK to be dropped
Submitted:	1 Dec 2009 22:34	Modified:	16 Jan 2010 15:37
Reporter:	Todd Farmer (OCA)	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Workbench: Documentation	Severity:	S2 (Serious)
Version:	5.2.10 r4680	OS:	Windows (XP)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Using the ALTER TABLE wizard to modify a table which already has a primary key, I added a column, and checked the PK column (leaving the existing column checked).  Rather than create this as a composite primary key, the resulting DDL script first dropped the existing primary key, then added the new one.  That is inappropriate, and puts the actual table at odds with the graphical representation (which still shows both columns with PK checked).

How to repeat:
Create a table with a PK column
Alter that table, adding a new column and checking the PK flag.

Suggested fix:
1.  If multiple columns cannot be checked to designate a composite PK (and it's quite likely that they should not, since the order of the columns may well matter), uncheck existing PK columns when a new column is selected as PK.
2.  If multiple columns *can* be checked to designate a composite PK, generate the correct DDL.

This already works fine but it needs to get documented.

Here is how it works:

1) Add a new diagram
2) Place a table
3) three columns in the table i,j,k (for testing all could be integer)
4) mark all three as PK
5) go to tab indexes
6) here you can change the sequence just change j=1, k=2, i=3 for getting primary key(j,k,i)
7) Forward Engineering

CREATE TABLE IF NOT EXISTS mydb.table1(
i INT NOT NULL,
j INT NOT NULL,
k INT NOT NULL.
PRIMARY KEY(j,k,i))
ENGINE=INNODB;

I've updated the docs to mention that when creating a composite primary key it is possible to select the correct order from the Indexes tab.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".