MySQL Workbench Plugin: Auto-Create Foreign Keys

To automatically create Foreign Key relationships is a typical use case for developers working with the MyISAM storage engine. This has been a popular topic on the Blog and forums so we’re revisiting it here – with a new and improved plugin written in Python.

While the InnoDB storage engine supports foreign keys, MyISAM doesn’t, but developers often pick it for various reasons and leave the application to handle relationships itself. MySQL Workbench can be used to reverse engineer a database to a model to better visualize or maintain it. But since MyISAM doesn’t have foreign keys, databases that use it will be lacking a very important part of it’s structure in the diagrams. You can link the columns manually, using the relationship tool to link columns, but you can also automate that. Databases are usually created so that columns that represent relationships have names that follow some kind of convention or pattern. For example, a city table that is related to a country table, may have a column called country_id, used as the foreign key. The pattern there would be something like <table_name>_id. We can find all such pairs of columns between potential foreign keys and primary keys and create a foreign key for the tables.

There are two core routines needed by this implementation:

  • the first is to find candidate columns. That is, columns that could be foreign keys that reference primary keys of other tables, according to some pattern.
  • the second is the code to actually create the foreign keys from the possible columns found previously.

Look for Candidates

The following is the code to find candidate columns:

def get_fk_candidate_list(schema, fk_name_format, match_types=False):
    candidate_list = []
    possible_fks = {}
    # create the list of possible foreign keys out of the list of tables
    for table in schema.tables:
        if table.primaryKey and len(table.primaryKey.columns) == 1: # composite FKs not supported
            format_args = {'table':table.name, 'pk':table.primaryKey.columns[0].name}
            fkname = fk_name_format % format_args
            possible_fks[fkname] = table

    # go through all tables in schema again, this time to find columns that seem to be a fk
    for table in schema.tables:
        for column in table.columns:
            if possible_fks.has_key(column.name):
                ref_table = possible_fks[column.name]
                ref_column = ref_table.primaryKey.columns[0].referencedColumn
                if ref_column == column:
                    continue
                if match_types and ref_column.formattedType != column.formattedType:
                    continue

                candidate_list.append((table, column, ref_table, ref_column))
    return candidate_list

First, it will go through the list of all tables in the given schema and create a dictionary of possible foreign key column names, according to a format string provided by the user. The format string has the %(table)s and %(pk)s variables replaced with the table name and primary key column name.

With the dictionary of possible foreign key names at hand, it then goes through all columns of all tables looking for any column name that is in the dictionary. If a match is found, a tuple of table, column, referenced table and referenced column names are added to a list of candidates. If the match_types flag is True, it will also check if the column types match and discard anything that doesn’t.

Create Foreign Keys

With the list of candidate columns, we can create a foreign key object from the table column to its referenced column.

for table, column, ref_table, ref_column in candidates:
    fk = table.createForeignKey(ref_column.name+"_fk")
    fk.referencedTable = ref_table
    fk.columns.append(column)
    fk.referencedColumns.append(ref_column)

According to the db_Table documentation, table objects have a convenient createForeignKey method, which takes the foreign key name as an argument, and returns a new db_ForeignKey object added to the table. The foreign key is empty, so we set its referencedTable field and add the column/referenced column pair to the columns and referencedColumns lists, respectively.

Adding a GUI

Now, for a fancier version, we will create a dialog that takes the naming pattern from the user, shows the list of candidates and creates the foreign keys when a button is clicked:

This GUI version uses the internal mforms toolkit. It provides a native interface in any of the supported platforms. See the documentation for it here.

Here is the part of the code that creates the UI. You can use it as a template for your own plugin dialogs. Go to the end of the post for the full plugin code.

import mforms

