In our database we have two tables -
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.
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
The primary key of the referenced table will be selected as a referenced field automatically:
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:
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 to
NO ACTION: is the same as
RESTRICT. If you don’t specify the
ON UPDATEclause, the default action is
Don’t forget to hit the Apply Database Changes button when you are done!