To create a reference between tables:
1. Click the Reference (
) item on the Main tab of Ribbon. Your mouse cursor will change its appearance.
2. Click on the table (referencing table or child table) that will have a foreign key.
3. Then click on the second table (referenced table or parent table) whose constraint (e.g. primary key) will be referenced by the new foreign key.
4. The Joins tab of the Reference Editor will be shown. You can choose columns of a referenced (parent) and referencing (child) table participated in the reference. The properties of auto column will be copied from the respective column of the referenced table.
Please note:
To create a self reference (that links columns at the same table), click the Reference () item on the Main tab of Ribbon. Then click the same table two times. To create N:M references, please refer to Creating a Many-to-Many Reference.
Please note:
By default Database Designer for MySQL draws references automatically using shortest line between table shapes on diagram with reference's label placed in the center of this line. Sometimes this can lead to references crossing, or reference's label can fit below table shape. Starting from version 2.0 you can route references manually to avoid this. Take a look at Manual Reference Drawing topic.
Reference Creation in Detail
On reference creation, Database Designer for MySQL performs the following actions:
1. Creates new column(s) in the referencing table, their parameters (name, data type) will be copied from the primary key constraint of the referenced table. If the referencing table already has column(s) analogous to the primary key(s) of the referenced table, this column(s) will be used as foreign-key column(s).
2. If there is no primary key or unique constraints in the referenced table, a standard primary key column will be created in the referenced table.
3. Creates foreign key constraint in the referencing table that refers to the referenced table primary key(s).
Please note:
Starting with MySQL 4.1.2 such foreign key constraints for references are created automatically by MySQL.So you can disable FK-indexes creation in "Default Database Options" tab of Diagram Properties dialog.
See the Diagram Display Preferences topic to find out more about default References options, actions, etc.
See the Notation topic to find out more about reference symbol on the diagram.
See also:
Diagram: Notation | "Default Database Options" tab | Diagram Properties
Diagram Objects: Reference Editor | Creating a Many-to-Many Reference