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:
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

[16 Mar 2005 8:43] Carsten Pedersen
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
[27 Oct 2006 0:08] Bugs System
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:


ChangeSet@1.2535, 2006-10-26 17:08:03-07:00, acurtis@xiphis.org +3 -0
    "Error 1068 when re-defining PK"
    Check if redeclaration of PK is identical and ignore if it is.
[6 Oct 2008 20:52] Konstantin Osipov
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
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.