I have a problem with MySQL Workbench and primary/foreign keys.

I have some tables with PKs involved in relationship with other tables. If I modify the type of the PK, the type of the FK doesn't automatically update to reflect the change.

Is there any solution? Do I have to manually modify all the relations?

  • I don't think that has anything to do with MySQL Workbench. It's just the way MySQL works. You need to change the referenced columns manually – a_horse_with_no_name Aug 5 '12 at 11:47
  • Thank you for your answer, but are you sure about this or it's your opinion? Is there any documentation mentioning this? I'm still having hope to don't this manually. The e-r is very big!!! Do you have any suggest on other software, so i can export from MySQL workbench and import in one other? Thanks a lot, again :) – Francesco Aug 5 '12 at 12:10
  • It would be a nice feature but I don't think it is on Workbench. You could run a (recursive) query/script using the INFORMATION_SCHEMA to find all columns in all tables that have a direct or indirect reference to the one you change. And then dynamically build the ALTER TABLE statements to reflect the change. – ypercubeᵀᴹ Aug 5 '12 at 12:56
  • The problem is that actually i'm in the design step, how can i run query/script in this step? If i try to export and execute the sql statement in phpMyAdmin i'll get some error due to this problem. Before MySql Workbench i was using a software called DDS-Pro, but now dds is old and no updates followed. In this software the problem mentioned in the post doesn't exist, there is already implemented. – Francesco Aug 5 '12 at 14:42

Actually cascade updates for data types are not supported by Workbench.

One workaround, is to define User Data Types, use those in your tables, and if necessary, update the User Data Type. That will generate the correct DDL.

I usually create User Data Types for PrimaryKeys, Name, Description, Money, etc.

In your case will not work, as you should do that before creating the tables. Now, if you must replace it in all your tables, do it for a User Type.

Other workaround for you will be to generate the database as is, go with the sugestion above, and then re-import the model to continue design.

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.