MySQL Bugs: #40467: Null Foreign Keys and Forward Engineer Alter Script Causes Problems

Bug #40467	Null Foreign Keys and Forward Engineer Alter Script Causes Problems
Submitted:	31 Oct 2008 23:14	Modified:	4 Sep 2009 13:02
Reporter:	William Ong	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.0, 5.1, 5.2	OS:	Windows
Assigned to:	Susanne Ebrecht	CPU Architecture:	Any

Description:
The Forward Engineer Alter repeats the script needed for a foreign key if it isn't declared not null.    

How to repeat:
Make some tables.  Have one table require a foreign key from another table. (In my case, it's a Restrict, Restrict) Make sure that the foreign key in that table has the "not null" check box unselected. 

Now do a Forward Engineer CREATE.  Now do a Forward Engineer ALTER on that create script you just created.  The program creates a script to drop all the foreign keys and recreate them again.

change isn't in the description to is

Thank you for the bug report. Are you using latest 5.0.27 released version?. If no please try it otherwise could you please provide a sample project file?. Thanks in advance.

I sent you the file as well as recreated the Alter problem.  The create problem pops up occasionally but I'm not sure how with the upgrade to 5.0.27.  5.0.26 caused it to happen more often.

Many thanks to write a bug report.

William, just for your information MySQL is using RESTRICT by default. Also SQL standard says that RESTRICT has to be default behaviour. So it is not necessary
here to write ON DELETE RESTRICT ... because the ON DELETE behaviour should be restrict anyway.

Ok, but you hit a bug here. Verified by the following test:

My test here:

1) Open WB
2) Open test schema: myschema.mwb (the schema that was in the package)
3) change table1_idtable1 to NULL (remove NOT NULL)
4) File->Export->Forward ... CREATE
4.1) name it my.sql or whatever
5) File->Export->Forward ... ALTER
sync with my.sql or how ever you named the script before

Result:

ALTER TABLE `mydb`.`table1_has_table2` DROP FOREIGN KEY `fk_table1_has_table2_table1` ;

ALTER TABLE `mydb`.`table1_has_table2` 
  ADD CONSTRAINT `fk_table1_has_table2_table1`
  FOREIGN KEY (`table1_idtable1` )
  REFERENCES `mydb`.`table1` (`idtable1` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

Why? That it drop the foreign key isn't necessary. I didn't made changes at all.

This only happens when a foreign key column is allowed to be NULL.

I am pretty sure this is fixed in 5.1 and 5.2 but will check this again.

This is neither fixed in 5.1 nor in 5.2

Re-rated this bug.

This is not repeatable anymore.

Seems this bug got fixed by accident.

Please try actual version Workbench 5.1.18 here.