Bug #2361 ALTER TABLE ... DROP PRIMARY KEY drops a non-primary key

• Submitted: 12 Jan 2004 12:22
• Modified: 14 Jan 2004 4:05
• Reporter: Peter Gulutzan
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.2
• OS: Linux (SuSE 8.2)
• Assigned to: Ramil Kalimullin
• CPU Architecture: Any

[12 Jan 2004 12:22] Peter Gulutzan

Description:
ALTER TABLE ... DROP PRIMARY KEY drops a UNIQUE key which is not primary. 
 
I am aware that this is documented behaviour. The MySQL Reference Manual says: 
 
"DROP PRIMARY KEY drops the primary index. If no such index exists, it drops the first 
UNIQUE index in the table. (MySQL marks the first UNIQUE key as the PRIMARY KEY if 
no PRIMARY KEY was specified explicitly.)" 
 
Nevertheless we should fix it because it is unexpected behaviour. 
 
I have the agreement of Brian Aker on this matter. 
 

How to repeat:
mysql> create table t22 (s1 int unique); 
Query OK, 0 rows affected (0.32 sec) 
 
mysql> show create table t22; 
+-------+-------------------------------------------------------------------------------------------------------------------+ 
| Table | Create Table                                                                                                      | 
+-------+-------------------------------------------------------------------------------------------------------------------+ 
| t22   | CREATE TABLE `t22` ( 
  `s1` int(11) default NULL, 
  UNIQUE KEY `s1` (`s1`) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+-------+-------------------------------------------------------------------------------------------------------------------+ 
1 row in set (0.00 sec) 
 
mysql> alter table t22 drop primary key; 
Query OK, 0 rows affected (0.29 sec) 
Records: 0  Duplicates: 0  Warnings: 0 
 
mysql> show create table t22; 
+-------+-----------------------------------------------------------------------------------------+ 
| Table | Create Table                                                                            | 
+-------+-----------------------------------------------------------------------------------------+ 
| t22   | CREATE TABLE `t22` ( 
  `s1` int(11) default NULL 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+-------+-----------------------------------------------------------------------------------------+ 
1 row in set (0.00 sec) 
 

Suggested fix:
Don't drop a unique key if the instruction is to drop a primary key.

[14 Jan 2004 4:05] Ramil Kalimullin

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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

[19 May 2008 5:49] vutukuri venkatesh

CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);

from the above table is created using primary key for 'id' field , then i want drop primary key for the 'id' field in this table. can any 1 tell me how to drop a primary for this table...pls answer me with "syntax " & clear details

venkatesh.vutukuri