MySQL Bugs: #23081: false primary key (only a unique key) makes changes on the column difficult

Bug #23081	false primary key (only a unique key) makes changes on the column difficult
Submitted:	7 Oct 2006 23:23	Modified:	17 Oct 2006 15:58
Reporter:	Cezary Okupski	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Administrator	Severity:	S3 (Non-critical)
Version:	1.2.4rc	OS:	Any (any)
Assigned to:	Vladimir Kolesnikov	CPU Architecture:	Any
Tags:	auto_increment, key, primary, unique

Description:
There is an issue with MySQL Server described in the report #22520 that would be fixed in 5.1 series although it would be nice if you provided a workaround for MySQL Administrator. The problem is that if a table hasn't got a primary key and has got a unique key Server reports it is a primary key. This fact misleads MySQL Administrator too and some particular changes on the column with this pseudo-primary key result in DROP PRIMARY KEY, ADD PRIMARY KEY… thus raising an error.

How to repeat:
1) Connect to a database
2) Create a table called for example `test` with one column `id` that is NOT NULL
3) Add a unique key on the only column and click Apply

Variant A
4) Go to column details, uncheck Primary Key as this was not what you had intended
5) Make any other modifications if you wish (I am suggesting it because you'll finally lose it ;>) and click Apply

Variant B
4) Try to add Auto Increment to the only column as you may be convinced by GUI there is a primary key
5) Make any other modifications if you wish (I am suggesting it because you'll finally lose it ;>) and click Apply

Error executing SQL commands to update table.
Can't DROP 'PRIMARY'; check that column/key exists (error 1091)

I haven't found "GUI method" to fix that table so that it has a new primary key because of "DROP PRIMARY KEY, ADD PRIMARY KEY(`id`)" occurring in every query. 

Suggested fix:
It is essential that Table Editor knows for elder Server versions a unique key is not a primary key.

Thank you for the report.

Verified as described.

Altough is possible to classify variant A as feature request, variant B is surely MySQL Administrator bug.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Workaround for the server bug implemented