Bug #8863 INNODB: FOREIGN KEY cannot be dropped.

• Submitted: 1 Mar 2005 13:28
• Modified: 1 Mar 2005 20:07
• Reporter: Disha
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.0.2
• OS: Windows (Windows 2003)
• 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