MySQL Bugs: #59668: Deletion of a recursive foreign key deletes the primary key

Bug #59668	Deletion of a recursive foreign key deletes the primary key
Submitted:	21 Jan 2011 21:09	Modified:	8 Mar 2011 15:06
Reporter:	Ral Carbonneau	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S2 (Serious)
Version:	5.2.31 CE Revision 7115	OS:	Any (Windows, Mac OS X)
Assigned to:	Alfredo Kojima	CPU Architecture:	Any
Tags:	delete, foreign key, primary key, recursive

Description:
When deleting a recurisve foreign key contraint, the primary key is deleted.  This should not occur, only the foreign key contraint should be deleted.

How to repeat:
1) Create a table with a recursive relationship.
CREATE  TABLE `test` (
  `ID` INT NOT NULL AUTO_INCREMENT ,
  `ParentID` INT NULL ,
  PRIMARY KEY (`ID`) );

2) Create the recursive foreign key contraint:
ALTER TABLE `test` 
  ADD CONSTRAINT `test-ParentID`
  FOREIGN KEY (`ID` )
  REFERENCES `test` (`ID` )
  ON DELETE RESTRICT
  ON UPDATE RESTRICT
, ADD INDEX `ParentID` (`ID` ASC) ;

3) In SQL Workbench, right-click on the test table and click "Alter Table" ...

4) Click on "Foreign Keys" tab

5) Right click on "ParentID" foreign key and click "Delete selected FKs"

6) Click Apply, the following SQL commands are generated
ALTER TABLE `test` DROP FOREIGN KEY `test-ParentID` ;
ALTER TABLE `test` DROP PRIMARY KEY ;

The DROP PRIMARY KEY is INCORRECT.

Suggested fix:
The generated SQL should not include "ALTER TABLE `test` DROP PRIMARY KEY ;"

Thank you for the bug report. Verified just as described when working with current MySQL 5.1 server.

fix confirmed in repository

An entry has been added to the 5.2.32 changelog: 

        When deleting a recursive foreign key contraint, the primary key 
        was also mistakenly deleted.