Bug #45163 Behavior different then expected from manual - ALTER w/ FK

• Submitted: 28 May 2009 15:10
• Modified: 18 Feb 2014 15:53
• Reporter: Lig Isler-Turmelle
• Status: Closed
• Category: MySQL Server: Documentation
• Severity: S3 (Non-critical)
• Version: 5.1.30, 5.0.70
• OS: Any
• Assigned to: Daniel Price
• CPU Architecture: Any

[28 May 2009 15:10] Lig Isler-Turmelle

Description:
From the manual page http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html 

"You cannot add a foreign key and drop a foreign key in separate clauses of a single ALTER TABLE  statement. Separate statements are required."

When you attempt it though - it works.

Test done on a table with no data.

mysql> SELECT VERSION();
+--------------------------------+
| VERSION()                      |
+--------------------------------+
| 5.1.30-enterprise-gpl-advanced | 
+--------------------------------+
1 row in set (0.01 sec)

mysql> CREATE TABLE parent (id INT NOT NULL,
    ->                      PRIMARY KEY (id)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE child (id INT, parent_id INT,
    ->                     INDEX par_ind (parent_id),
    ->                     FOREIGN KEY (parent_id) REFERENCES parent(id)
    ->                       ON DELETE CASCADE ON UPDATE CASCADE
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.06 sec)

mysql> SHOW CREATE TABLE child;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                       |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| child | CREATE TABLE `child` (
  `id` int(11) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  KEY `par_ind` (`parent_id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE child DROP FOREIGN KEY `child_ibfk_1`, ADD FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE CASCADE;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE child;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                     |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| child | CREATE TABLE `child` (
  `id` int(11) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  KEY `par_ind` (`parent_id`),
  CONSTRAINT `child_ibfk_2` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

How to repeat:
create parent child tables with linkage between them.  then using one alter table, drop the old index and add a new one.

Suggested fix:
pick one behavior or the other and stick with it.  Either document the change in the manual or throw an error when you try it.

[17 Jan 2014 14:51] Marko Mäkelä

Posted by developer:
 
This seems to be a documentation issue.

The ALTER TABLE...ADD/DROP FOREIGN KEY operations were overhauled in
MySQL 5.6 by WL#5534 and WL#6251, which shipped with MySQL 5.6.8 or
maybe a couple releases earlier.

In some cases, ALTER TABLE...ALGORITHM=COPY could still issue
unintuitive error messages, but the default ALGORITHM=INPLACE should
work better. There are still some problems with DDL operations and
FOREIGN KEY in MySQL 5.6, because the two data dictionaries can get
out of sync: the InnoDB-internal one and the MySQL *.frm files.

I tried the test case, and the DROP FOREIGN KEY, ADD FOREIGN KEY works
with both ALGORITHM=INPLACE and ALGORITHM=COPY. This seems consistent
to me.

[18 Feb 2014 15:53] Daniel Price

The documentation has been updated to reflect that as of MySQL 5.6.6, adding and dropping a foreign key in the same ALTER TABLE statement is supported for ALTER TABLE ... ALGORITHM=INPLACE.

http://dev.mysql.com/doc/refman/5.6/en/alter-table.html
http://dev.mysql.com/doc/refman/5.7/en/alter-table.html

While adding and dropping a foreign key in the same ALTER TABLE statement for ALGORITHM=COPY may work in some cases, it could still be problematic, and is therefore unsupported.

Thank you for the bug report.