Simple entity relationship and foreign key question
Initially I was just looking for some open source software for creating entity relationship diagrams to help complete the design and implementation of a database I'm working on. When I read about Workbench I knew it would be ideal, saving me from creating a design from scratch by synchronising it with the database I had created so far.
But the problem is I'm having some trouble with creating the relationships. I don't know if it's just me, but I seem to be in a constant battle with Workbench, maybe I don't understand the workbench way of doing things, and I need to change the way I approach this I don't know. Hopefully someone can help.
I started by synchronising the model with the database and it loaded the tables I had already created. At this point there are no foreign keys defined in the tables themselves, well the columns are there but they are not defined as foreign keys. So I set about creating the relationships and this is where the battle began. I've managed to wrestle 3 of the tables together with 2 relationships, but the final one is having none of it.
One of the tables that were imported from the database is "games" which has a "game_id" primary key and a list of other fields including "map_id" which will be a foreign key. I added a new table "maps" with a primary key of "map_id" and one other field called "name". All I wanted to do is create a non-identifying 1-many relationship from the "map" table to the "games" table. I.e. each game record only has one map, but each map can be used in many games.
Using the tools method to add the relationship, I click on the "place a new 1:n non-identifying relationship" icon, click once on the "games" table (the many side) and then once on the "map" table. This creates the 1:n relationship the right way round and links the "maps.map_id" primary key to a new "games.maps_map_id" foreign key. So I opened the "Foreign Keys" tab on the "game" table, changed the foreign key name to map_id, but although I could untick the "maps_map_id" column it wouldn't let me tick the "map_id" column that was already in the table before I created the relationship. So I deleted that relationship.
Next I tried adding the relationship from the table editor, but first I removed the "games.maps_map_id" column. Then went back to the "Foreign Keys" tab of the "games" table, entered map_id under the foreign key name column, selected the maps table from the dropdown under the referenced table column, but again it wouldn't let me tick the "map_id" under column. Strangely it would only let me tick columns that were varchars, despite the map_id column in both tables being defined as an integer.
So I guess, after all that waffle, my question is really, how do I create a relationship between two tables manually, using an existing column in the "games" table that I choose, as the foreign key?