Tables Overview
In our database we have two tables - blog_posts and authors. The blog_posts table stores the blog posts info and the authors table stores the info about the authors of the blog posts:
We don’t want to enter the author name, bio, photo and other info over and over again in every blog post we publish so we are going to reference the author in the the blog posts table.
Database Relations
Creating a Reference Field
Right click the blog_posts table and add New Reference:
Let’s call it authorid. You can find the Reference Field properties below, in the properties panel:
Selecting a Reference Table
Open the Table menu, and select the table you want to reference this field to. In our case this is the authors table:
The primary key of the referenced table will be selected as a referenced field automatically:
Advanced Options
There are a couple of advanced options available for the reference fields. You can select automatic on Delete and on Update relation actions from the two dropdowns:
These option specify how the foreign key maintains the referential integrity between the child and parent table.
MySQL has five reference options: CASCADE , SET NULL , NO ACTION , RESTRICT , and SET DEFAULT .
-
CASCADE: if a row from the parent table is deleted or updated, the values of the matching rows in the child table automatically deleted or updated. -
RESTRICT: if a row from the parent table has a matching row in the child table, MySQL rejects deleting or updating rows in the parent table. -
SET DEFAULT: When a referenced foreign key is deleted or updated, the columns of all rows referencing that key are set to the default value for that column. -
SET NULL: if a row from the parent table is deleted or updated, the values of the foreign key column in the child table are set toNULL. -
NO ACTION: is the same asRESTRICT. If you don’t specify theON DELETEandON UPDATEclause, the default action isRESTRICT.
Apply Changes
Don’t forget to hit the Apply Database Changes button when you are done!
Last updated: