Bug #26083 loosing referential constraints actions after ALTER TABLE
Submitted: 5 Feb 2007 13:08 Modified: 5 Feb 2007 13:25
Reporter: Ilmars Knipshis Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:MySQL server 5.1 OS:FreeBSD (FreeBSD 6.1)
Assigned to:

[5 Feb 2007 13:08] Ilmars Knipshis
Description:
I noticed that my InnoDB tables looses the actions ON DELETE ON UPDATE after each mysql server restart. So it is also after ALTER TABLE command. I create small SQL example and test with it different MySQL server versions. 4.X OK, 5.0 OK. The problem is only with new 5.1 versions. Below is my test example. I used test data base.
 

How to repeat:
use test;

CREATE TABLE tableB (
 id bigint(20) Not NULL AUTO_INCREMENT,
PRIMARY KEY(id))
      ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE tableA(
 second_id bigint(20) NOT NULL,
 id bigint(20) Not NULL AUTO_INCREMENT,
 PRIMARY KEY(id),
 UNIQUE KEY second_id (second_id),
        CONSTRAINT testconstraint FOREIGN KEY (second_id) REFERENCES tableB (id)
    ON DELETE CASCADE    ON UPDATE CASCADE) 
      ENGINE=InnoDB DEFAULT CHARSET=latin1;

## before any action the table constraints are OK:
show create table tableA;
>| tableA | CREATE TABLE `tableA` (
>  `second_id` bigint(20) NOT NULL,
>  `id` bigint(20) NOT NULL AUTO_INCREMENT,
>  PRIMARY KEY (`id`),
>  UNIQUE KEY `second_id` (`second_id`),
>  CONSTRAINT `testconstraint` FOREIGN KEY (`second_id`) REFERENCES `tableB` >(`id`) ON DELETE CASCADE ON UPDATE CASCADE
>) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

## now we do ALTER TABLE and check constraints
 ALTER TABLE tableA TYPE=InnoDB;
 show create table tableA;
| tableA | CREATE TABLE `tableA` (
  `second_id` bigint(20) NOT NULL,
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `second_id` (`second_id`),
  CONSTRAINT `testconstraint` FOREIGN KEY (`second_id`) REFERENCES `tableB` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

## There are no: "ON DELETE CASCADE ON UPDATE CASCADE" !!
[5 Feb 2007 13:25] Sveta Smirnova
Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Duplicates bug #24741