MySQL Bugs: #68286: Cannot change FK constraint settings

Bug #68286	Cannot change FK constraint settings
Submitted:	6 Feb 2013 5:42	Modified:	6 Feb 2013 15:01
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.1, 5.5(.29), 2.6.7	OS:	Windows (probably any)
Assigned to:		CPU Architecture:	Any

Description:
A Foreign Key CONSTRAINT exists (defined like "CONSTRAINT `fk_inventory_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`)"), but needs to be altered to include "ON DELETE CASCADE ON UPDATE CASCADE".  

I drop the CONSTRAINT and create again like in script below. The script in case is generated by an application/GUI tool. 

All server versions from 5.1 fail with ALTER TABLE the script (I did not try 5.0).  The error with MySQL 5.6 is different than with 5.1 and 5.5. Seems like a temporary table issue. 

For simplicity I have only one table (the 'child' table) in test case script (and "SET FOREIGN_KEY_CHECKS = 0" to run the script).  But it is also reproducible live with existing parent tables. The test case script below uses a table from sakila database (what the real-life case does not)

How to repeat:
USE pl;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

CREATE TABLE `sometab` (
  `inventory_id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `film_id` SMALLINT(5) UNSIGNED NOT NULL,
  `store_id` TINYINT(3) UNSIGNED NOT NULL,
  `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`inventory_id`),
  KEY `idx_fk_film_id` (`film_id`),
  KEY `idx_store_id_film_id` (`store_id`,`film_id`),
  CONSTRAINT `fk_inventory_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`),
  CONSTRAINT `fk_inventory_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`)
) ENGINE=INNODB AUTO_INCREMENT=4582 DEFAULT CHARSET=utf8;

/* Alter table in target */
ALTER TABLE `sometab` 
	DROP FOREIGN KEY `fk_inventory_film`  , 
	ADD CONSTRAINT `fk_inventory_film` 
	FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON DELETE CASCADE ON UPDATE CASCADE , 
	DROP FOREIGN KEY `fk_inventory_store`  , 
	ADD CONSTRAINT `fk_inventory_store` 
	FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE ;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/* returns

MySQL 5.5 error:
Error Code: 1005
Can't create table 'import.#sql-9b0_1' (errno: 121) 

MySQL 5.6 error:
Error Code: 1826
Duplicate foreign key constraint name 'pl/fk_inventory_film'

*/

Actually it does not matter if I shorten the ALTER TABLE to cahnge only one CONSTRAINT to read like

ALTER TABLE `sometab` 
	DROP FOREIGN KEY `fk_inventory_store`  , 
	ADD CONSTRAINT `fk_inventory_store` 
	FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE ;

If I run two ALTER TABLE statements like

ALTER TABLE `sometab` DROP FOREIGN KEY `fk_inventory_store`; 	
ALTER TABLE `sometab` ADD CONSTRAINT `fk_inventory_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE ;

... there is no error.

Suggested fix:
I believe the syntax is valid as no syntax error is raised.

BTW: Can you advise another syntax that works *in a single ALTER TABLE statement*?

I'd say this is a documented limitation. Read http://dev.mysql.com/doc/refman/5.5/en/alter-table.html:

"You cannot add a foreign key and drop a foreign key in separate clauses of a single ALTER TABLE statement. You must use separate statements."

Why doesn't it return 1064 syntax error then?

Changing category to 'parser' accordingly. The parser should catch this as an invalid statement, and a 1064 syntax error should be returned, I think.

Let me add that I think a single ALTER TABLE is preferable for performance reasons (even with "online ALTER TABLE" as of 5.6).

Verified in 5.6.10:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.10    |
+-----------+
1 row in set (0.00 sec)

mysql> /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> CREATE TABLE `sometab` (
    ->   `inventory_id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   `film_id` SMALLINT(5) UNSIGNED NOT NULL,
    ->   `store_id` TINYINT(3) UNSIGNED NOT NULL,
    ->   `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (`inventory_id`),
    ->   KEY `idx_fk_film_id` (`film_id`),
    ->   KEY `idx_store_id_film_id` (`store_id`,`film_id`),
    ->   CONSTRAINT `fk_inventory_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`),
    ->   CONSTRAINT `fk_inventory_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`)
    -> ) ENGINE=INNODB AUTO_INCREMENT=4582 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> /* Alter table in target */
mysql> ALTER TABLE `sometab` 
    -> DROP FOREIGN KEY `fk_inventory_film`  , 
    -> ADD CONSTRAINT `fk_inventory_film` 
    -> FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON DELETE CASCADE ON UPDATE CASCADE , 
    -> DROP FOREIGN KEY `fk_inventory_store`  , 
    -> ADD CONSTRAINT `fk_inventory_store` 
    -> FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE ;
ERROR 1826 (HY000): Duplicate foreign key constraint name 'test/fk_inventory_film'
mysql> 
mysql> /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
Query OK, 0 rows affected (0.00 sec)

I agree that this limitation is documented and thus should ideally be caught at the SQL layer before attempting to actually ALTER the table. 

I will copy this over to the developers so that they can take a closer look.

Thank you for the great bug report, Peter!

Posted by developer:
 
Note that this limitation is still there in version 8.0.17-git.

However, I don't agree that we should add check prohibiting such statements to parser,
Instead, in my opinion, we should try to remove this limitation. There are no conceptual
reasons behind it and we allow similar ALTER TABLE which remove and add the same object
within the same statement for other types of objects like columns and keys.