Bug #8863 INNODB: FOREIGN KEY cannot be dropped.
Submitted: 1 Mar 2005 13:28 Modified: 1 Mar 2005 20:07
Reporter: Disha Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.0.2 OS:Microsoft Windows (Windows 2003)
Assigned to: CPU Architecture:Any

[1 Mar 2005 13:28] Disha
Description:
If we create FOREIGN KEY in a table on INNODB and then drop it, the SHOW INDEX FROM statement still displays the key name even though the FOREIGN KEY is dropped.

How to repeat:
1. Use database 'test' i.e. execute the following SQL statement:
             use test;
2. Create and Run the follwing query:

	drop table t1;
	drop table t2;
	create table t1 (a1 int not null, a2 char not null, primary key (a1));
	create table t2 (a1 int not null, a2 char not null);
	alter table t2 add constraint fk_1 foreign key (a1) references t1 (a1);
	show index from t2;
	ALTER TABLE t2 DROP FOREIGN KEY fk_1;
	show index from t2;

3. Observe that the execution dispalys "fk_1" at column "Key_name".

Expected Results: 
The foreign key should get dropped and SHOW INDEX should not display "fk_1" at column "Key_name".
    
Actual Results  : 
The DROP FOREIGN key statement executes successfully but the SHOW INDEX displays "fk_1" at column "Key_name" indicating that the key was not dropped
[1 Mar 2005 14:31] MySQL Verification Team
When you create foreign key constraints, index on the column is automatically created. When you drop foreign key, of course only foreign key is dropped.

mysql> show create table t2;
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `a1` int(11) NOT NULL,
  `a2` char(1) NOT NULL,
  KEY `fk_1` (`a1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[1 Mar 2005 20:07] Heikki Tuuri
Hi!

I could add to Victoria's fine reply that the FOREIGN KEY name is actually the CONSTRAINT name. Dropping the constraint does not require dropping the index.

Regards,

Heikki