Bug #45787 ALTER TABLE doesn't differentiate between unique and foreign key

• Submitted: 26 Jun 2009 11:35
• Modified: 26 Jun 2009 13:10
• Reporter: Björn Lindqvist
• Status: Closed
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.0.67
• OS: Linux
• CPU Architecture: Any

[26 Jun 2009 11:35] Björn Lindqvist

Description:
These two sql statements have different results in MySQL 5.0.67 and later MySQL versions. On 5.0.67, the following happens:

mysql> create table bajs (email varchar(200), unique key unq_email (email));
Query OK, 0 rows affected (0.02 sec)

mysql> alter table bajs drop foreign key unq_email;
ERROR 1025 (HY000): Error on rename of './test/bajs' to './test/#sql2-f61-1a33' (errno: 152)

on 5.0.75 and later:

mysql> create table bajs (email varchar(200), unique key unq_email (email));
Query OK, 0 rows affected (0.00 sec)

mysql> alter table bajs drop foreign key unq_email;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Which version is correct? Note the ALTER TABLE statements "drop foreign key unq_email" clause, but unq_email is a unique key. Is it allowed to drop a "unique key" using "drop foreign key" or not?

How to repeat:
See above

[26 Jun 2009 12:36] Valeriy Kravchuk

Thank you for the problem report. I think this happened just because in older version table was created as InnoDB while in a new one it is MyISAM (and for these tables foreign key references are just silently ignored). Look:

mysql> show create table bajs\G
*************************** 1. row ***************************
       Table: bajs
Create Table: CREATE TABLE `bajs` (
  `email` varchar(200) default NULL,
  UNIQUE KEY `unq_email` (`email`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

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

mysql> show create table bajs\G
*************************** 1. row ***************************
       Table: bajs
Create Table: CREATE TABLE `bajs` (
  `email` varchar(200) default NULL,
  UNIQUE KEY `unq_email` (`email`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table bajs engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table bajs\G
*************************** 1. row ***************************
       Table: bajs
Create Table: CREATE TABLE `bajs` (
  `email` varchar(200) default NULL,
  UNIQUE KEY `unq_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>  alter table bajs drop foreign key unq_email;
ERROR 1025 (HY000): Error on rename of './test/bajs' to './test/#sql2-3b37-15' (errno: 152)
mysql> show create table bajs\G
*************************** 1. row ***************************
       Table: bajs
Create Table: CREATE TABLE `bajs` (
  `email` varchar(200) default NULL,
  UNIQUE KEY `unq_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.84-debug | 
+--------------+
1 row in set (0.00 sec)

In both cases, as you can see, UNIQUE key is NOT dropped. I think this is not a bug. Please, check.

[26 Jun 2009 13:10] Björn Lindqvist

Yes, you are correct. The new MySQL:s create MyISAM tables by default while the older one used InnoDB, which I missed. But isn't it strange that 5.0.67 creates InnoDB and 5.0.75 creates MyISAM? :)