|
in resulting table there is no primary key:
the warning is also in error as primary keys shouldn't have names.
CREATE TABLE b (`transaction_id` int(11) NOT NULL DEFAULT '0', KEY `transaction_id` (`transaction_id`));
|
Query OK, 0 rows affected (0.02 sec)
|
|
alter table b DROP KEY IF EXISTS transaction_id, ADD PRIMARY KEY IF NOT EXISTS (transaction_id);
|
Query OK, 0 rows affected, 1 warning (0.00 sec)
|
Records: 0 Duplicates: 0 Warnings: 1
|
|
show warnings;
|
+-------+------+-------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-------------------------------------+
|
| Note | 1061 | Duplicate key name 'transaction_id' |
|
+-------+------+-------------------------------------+
|
1 row in set (0.00 sec)
|
|
show create table b;
|
+-------+-----------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-----------------------------------------------------------------------------------------------------------+
|
| b | CREATE TABLE `b` (
|
`transaction_id` int(11) NOT NULL DEFAULT '0'
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-----------------------------------------------------------------------------------------------------------+
|
If the original key was called something other than the same as the column name it works
CREATE TABLE b (`transaction_id` int(11) NOT NULL DEFAULT '0', KEY `t` (`transaction_id`));
|
Query OK, 0 rows affected (0.02 sec)
|
|
alter table b DROP KEY IF EXISTS t, ADD PRIMARY KEY IF NOT EXISTS (transaction_id);
|
Query OK, 0 rows affected (0.03 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
show create table b;
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
|
| b | CREATE TABLE `b` (
|
`transaction_id` int(11) NOT NULL DEFAULT '0',
|
PRIMARY KEY (`transaction_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
|
It is the IF NOT EXISTS on PRIMARY KEY that is the critical aspect.
CREATE TABLE b (`transaction_id` int(11) NOT NULL DEFAULT '0', KEY `transaction_id` (`transaction_id`));
|
Query OK, 0 rows affected (0.03 sec)
|
|
alter table b DROP KEY transaction_id, ADD PRIMARY KEY IF NOT EXISTS (transaction_id);
|
Query OK, 0 rows affected, 1 warning (0.00 sec)
|
Records: 0 Duplicates: 0 Warnings: 1
|
|
show warnings;
|
+-------+------+-------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-------------------------------------+
|
| Note | 1061 | Duplicate key name 'transaction_id' |
|
+-------+------+-------------------------------------+
|
1 row in set (0.01 sec)
|
|
show create table b;
|
+-------+-----------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-----------------------------------------------------------------------------------------------------------+
|
| b | CREATE TABLE `b` (
|
`transaction_id` int(11) NOT NULL DEFAULT '0'
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-----------------------------------------------------------------------------------------------------------+
|
Without IF NOT EXISTS it works fine.
CREATE TABLE b (`transaction_id` int(11) NOT NULL DEFAULT '0', KEY `transaction_id` (`transaction_id`));
|
Query OK, 0 rows affected (0.00 sec)
|
|
alter table b DROP KEY transaction_id, ADD PRIMARY KEY (transaction_id);
|
Query OK, 0 rows affected (0.02 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
show create table b;
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
|
| b | CREATE TABLE `b` (
|
`transaction_id` int(11) NOT NULL DEFAULT '0',
|
PRIMARY KEY (`transaction_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
|
|