class RelationshipCreator(mforms.Form):
  def __init__(self):
    mforms.Form.__init__(self, None, mforms.FormNone)

    self.set_title("Create Relationships for Tables")

    box = mforms.newBox(False)
    self.set_content(box)
    box.set_padding(12)
    box.set_spacing(12)

    label = mforms.newLabel(
"""This will automatically create foreign keys for tables that match
a certain column naming pattern, allowing you to visualize relationships
between MyISAM tables.

To use, fill the Column Pattern field with the naming convention used for
columns that are meant to be used as foreign keys. The %(table)s and %(pk)s
variable names will be substituted with the referenced table values.""")
    box.add(label, False, True)

    hbox = mforms.newBox(True)
    hbox.set_spacing(12)
    box.add(hbox, False, True)

    label = mforms.newLabel("Column Pattern:")
    hbox.add(label, False, True)
    self.pattern = mforms.newTextEntry()
    hbox.add(self.pattern, True, True)
    self.matchType = mforms.newCheckBox()
    self.matchType.set_text("Match column types")
    hbox.add(self.matchType, False, True)
    self.matchType.set_active(True)
    search = mforms.newButton()
    search.set_text("Preview Matches")
    search.add_clicked_callback(self.findMatches)
    hbox.add(search, False, True)

    self.pattern.set_value("%(table)s_id")

    self.candidateTree = mforms.newTreeView(mforms.TreeShowHeader)
    self.candidateTree.add_column(mforms.StringColumnType, "From Table", 100, False)
    self.candidateTree.add_column(mforms.StringColumnType, "Column", 100, False)
    self.candidateTree.add_column(mforms.StringColumnType, "Type", 100, False)
    self.candidateTree.add_column(mforms.StringColumnType, "To Table", 100, False)
    self.candidateTree.add_column(mforms.StringColumnType, "Column", 100, False)
    self.candidateTree.add_column(mforms.StringColumnType, "Type", 100, False)
    self.candidateTree.end_columns()
    box.add(self.candidateTree, True, True)

    hbox = mforms.newBox(True)
    hbox.set_spacing(12)
    self.matchCount = mforms.newLabel("")
    hbox.add(self.matchCount, False, True)
    self.cancelButton = mforms.newButton()
    self.cancelButton.set_text("Cancel")
    hbox.add_end(self.cancelButton, False, True)
    self.okButton = mforms.newButton()
    self.okButton.set_text("Create FKs")
    hbox.add_end(self.okButton, False, True)
    self.okButton.add_clicked_callback(self.createFKs)
    box.add(hbox, False, True)

    self.set_size(700, 600)

The dialog is implemented as a subclass of the mforms.Form class. That is the class for creating a window.

Line 5 calls the __init__ method of mforms.Form. Nothing fancy here, as we just want a plain normal window. Line 7 which follows, sets the title of the window.

Line 9, mforms.newBox(False) is used to create a box layouter that is “not horizontal” (ie, vertical). This is used to layout controls that are added to it from top to bottom, in a single column. Line 10 makes the window display this box as its first control. Anything else you want displayed in the window must be added to this box, either as a direct child or nested in other layouters children of this one.
Lines 11 and 12 set a padding around the box and a spacing between each item inside it, so we have a not so cluttered appearance in our dialog.

Line 14 creates a text label control with some rather lengthy description text, which is then added to the box we created above. The 2nd argument to the add method tells the layouter to not expand the added control. That is, the label will allocate as much space as it needs to show all its contents. If it was set to True, it would instead use all the space left in its container. The 3rd argument tells the layouter to fill the space allocated for it with the control. Since expand is False in this case, this won’t make much difference, but if it was True, it would toggle whether the label should have the same size as the space allocated for it or not. Note the difference between allocated space and actually used space.

Line 24 creates another box, this time a horizontal one, which is then added to the previously created vertical box. Anything added to this box will be laid out as a single row inside the first box. Anything added to the first box after this point, will be added below the row created by this box.

Lines 28 to 39 creates a label, a text field, a checkbox and a button, which are all laid in a row, using the horizontal box above. For the search button, we’re setting a callback which will be called when the user clicks it. The callback is just a method in the same class, called findMatches. It doesn’t take any argument.

A tree with 6 columns is then created from lines 43 to 50. The tree (which is just a plain list of rows) is set up by adding as many columns are desired, with their types, captions, default width and a flag telling whether the column is editable or not. After the columns are added, the end_columns() method must be called.

