Relations between tables MySQL dB

[Harry_38]

I have got my backend dB 9 tables (in MySQL) for my Access application on an external server. I need to create relations between some of the tables.
For this pupose I dowloaded MySQL workbench; I created an EER diagram but am unable to create the desired relation. I picked up somewhere that the database has to of type INNODB; no idea what mine is.
Can anybody help pls.

 

[Banana]

In Workbench, you'd select "One-Many Identifying Relationship" to create a relationship with referential integrity (to borrow Access' parlance) between two tables. Yes, you'd need it to use InnoDb database engine. MySQL is different in that you get to choose between different database engine. Right now, InnoDb is the only one that supports referential integrity (the more general term is "foreign key constraint", BTW). You specify this by setting the Engine = InnoDb instead of default MyISAM.

 

[Harry_38]

Thanks Banana; I had the dB engine changed to INNODB. In Workbench thing look very easy te create the relations; I point to 1:n and cursur changes to 'special' but I can't do anything with it. I have attached a prntscrn (workbench.zip). Please help, getting desperate.

 

[Banana]

Presuming you have two table with both primary key and foreign key defined:

Click the bottommost icon on the left toolbar to select "Place relationship upon existing columns" (or you can press key "6" to select the same tool)
Select the Foreign Key column first
Select the Primary Key on the other table second.


Alternative method:

Double-click the table with foreign key.
On the bottom portion, click Foreign Key.
On left hand pane, give the name of the constraint & select the table. On righthand pane select the foreign key column.


See if this helps.

 

[Harry_38]

Thanks Banana; the alternative method worked best.
I found out that creating the relations in mysql is easy as well.
Relations all work now, next job is to improve the speed of th eslow reports.