MySQL Bugs: #33134: describe table output wrong for unique key when primary key is dropped

Bug #33134	describe table output wrong for unique key when primary key is dropped
Submitted:	11 Dec 2007 10:57	Modified:	11 Dec 2007 11:25
Reporter:	Paul Keenan	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.0.51	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	DESCRIBE, key, primary, unique

Description:
I created a table with two non-NULL integers.  The first is designated as the primary key.  The second is designated a unique key.  The "describe table" shows the keys as "PRI" and "UNI" respectively.

When I drop the primary key, the "describe table" command wrongly now describes the key on the second integer as the primary key, i.e. it shows the key as "PRI" instead of "UNI".  The key on the second integer, while being unique and non-null is nevertheless NOT the primary key, as an attempt to drop the primary key will confirm.

How to repeat:
mysql> create table t1 (i1 int not null, i2 int not null, primary key (i1), unique key (i2));
Query OK, 0 rows affected (0.05 sec)

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i1    | int(11) | NO   | PRI |         |       |
| i2    | int(11) | NO   | UNI |         |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table t1 drop primary key;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i1    | int(11) | NO   |     |         |       |
| i2    | int(11) | NO   | PRI |         |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table t1 drop primary key;
ERROR 1091 (42000): Can't DROP 'PRIMARY'; check that column%

Thank you for a problem report. Looks like your table is created as InnoDB one. Please, send the results of

show create table t1\G

to check that.

Yes, it's InnoDB :

mysql> show create table t1;
+-------+-----------------------------------------------------------------------
----------------------------------------------------------------+
| Table | Create Table
                                                                |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `i1` int(11) NOT NULL,
  `i2` int(11) NOT NULL,
  UNIQUE KEY `i2` (`i2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Verified as described.

mysql> create table t1 (id integer not null, num integer not null, primary key(id), unique(num));
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       | 
| num   | int(11) | NO   | UNI | NULL    |       | 
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `num` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `num` (`num`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table t1 drop primary key;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       | 
| num   | int(11) | NO   | PRI | NULL    |       | 
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `num` int(11) NOT NULL,
  UNIQUE KEY `num` (`num`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

It seems, that "desc" just print out wrong informations.

See also Bug#11228.