MySQL Bugs: #54951: Creating a unique key across fields that have foreign keys, deletes first FK

Bug #54951	Creating a unique key across fields that have foreign keys, deletes first FK
Submitted:	2 Jul 2010 7:51	Modified:	27 Jul 2011 21:32
Reporter:	Florian Beese	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.1.41-3ubuntu12.3, 5.1.49-bzr	OS:	Linux (Ubuntu)
Assigned to:		CPU Architecture:	Any
Tags:	foreign, key, removed, unique

Description:
When I have a table that contains 2 foreign keys and I add a unique key across the two fields that have the foreign keys, the first key is gone.

The constraints survive, but the key on the foreign key column is gone. Its ever the key on the first column that is used in the unique key.

A workaround is, to add the key again after it was accidentally removed. For the example below:

ALTER TABLE `testdb`.`tabB_C` ADD KEY `FK__tabB_C__tabB` (`B_ID`);

I also realized, that its somehow possible to add a KEY with the same names again (`FK__tabB_C__tabC` from example) even if they exist. That behavior is a bit strange to me either.

How to repeat:
# Use the following example code, just copy-and-paste:

#
# Example Code related to my problem...
#

CREATE DATABASE `testdb`;
USE `testdb`;

CREATE TABLE `testdb`.`tabB_C` (
  `ID` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
  `B_ID` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
  `C_ID` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`ID`)
)
ENGINE = MyISAM;

CREATE TABLE `testdb`.`tabB` (
  `ID` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
  `c2` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
  `c3` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`ID`)
)
ENGINE = MyISAM;

CREATE TABLE `testdb`.`tabC` (
  `ID` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
  `c2` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
  `c3` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`ID`)
)
ENGINE = MyISAM;

#Make em to InnoDB:
ALTER TABLE `testdb`.`tabB_C` ENGINE = InnoDB;
ALTER TABLE `testdb`.`tabB` ENGINE = InnoDB;
ALTER TABLE `testdb`.`tabC` ENGINE = InnoDB;

#Now we alter the table and add the keys:

ALTER TABLE `testdb`.`tabB_C` ADD CONSTRAINT `FK__tabB_C__tabB` FOREIGN KEY `FK__tabB_C__tabB` (`B_ID`)
    REFERENCES `tabB` (`ID`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT;

ALTER TABLE `testdb`.`tabB_C` ADD CONSTRAINT `FK__tabB_C__tabC` FOREIGN KEY `FK__tabB_C__tabC` (`C_ID`)
    REFERENCES `tabC` (`ID`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT;

SHOW CREATE TABLE tabB_C;

#SHOW CREATE TABLE HERE:
#
#CREATE TABLE `tabB_C` (
#  `ID` bigint(20) unsigned NOT NULL DEFAULT '0',
#  `B_ID` bigint(20) unsigned NOT NULL DEFAULT '0',
#  `C_ID` bigint(20) unsigned NOT NULL DEFAULT '0',
#  PRIMARY KEY (`ID`),
#  KEY `FK__tabB_C__tabB` (`B_ID`),
#  KEY `FK__tabB_C__tabC` (`C_ID`),
#  CONSTRAINT `FK__tabB_C__tabC` FOREIGN KEY (`C_ID`) REFERENCES `tabC` (`ID`),
#  CONSTRAINT `FK__tabB_C__tabB` FOREIGN KEY (`B_ID`) REFERENCES `tabB` (`ID`)
#) ENGINE=InnoDB DEFAULT CHARSET=latin1
#

#depending on what col you refer first in the key, the key for the foreign key is gone...
ALTER TABLE `testdb`.`tabB_C` ADD UNIQUE INDEX `ix_UNIQUE` USING BTREE(`B_ID`, `C_ID`);
#ALTER TABLE `testdb`.`tabB_C` ADD UNIQUE INDEX `ix_UNIQUE` USING BTREE(`C_ID`, `B_ID`);

SHOW CREATE TABLE tabB_C;

# CREATE TABLE `tabB_C` (
#   `ID` bigint(20) unsigned NOT NULL DEFAULT '0',
#   `B_ID` bigint(20) unsigned NOT NULL DEFAULT '0',
#   `C_ID` bigint(20) unsigned NOT NULL DEFAULT '0',
#   PRIMARY KEY (`ID`),
#   UNIQUE KEY `ix_UNIQUE` (`B_ID`,`C_ID`) USING BTREE,
#   KEY `FK__tabB_C__tabC` (`C_ID`),
#   CONSTRAINT `FK__tabB_C__tabB` FOREIGN KEY (`B_ID`) REFERENCES `tabB` (`ID`),
#   CONSTRAINT `FK__tabB_C__tabC` FOREIGN KEY (`C_ID`) REFERENCES `tabC` (`ID`)
# ) ENGINE=InnoDB DEFAULT CHARSET=latin1

#The key `FK__tabB_C__tabB` is gone...
#if you used the second statement to create the unique key, the key `FK__tabB_C__tabC` will be removed, because its mentioned first.

Suggested fix:
Don't delete the key!?

Verified just as described with recent 5.1.49 from bzr:

...
mysql> SHOW CREATE TABLE tabB_C\G
*************************** 1. row ***************************
       Table: tabB_C
Create Table: CREATE TABLE `tabB_C` (
  `ID` bigint(20) unsigned NOT NULL DEFAULT '0',
  `B_ID` bigint(20) unsigned NOT NULL DEFAULT '0',
  `C_ID` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `FK__tabB_C__tabB` (`B_ID`),
  KEY `FK__tabB_C__tabC` (`C_ID`),
  CONSTRAINT `FK__tabB_C__tabC` FOREIGN KEY (`C_ID`) REFERENCES `tabc` (`ID`),
  CONSTRAINT `FK__tabB_C__tabB` FOREIGN KEY (`B_ID`) REFERENCES `tabb` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> ALTER TABLE `testdb`.`tabB_C` ADD UNIQUE INDEX `ix_UNIQUE` USING BTREE(`B_ID`, `C_ID`);
Query OK, 0 rows affected (0.64 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE tabB_C\G*************************** 1. row ***************************
       Table: tabB_C
Create Table: CREATE TABLE `tabB_C` (
  `ID` bigint(20) unsigned NOT NULL DEFAULT '0',
  `B_ID` bigint(20) unsigned NOT NULL DEFAULT '0',
  `C_ID` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ix_UNIQUE` (`B_ID`,`C_ID`) USING BTREE,
  KEY `FK__tabB_C__tabC` (`C_ID`),
  CONSTRAINT `FK__tabB_C__tabC` FOREIGN KEY (`C_ID`) REFERENCES `tabc` (`ID`),
  CONSTRAINT `FK__tabB_C__tabB` FOREIGN KEY (`B_ID`) REFERENCES `tabb` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Closing with a doc change, to clarify that the auto-created index can be auto-dropped later if no longer needed because of explicit index creation. Fix is in http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html and corresponding pages for 5.5 and 5.6 Ref Man.