3

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

Improve this question
8
  • MySQL Workbench figures out the relationships when they exist, as FOREIGN KEY constraints.
    – ypercubeᵀᴹ
    Commented Jun 22, 2012 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.
    – ypercubeᵀᴹ
    Commented Jun 22, 2012 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?
    – ypercubeᵀᴹ
    Commented Jun 22, 2012 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.
    – Michael Durrant
    Commented Jun 22, 2012 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.
    – ypercubeᵀᴹ
    Commented Jun 22, 2012 at 14:34

2 Answers 2

Reset to default
7

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.

Improve this answer
4

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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