Description:
When dropping a PRIMARY KEY, information_schema.columns is not updated if the PRIMARY KEY is also declared as UNIQUE.
How to repeat:
CREATE TABLE t0(c0 INT PRIMARY KEY UNIQUE);
ALTER TABLE t0 DROP PRIMARY KEY;
SELECT COLUMN_KEY, COLUMN_NAME from information_schema.columns WHERE TABLE_NAME = 't0'; -- unexpected: c0 is still shown as PRI
ALTER TABLE t0 DROP PRIMARY KEY; -- ERROR 1091 (42000): Can't DROP 'PRIMARY'; check that column/key exists
The SELECT displays the following:
+------------+-------------+
| COLUMN_KEY | COLUMN_NAME |
+------------+-------------+
| PRI | c1 |
+------------+-------------+
This seems to suggest that the primary key was not removed. However, the subsequent DROP PRIMARY KEY results in an error, which indicates that the PRIMARY KEY had actually been removed.
When not declaring the PRIMARY KEY as UNIQUE, the SELECT works as expected:
CREATE TABLE t0(c0 INT PRIMARY KEY);
ALTER TABLE t0 DROP PRIMARY KEY;
SELECT COLUMN_KEY, COLUMN_NAME from information_schema.columns WHERE TABLE_NAME = 't0';
+------------+-------------+
| COLUMN_KEY | COLUMN_NAME |
+------------+-------------+
| | c0 |
+------------+-------------+
