Bug #10666 Primary key that is added on child after creating foreign key cannot be dropped

• Submitted: 16 May 2005 12:03
• Modified: 19 May 2005 8:30
• Reporter: Disha
• Status: Won't fix
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.0.5 Beta
• OS: Windows (Windows 2000 Server)
• Assigned to: Heikki Tuuri
• CPU Architecture: Any

[16 May 2005 12:03] Disha

Description:
If primary key is added on a child table when foreign key already exists and we try to drop the primary key then error is displayed.

How to repeat:
1. Start the MySQL client and connect to the database with valid user and password.
2. Set the delimiter to //
3. Create tables t1 and t2 as follows:
   create table t1(f1 int, primary key(f1)) engine=innodb//
   create table t2(f1 int, foreign key(f1) references t1(f1)) engine=innodb//

4. Now add primary key on child table t2 and drop it as follows:
   alter table t2 add primary key (f1)//
   alter table t2 drop primary key//
   
Expected Results: 
1. Either (a) it should no allow adding primary key on the table or (b) if allowed to add primary key then dropping of the same should be allowed.

Actual Results: 
1. Though adding primary key is allowed, dropping of the same is not allowed.
2. When tried to drop the primary key error related to foreign key constraint is displayed as follows:
ERROR 1025 (HY000): Error on rename of '.\test\#sql-758_1' to '.\test\t2' (errno: 150)

[16 May 2005 12:17] Jan Lindström

InnoDB needs indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. Starting with MySQL 4.1.2, these indexes are created automatically. If you drop primary key created to foreign key column, InnoDB should 
automatically create a new index for the foreign key column.

[19 May 2005 8:30] Heikki Tuuri

Hi!

I think this is a marginal case, and we will not fix this.

Regards,

Heikki