MySQL Bugs: #9215: Error 1068 when re-defining PK

Bug #9215	Error 1068 when re-defining PK
Submitted:	16 Mar 2005 8:43	Modified:	6 Oct 2008 20:52
Reporter:	Carsten Pedersen	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	4.1.7-standard-log	OS:	Linux (SuSE Linux 9.2)
Assigned to:		CPU Architecture:	Any

Description:
During ALTER TABKLE, re-specifying the PRIMARY KEY for a field which is already the PK yields error 1068

How to repeat:
create table t (i int not null primary key);

alter table t change i i tinyint; # works, i is still the PK

drop table t;

create table t (i int not null primary key); # same as before

alter table t change i i tinyint primary key; # now with "primary key"
# --> ERROR 1068 (42000): Multiple primary key defined

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14455

ChangeSet@1.2535, 2006-10-26 17:08:03-07:00, acurtis@xiphis.org +3 -0
  Bug#9215
    "Error 1068 when re-defining PK"
    Check if redeclaration of PK is identical and ignore if it is.

Some users may expect standard-compliant semantics, (i.e. an error) quoting a mail exchange with PeterG:
--------------------------------------------------
Konstantin Osipov wrote:
> Hi,
>
> What is the standard behavior in case of Bug#9215?

This is not standard syntax, but let's assume the statement was
ALTER TABLE t ADD PRIMARY KEY (i);
Syntactically this is an <add table constraint definition> of a
unique constraint (primary key constraints are also unique constraints),
so the following statement in the SQL/Foundation document applies:
"
11.7 <unique constraint definition>
...
Syntax Rules
...
7) If a <unique constraint definition> that specifies PRIMARY KEY is contained
in an <add table constraint definition>, then the table identified by the <table
name> immediately contained in the containing <alter table statement> shall not
have a unique constraint that was defined by a <unique constraint definition>
that specified PRIMARY KEY.
"
Therefore "ALTER TABLE t ADD PRIMARY KEY (i)" should cause a syntax error
if there is already a primary key, even if the original primary key was on
column i. The statement "alter table t change i i tinyint primary key;"
is in my opinion an analogous case, so it should cause a syntax error too.
----------------------------------------