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
Triage: Triaged: D2 (Serious)

[11 Dec 2007 10:57] Paul Keenan
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%
[11 Dec 2007 11:16] Valeriy Kravchuk
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.
[11 Dec 2007 11:22] Paul Keenan
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>
[11 Dec 2007 11:25] Susanne Ebrecht
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.
[17 Sep 2010 8:03] Konstantin Osipov
See also Bug#11228.