Description:
I'm using the export "SQL ALTER script".
Everything's okay but one thing.
On one table, I have two foreign keys, and this table has changed (ON CASCADE stuff for a foreign key).
Here's the generation :
--=================================
ALTER TABLE `myschema`.`rss` DROP FOREIGN KEY `USER_RSS_LINK` ;
ALTER TABLE `myschema`.`rss`
ADD CONSTRAINT `USER_RSS_LINK`
FOREIGN KEY (`user_idFK` )
REFERENCES `myschema`.`users` (`user_id` )
ON DELETE CASCADE
, DROP INDEX `WEBSITE_RSS_LINK`
, DROP PRIMARY KEY
, ADD PRIMARY KEY (`rss_id`, `user_idFK`, `website_idFK`) ;
--=================================
Here's what I have done (it works) :
--=================================
ALTER TABLE `myschema`.`rss` DROP FOREIGN KEY `USER_RSS_LINK` ;
ALTER TABLE `myschema`.`rss` DROP FOREIGN KEY `WEBSITE_RSS_LINK` ;
ALTER TABLE `myschema`.`rss`
ADD CONSTRAINT `USER_RSS_LINK`
FOREIGN KEY (`user_idFK` )
REFERENCES `myschema`.`users` (`user_id` )
ON DELETE CASCADE
, DROP INDEX `WEBSITE_RSS_LINK`
, DROP PRIMARY KEY
, ADD PRIMARY KEY (`rss_id`, `user_idFK`, `website_idFK`) ;
ALTER TABLE `myschema`.`rss`
ADD CONSTRAINT `WEBSITE_RSS_LINK`
FOREIGN KEY (`website_idFK` )
REFERENCES `myschema`.`websites` (`website_id` );
--=================================
------------------------------------------------
------------------------------------------------
MySQL 5.1.30-community via TCP/IP on localhost.
MySQL Client Version 5.1.11
Mysql Query 1.2.12 to test the sql scripts generated by MySQL workbench.
How to repeat:
I had other "on cascade" stuff, and the alter script generation has worked on it, so I think that there's a bug when :
- there is a table with 2 foreign keys (with no "cascade" stuff on it)
- You generate the "sql CREATE script" : sql-create.sql
- you add to one of these foreign keys a "on delete cascade" property
- you generate the "sql ALTER script" with the "sql-create.sql" file
- create the database with the first sql script
- alter the database with the alter script
==> Error 1025
Suggested fix:
Drop all the foreign keys of the table if one of them has changed.
Then alter the table with every foreign key.
