MySQL Bugs: #38710: Foreign keys dropped after attempting to change cascade.

Bug #38710	Foreign keys dropped after attempting to change cascade.
Submitted:	11 Aug 2008 8:29	Modified:	13 Oct 2008 8:11
Reporter:	David Sanders (Candidate Quality Contributor)	Email Updates:
Status:	To be fixed later
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	1.2.12	OS:	Microsoft Windows (XP SP3)
Assigned to:	Mike Lischke	Target Version:
Triage:	D3 (Medium)

Description:
After attempting to change the "On Delete" to "Set Null" for a "Not Null" foreign key,
you'll correctly receive an error.  After clicking ok on the error dialog and then
clicking "Discard Changes", the foreign key is actually dropped (erroneously).

Before applying the changes in "How to repeat" below, the discard changes does actually
work correctly.  It's only after the apply changes that discard deletes the FK.

How to repeat:
1. Create a table 'foo' with a single primary key

2. Create a table 'bar' with a primary key and a foreign key referencing the primary key
of foo that is 'Not Null' and has both 'Restrict' for 'On Delete' and 'On Cascade' (and
then apply changes and close the create table dialog)

3. Reopen the Edit Table window for the table 'bar'

4. Change 'On Delete' to 'Set Null'

5. Click 'Apply Changes'

6. Click 'Execute'

-> An error should display: "Error while executing query......."

7. Click 'OK'

8. Click 'Discard Changes'

-> Confirmation dialog appears

9. Click 'Yes'

-> The foreign key disappears and any attempt to reopen the 'Edit Table' window does not
show the foreign key, so it appears to have actually been dropped.

This may be related to Bug #28885

Thank you for the bug report.

I'm afraid currently we can't do anything about this. The problem here is that we'd either
need something like a syntax check by the server before applying any change (so we would
not drop anything if there is an error) or have transactional DDL or know the reverse
operation to "undo" the drop, which is necessary before creating the new FK.
Unfortunately, none of them are available to QB so when you decide to execute the SQL you
get shown in the popup when you click "Apply Changes" then two commands are actually
executed: a drop for the old key and the alter command for the new key. Hence if the
second command fails the first one is still already done and the key is gone. So check
carefully what gets executed. QB is not a tool to limit a user but can do some serious
damage if used wrongly.

Since the QB features will be included into MySQL Workbench at a later point in time and
we have much better parsing capabilities there, we can then at least "undo" the drop
operation.