During the process of normalization, groups of fields that represent a distinct entity are removed from a larger and/or more central table to a separate one. Common fields (usually IDs) are then employed to maintain their relationship. We can see an example below:
In relational database, referential integrity between tables is enforced using foreign key constraints.
This blog will cover how foreign keys work as well as how to create a foreign key constraint in MySQL using Navicat 16 for MySQL .
About the Film and Inventory Relation
The model that we saw in the intro depicts a one-to-many relationship between the film and inventory tables whereby a film entity (1 row) may link to zero or more entities (rows) in the inventory table.
The film table is called the parent table or referenced table, and the inventory table is known as the child table or referencing table. As such, the foreign key columns of the child table often refer to the primary key columns of the parent table.
In this example, we are only focusing on one relation. In fact, a table can have more than one foreign key where each foreign key references to a primary key of the different parent tables.
Once a foreign key constraint is in place, the foreign key columns from the child table must have the corresponding row in the parent key columns of the parent table or values in these foreign key columns must be NULL. For example, each row in the inventory table has a film_id that exists in the film_id column of the film table. Multiple rows in the inventory table can have the same film_id.
In the next section we'll create a Foreign Key Constraint for this relationship in Navicat 16 for MySQL.
Creating a Foreign Key Constraint in Navicat
In Navicat, you'll find Foreign Key Constraints on the Foreign Keys tab of the Table Designer. To create a new Foreign Key Constraint, open the parent table - in this case film - in the Table Designer and click the Add Foreign Key button. That will create a new row in the Foreign Keys list:
Next, select the "film" table from the Fields drop-down, the "inventory" table from the Referenced Table drop-down and the "film_id" for the Referenced Fields:
The next step is to choose the On Delete and On Update actions. MySQL supports five different referential options, as follows:
- CASCADE: It is used when we delete or update any row from the parent table, the values of the matching rows in the child table will be deleted or updated automatically.
- SET NULL: It is used when we delete or update any row from the parent table, the values of the foreign key columns in the child table are set to NULL.
- RESTRICT: It is used when we delete or update any row from the parent table that has a matching row in the reference(child) table, MySQL does not allow to delete or update rows in the parent table.
- NO ACTION: It is similar to RESTRICT. But it has one difference that it checks referential integrity after trying to modify the table.
- SET DEFAULT: The MySQL parser recognizes this action. However, the InnoDB and NDB tables both rejected this action.
Let's follow the example of the existing FK and choose an On Delete action of RESTRICT and an On Update action of CASCADE:
Finally, click the Save button to create the new Foreign Key Constraint. Note that Navicat will create the name for you if you do not populate the Name field.
Conclusion
Foreign Keys play an essential role in maintain referential integrity between tables. As such, one should be created for every table relationship. Navicat 16 for MySQL makes it quite easy to manage your Foreign Key Constraints without having to write any SQL commands.