Finally, another row is added, starting from line 53. This row contains a Cancel and OK (Create FKs) buttons. Instead of add(), the add_end()method from Box is used, so that the buttons are laid out from right to left, instead of starting from the left to the right.

At last, the default size of the window is set.

This image shows rectangles around each of the boxes used to lay out the dialog.

The Whole Thing

To make this a plugin, there’s a few more bureaucratic lines of code that must be added. This code is described in our previous plugin tutorials and in the documentation.

The full plugin code contains the complete implementation. To install it, save it as relationship_create_grt.py and Install it from the Scripting -> Install Plugin/Module… menu item. After restarting Workbench, a new item called Create Relationships from Columns will appear in the Plugins -> Catalog menu. Note: the plugin requires MySQL Workbench 5.2.29

 

UPDATE: Fixed link to full plugin code

23 thoughts on “MySQL Workbench Plugin: Auto-Create Foreign Keys”

  1. Marvellous!

    I am dl’ing now. I’m so excited! hehehe. you sure know the way to a geek’s heart. (Serious comments later after I test)

  2. Sounds promissing! Esspecially now we are at the startingline of migrating our myISAM tables to InnoDB. However after installing the module/script it does not show up under Plugins>Catalog. I’m using Workbench 5.2.29

  3. I followed the instructions and after installing and restarting the module/script it does not show up under Plugins>Catalog. I’m using Workbench 5.2.29.

    I even copied the script file over to the path: C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE\modules but that didn’t help.

    Note: I’m running Windows 7 64bit if that makes any difference. I can’t seem to get this plugin to install. How do I use it?

    I would appreciate someone getting back to me. Thanks!

  4. It seems the plugin doesn’t like when tables (and the resulting fk columnnames) contain _ (0x5f) for example tablename education_something and fk education_something_id

    //L

  5. How did you guys install the plugin? Does the plugin show up in the Scripting -> Plugin Manager… window?

    Leif: I didn’t see any problem when tried the plugin in a model with underscores/_ galore, what error did you get?

  6. Hi Alfredo,

    I followed the instructions and installed the plugin through “Scripting”>>”Install Plugin/Module”.
    And no, it does not show up under Scripting -> Plugin Manager… window

    Thanks your help is appreciated

  7. We installed plugin but we cannot make it works.
    Our naming convention is IDTableName ie. IDEmployee where Employee is the table name. We use template %(pk)s%(table)s for searching and got 0 mathces found.

  8. I am using workbench 5.2.34CE and I have the following error:

    Error: Time: 12:10:42 Message: Error executing plugin wb.catalog.util.autoCreateRelationships: error calling AutoRelationshipUtils.autoCreateRelationships: see output for details Details: n/a

    Traceback (most recent call last):

    File “C:\Users\Prof\AppData\Roaming\MySQL\Workbench\modules\relationship_create_grt.py”, line 142, in autoCreateRelationships

    form = RelationshipCreator(catalog)

    File “C:\Users\Prof\AppData\Roaming\MySQL\Workbench\modules\relationship_create_grt.py”, line 81, in __init__

    self.candidateTree = mforms.newTreeView(mforms.TreeShowHeader)

    AttributeError: ‘module’ object has no attribute ‘TreeShowHeader’

  9. You can fix that by editing relationship_create_grt.py and replace mforms.TreeShowHeader with 0, in the parts the error point.

  10. I had trouble getting the plugin to work with primary keys. In the end I had to change a line in get_fk_candidate_list from

    format_args = {‘table’:table.name, ‘pk’:table.primaryKey.columns[0].name}

    to

    format_args = {‘table’:table.name, ‘pk’:table.primaryKey.columns[0].referencedColumn.name}

    Thanks for a great utility – once I made that change it did just what I needed.

  11. Thanks a lot for your plug-in, exactly what I was looking for ! And moreover it showed me plug-ins can be made using Python (I’m a newbie with mysql workbench).

  12. I have tables with ends ‘s’ (userS, groupS), but the reference field name not contains ends ‘s’. for Example:
    `groups.user_id` reference to `users.id`.
    You might not consider the end ‘s’ in table name of the search keys?
    Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.