Creating Database Relations with the Database Manager

[Teodor]

With the new Database Manager you can easily setup relations between tables. You can create a reference field and select which other table it references - the relation and foreign keys between the tables will be created automatically.

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 to NULL .

• NO ACTION : is the same as RESTRICT. If you don’t specify the ON DELETE and ON UPDATE clause, the default action is RESTRICT .

Apply Changes

Don’t forget to hit the Apply Database Changes button when you are done!