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" !!
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" !!