CODEWITHSUNDEEP
mysqlmysql-workbenchworkbench
user765368
user765368

Reputation: 20356

manually choose foreign key column mysql workbench

Let's say I have a bunch of MyISAM tables in a MySQL database. I know that if my tables are InnoDB MySQL Workbench will pick up the relationships between the tables automatically. But if my tables are MyISAM, is there a way that I can select which column to use as a foreign key in MySQL Workbench without MySQL Workbench adding a column in my table schema? For example, let's say I have these columns in a table called users:

id              PRIMARY KEY
username        VARCHAR (255)
password        VARCHAR (255)
email           VARCHAR (255)
user_type_id    INT

As you can see above, user_type_id would be a foreign key coming from a table called user_types. If I add a many-to-one relationship between my tables users and user_types in MySQL Workbench, a column user_types_id will be automatically added to the schema of my table users (because that's what MySQL Workbench considers as a foreign key usually). I don't want that to happen, I want to be able to tell MySQL Workbench to use the column user_type_id as my foreign key. Any way I can do that?

Thank you

NOTE: There is a Foreign Keys tab when I double click on a table on the model view in MySQL Workbench, but when I do so, I get the following text:

Note: foreign keys can only be defined for certain storage engines (like InnoDB). The server accepts foreign key definitions for other storage engines but silently ignores them. Switch your table engine to one that supports foreign keys to allow adjustments here.

Upvotes: 1

Views: 6864

Answers (2)

Mike Lischke
Mike Lischke

Reputation: 53502

You could temporarily switch to InnoDB, define the relationship and switch back to MyISAM. The relationships will remain. BUT, they are of no real use except to document your intention.

If that's all you want then go ahead.

Btw: the FK tab page is only unavailable on Windows (for engines that don't support FKs), as we have seen many complaints from users that defined FKs for MyISAM, just to see no effect in their target DB. On Linux + OS X you can work on FKs regardless of the selected storage engine.

Upvotes: 2

LNendza
LNendza

Reputation: 1410

If I'm understanding your question correctly, I believe you can do this from the Foreign Keys tab on the table itself. If you double click a table from the model overview, on the bottom tabs there is a Foreign Key tab. You can manually add in foreign keys however you choose from that dialog. Does that make sense?

Upvotes: 0

Related Questions