Bug #58379 Insert with Recursave dependency in Foreign Keys throws error
Submitted: 22 Nov 2010 12:37 Modified: 23 Jul 2014 20:46
Reporter: matt lago Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench: Modeling Severity:S4 (Feature request)
Version:5.2.31 OS:Any
Assigned to: CPU Architecture:Any

[22 Nov 2010 12:37] matt lago
Description:
When you have 2 tables with foreign keys that require each other the insert in the ERR diagram doesn't work correctly on import after exporting from workbench. It throws the error that its not a valid insert (which its not because the other data is missing)

How to repeat:
Make a table A
Make a table B

add a FK from A to B
add a FK from B to A

Add via the insert tab data to both tables that would be valid data

Export the data and inserts

Try and run that sql on a database and you will be unable to insert the data

Suggested fix:
An ignore foreign key's checkbox for inserts on export?

Exporter logic that will see this situation and verify the data is valid and if so allow it to be inserted past the foreign key restriction with SET FOREIGN_KEY_CHECKS = 0; ... SET FOREIGN_KEY_CHECKS = 1;.
[22 Nov 2010 12:38] matt lago
Demo wkb file that wont export/import correctly via sql

Attachment: database.mwb (application/octet-stream, text), 33.01 KiB.

[27 Dec 2010 13:56] Valeriy Kravchuk
Indeed, in 5.2.31 INSERTs are generated after restoring original values of variables that control SQL mode and constraints checking:

... DDL ...

... GRANT ...

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
 
... INSERTS ...

We need a way to fix/change this.
[23 Jul 2014 20:46] Alfredo Kojima
Posted by developer:
 
The Disable FK Checks for Inserts was added some time ago (6.0 or earlier), so I'm closing this bug.