MySQL Bugs: #38064: Foreign key referenced columns disappeared

Bug #38064	Foreign key referenced columns disappeared
Submitted:	12 Jul 2008 4:40	Modified:	10 Sep 2008 9:45
Reporter:	Ken Zo	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.0.23 OSS rev 3198	OS:	Windows (XP SP2)
Assigned to:		CPU Architecture:	Any
Tags:	Foreign key editor, relationships, vanished

Description:
I create a model with three simple tables, with foreign key constraints between them.

I use Forward Engineer SQL CREATE, and it creates a broken script including the following in the CREATE TABLE statement:

  CONSTRAINT `fk_user_class_to_user_user`
    FOREIGN KEY (`user_id` )
    REFERENCES `another export bug`.`user` ()
    ON DELETE CASCADE
    ON UPDATE CASCADE)

Note the empty parentheses, which should read:
    REFERENCES `another export bug`.`user` (`user_id`)

Even though this relationship displays correctly in the GUI, the export excludes the column name and then MySQL cannot execute the script:
"ERROR 1064 (42000) at line 30: You have an error in your SQL syntax"...

In a larger model, this happened with a few tables.

How to repeat:
Open the attached MWB file.  Forward Engineer SQL CREATE.  Note that script (also attached) has the above error and will not execute in MySQL.

This MWB file will generate a broken SQL Create script

Attachment: sql create constraint bug example.mwb (application/octet-stream, text), 5.47 KiB.

This is the broken SQL Create script that the MWB file creates

Attachment: sql create constraint bug example.sql (application/octet-stream, text), 1.87 KiB.

It turns out this problem started before the SQL Create.  I now see that the "referenced column" setting has gone blank for all the foreign keys (in foreign key panel) in my model that referred to one of my (still existing) tables.  I don't know when this happened, or why it spontaneously happened across my whole model.  But, Forward Engineer doesn't appear to be what's at fault, and I cannot tell you how to reproduce the problem since I don't know when it started.

To be clear, these referenced columns used to be listed, which I am sure of since the Forward Engineer SQL Create commands used to work.  So, this does still seem like a bug is present (that my foreign key referenced columns disappeared all at once), but I can't tell you how to reproduce it.

Thank you for the report.

Verified as described.

Ken, your model actually contains two foreign keys. One is correct, the other lacks the referenced column. We need to find out how that could go blank, otherwise we are unable to fix the problem.

Mike,

to repeat this follow instructiions from bug #38614:

1. Create new WB project
2. Create new ERR diagram
3. Place a table, add 2 INT columns to the table.
4. Create foreign key. Don't check reference table, but check 1 of columns. You will see this foreign key doesn't refer to any column.
5. Place 2 new tables with INT column each.
6. Create second foreign key (valid)
7. Edit foreign key created in step 4 so it is referencing to a table, but don't touch referenced column.
8. Export.

Notice you can not add referenced column to the foreign key created in step 4.

Bug #38614 was marked as duplicate of this one.

Hello Ken Zo,

yould you verify the issue using the latest version to see if it still happens - and if it does, maybe you can track down at which point the set target columns are cleared/lost.

thx
johannes

The steps to reproduce were provided by Sveta Smirnova in an earlier comment.  Here they are again:

to repeat this follow instructiions from bug #38614:

1. Create new WB project
2. Create new ERR diagram
3. Place a table, add 2 INT columns to the table.
4. Create foreign key. Don't check reference table, but check 1 of
columns. You will see this foreign key doesn't refer to any column.
5. Place 2 new tables with INT column each.
6. Create second foreign key (valid)
7. Edit foreign key created in step 4 so it is referencing to a table,
but don't touch referenced column.
8. Export.

In version 5.1.24 is possible to edit invalid foreign key, so I change status of the report to "Can't repeat".

Regarding to invalid SQL MySQL Workbench has validation feature which should be used to prevent such situations.