MySQL Bugs: #27009: Foreign key 'on update' and 'on delete' clauses dropped on alter table add colum

Bug #27009	Foreign key 'on update' and 'on delete' clauses dropped on alter table add colum
Submitted:	9 Mar 2007 19:00	Modified:	13 May 2007 13:21
Reporter:	Ryan Nevell	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	5.1.15-beta-community-nt	OS:	Windows (Windows XP, SP2, 32-bit)
Assigned to:		CPU Architecture:	Any

Description:
An existing InnoDB table with foreign keys having 'on update' and/or 'on delete' clauses (other than restrict), loses these clauses when altering the table. These alters add/drop a field that is unrelated to the reference. The foreign key remains, becomes on update/delete restrict.

How to repeat:
DROP TABLE IF EXISTS `r1`;
CREATE TABLE `r1` (
  `id1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `r2`;
CREATE TABLE `r2` (
  `id2` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
  `id1` int(11) NOT NULL,
  `id2` int(11) NOT NULL,
  `data` text,
  `dummy` text,
  FOREIGN KEY (`id1`) REFERENCES `r1` (`id1`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`id2`) REFERENCES `r2` (`id2`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Everything looks good now...
mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id1` int(11) NOT NULL,
  `id2` int(11) NOT NULL,
  `data` text,
  `dummy` text,
  KEY `id1` (`id1`),
  KEY `id2` (`id2`),
  CONSTRAINT `t_ibfk_1` FOREIGN KEY (`id1`) REFERENCES `r1` (`id1`) ON DELETE CA
SCADE ON UPDATE CASCADE,
  CONSTRAINT `t_ibfk_2` FOREIGN KEY (`id2`) REFERENCES `r2` (`id2`) ON DELETE CA
SCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

-- Alter the table in some way either
ALTER TABLE `t` DROP COLUMN `dummy`;
-- or
ALTER TABLE `t` ADD COLUMN `test` text;

-- But now our 'on update' and 'on delete' clauses are gone :(
mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id1` int(11) NOT NULL,
  `id2` int(11) NOT NULL,
  `data` text,
  `dummy` text,
  `test` text,
  KEY `id1` (`id1`),
  KEY `id2` (`id2`),
  CONSTRAINT `t_ibfk_1` FOREIGN KEY (`id1`) REFERENCES `r1` (`id1`),
  CONSTRAINT `t_ibfk_2` FOREIGN KEY (`id2`) REFERENCES `r2` (`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

Suggested fix:
Make sure these clauses are not lost on ALTER, or document that this is the expected behavior.

This bug is probably more critical than originally thought:
I thought an acceptable work-around would be
 1) Lock all the parent tables that this table's foreign keys reference
 2) Alter this table (which causes the foreign keys' clauses to be dropped)
 3) Alter this table adding foreign keys back
 4) Unlock parent tables

However, in step 3, "ALTER TABLE ... ADD FOREIGN KEY" causes these "ON UPDATE/ON DELETE" clauses of the other foreign keys to be dropped. Thus you cannot have 2 or more foreign keys with on update/delete clauses!

So, is there any way to get a table with 2 or more foreign keys with on update/on delete clauses except for at the time of the table's initial creation?
If not, I don't think there is a work-around for this bug.

Thank you for a problem report. Please, try to repeat with a newer version, 5.1.17, just released officially. I am noit able to repeat with latest 5.1.18-BK on Linux, hence the request:

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

mysql> DROP TABLE IF EXISTS `r1`;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE TABLE `r1` (
    ->   `id1` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id1`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE IF EXISTS `r2`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `r2` (
    ->   `id2` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id2`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Query OK, 0 rows affected (0.01 sec)

mysql> DROP TABLE IF EXISTS `t`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `t` (
    ->   `id1` int(11) NOT NULL,
    ->   `id2` int(11) NOT NULL,
    ->   `data` text,
    ->   `dummy` text,
    ->   FOREIGN KEY (`id1`) REFERENCES `r1` (`id1`) ON DELETE CASCADE ON UPDATE
    -> CASCADE,
    ->   FOREIGN KEY (`id2`) REFERENCES `r2` (`id2`) ON DELETE CASCADE ON UPDATE
    -> CASCADE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.02 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id1` int(11) NOT NULL,
  `id2` int(11) NOT NULL,
  `data` text,
  `dummy` text,
  KEY `id1` (`id1`),
  KEY `id2` (`id2`),
  CONSTRAINT `t_ibfk_1` FOREIGN KEY (`id1`) REFERENCES `r1` (`id1`) ON DELETE CA
SCADE ON UPDATE CASCADE,
  CONSTRAINT `t_ibfk_2` FOREIGN KEY (`id2`) REFERENCES `r2` (`id2`) ON DELETE CA
SCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> ALTER TABLE `t` DROP COLUMN `dummy`;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id1` int(11) NOT NULL,
  `id2` int(11) NOT NULL,
  `data` text,
  KEY `id1` (`id1`),
  KEY `id2` (`id2`),
  CONSTRAINT `t_ibfk_1` FOREIGN KEY (`id1`) REFERENCES `r1` (`id1`) ON DELETE CA
SCADE ON UPDATE CASCADE,
  CONSTRAINT `t_ibfk_2` FOREIGN KEY (`id2`) REFERENCES `r2` (`id2`) ON DELETE CA
SCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> ALTER TABLE `t` ADD COLUMN `test` text;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id1` int(11) NOT NULL,
  `id2` int(11) NOT NULL,
  `data` text,
  `test` text,
  KEY `id1` (`id1`),
  KEY `id2` (`id2`),
  CONSTRAINT `t_ibfk_1` FOREIGN KEY (`id1`) REFERENCES `r1` (`id1`) ON DELETE CA
SCADE ON UPDATE CASCADE,
  CONSTRAINT `t_ibfk_2` FOREIGN KEY (`id2`) REFERENCES `r2` (`id2`) ON DELETE CA
SCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".