Subtitle: Makes an A$$ Out of You and Me

I ran into an issue last night with MySQL Workbench that frustrated me. I’m working with a table of medium-low complexity (about 10 Tables and 15 foreign keys) that I’ve inherited in joining a research team.  At this point I’ve reworked the whole thing to be a little more robust, but when I did so I ran into some problems on importing the results into a table.

On some of the tables I was getting an “Can’t create table <x> (errno: 150)”, which turns out to be a foreign key(FK) creation failure. I then realized that all of the tables that were failing were referencing the same core table, but with no visible issues. The primary key on the table was a composite of two  surrogate keys [INT(10) in this case]: one from the parent table and a local one.

I immediately thought that the FK definitions might have been incompatible, and so I combed through some of them for reasons why this would happen. I looked through InnoDB reference documentation in search of limitations in the implementation. Maybe there was a limitation on the number of keys referencing a single table, or on chaining “cascade”, “set null”, “restrict” behaviors, or on the maximum length for foreign key field names. Nothing.

I was up until 2 in the morning in the office last night with this problem with no success and eyelids with exponential density growth. When I got in today I quickly realized that it was just a simple conflict between ‘unsigned’ and normal int values in the foreign keys and relating tables.

My beef: All of the table declarations were made in MySQL workbench. Relationships and everything were formed using the exact same tools for all of it, and yet some of my columns were unsigned and some were not. Additionally, there was seemingly no way to change this value in the interface. Well, it turns out there is.

How to enable zerofill, unsigned, and other flags in MySQL Workbench:

• Go to (Menubar)->Tools->Options
• Open the ‘Diagram’ Tab
• Ensure “Show Table Column Flags” is checked

My biggest beef: A good programmer should never assume that a tool is doing what he wants it to do. If there’s no visible indication that numbers are being given unsigned classification, then that’s your job.  I wasted a few hours on this problem, but my jedi masters should be pleased that I’ve resolved this error in less than 24 hours. PEBKAC ftw.