I thought that previously when I reverse engineered databases, mySql Workbench figured out the relationships from the id names and connected the tables leading to an awesome ERD, even when the foreign key constraint clauses don't exist and they are just used through the fields (xxxx_id) have the foreign key values.

However currently, when I pick various databases (I create them in rails) and try to import them I end up with this nasty looking smodge:

enter image description here

Instead of:

enter image description here

  • MySQL Workbench figures out the relationships when they exist, as FOREIGN KEY constraints. Jun 22 '12 at 13:49
  • When no relationships exist, it doesn't guess anything. Note that even a human would only guess that survey_sections.survey_header_id is an FK to survey_headers (id). And he could be wrong. Jun 22 '12 at 14:05
  • Some people add an _id to an FK column. Some use id, others use tablename_id, others use tablenameID, etc... as a PK name. The list is endless. And what about compound PK and FKs? What if the constraint should be towards a UNIQUE and not towards a PRIMARY key? How should WOrkbench (or anyone) guess? Jun 22 '12 at 14:07
  • Sure, it seems like a few dialog boxes or settings could address the most common scenarios of othertableID othertable_ID, etc. and no compound keys used for joins. The possibilities are endless sure, but the most common ones are out there. Jun 22 '12 at 14:13
  • 1
    What could be done is a script to create those FKs (based on these specs) and then let Workbench reverse engineer. Jun 22 '12 at 14:34

MySQL Workbench does not have the capability to "infer" the relationships from table column names, or MyISAM tables. It requires that the constraints be defined in the database or SQL script being reverse engineered.


For any rails coder who finds this, you can use the immigrant gem. This generates a migration that builds all missing foreign key constraints. It uses foreigner, which provides helper methods for foreign key constraints. Then workbench should be able to build the relationships.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.