Feb 202014
 
Article MySQL

The relationships between different tables that use the InnoDB storage engine in MySQL may be enforced by means of  Foreign Key Constraints.

These constraints make the database reject operations that would result in data inconsistencies. This post explains the possibilities offered by foreign key constraints, and how to implement them.

Sample tables

To illustrate this post, we will create a sample e-commerce database, including a table ‘customers’ and a table ‘orders’.

First, the table ‘customers’ with fields ‘id’ and ‘name’ is to be created. The ‘id’ field will be the primary key for this table (of course, in a real database there would be much more information in this table, such as contact phone number, contact email address, physical delivery address, etc., but these two fields are enough for the purpose of this post):

Next, the table ‘orders’ is created. There is also a primary key ‘id’ field, to uniquely identify each order. There is also a ‘customer_id’ field to identify the client that made the purchase. Finally, there is a ‘product_id’ field, to identify the product being purchased:

Setting up the constraint

In this database, we want to make sure that any attempt to insert in the “orders” table a record having a value for “customer_id” that does not exist in the “customers” table. To do this, we set up a constraint between the fields “customer_id” in the “orders” table and the “id” field in the “customers” table, as follows:

Once the constraint is in place, we can check that it is working as expected, inserting some some records in both tables:

As we can see in the example above, when an attempt is made to insert a second record in the “orders” table, with a value ‘2’  as the value of the customer_id field, that does not exist as a value of the “id” field  in the “customers” table,    an error is generated. In the same way, an error happens also if we try now to delete from the “customers” table the record with “id”=1, because there is a record in the orders table referencing it:

The same error would be generated if we try to execute an ‘UPDATE’ sentence to change the value of the ‘id’ field in the ‘customers’ table, leaving orphan the reference from the “orders” table.

Indexes  related to the constraints

To define a foreign constraint, there must be an index in the main table for the referenced field(s). In the example above, the main table is “customers”, and “id” is the referenced field. As this field is the primary key for the table, there is no need to explicitly create an index on it. In the referencing table, there must also be an index on the referencing fields(s). If it did not previously exists, it is automatically created at the time the foreing constraint is created. If we check now the table structure after having added the foreign key::

we can see that there is a new index “KEY customer_id (customer_id)”.

Clauses “ON DELETE” and “ON UPDATE”

As we have seen so far, the default behaviour of a foreing key constraint is to reject changes to the database done by ‘DELETE’ or ‘UPDATE’ sentences that would result in the referential integrity being broken. This default behaviour can be altered my means of the  “ON DELETE” and “ON UPDATE” clauses, that can take the following values:

  • RESTRICT – This is the default behaviour, that rejects changes that would result in inconsistencies in the referential integrity.
  • CASCADE – Used in a DELETE sentence, deletes records in the referencing table that reference a record being deleted in the main table. Used in an UPDATE table, updates the value of the secondary key in the referencing table when the value of the referenced main key is changed in the main table.
  • SET NULL – Sets to NULL the value of the secondary in the affected records in the referencing table, when a record is deleted in the main table or the value of the referenced key is changed.
  • NO ACTION – Disables the effect of the constraint, allowing the change (delete, update) requested. This results in the referential integrity being broken.

Example: If we drop the constraint that we had previously established, and create it again in “CASCADE” mode:

Now, a change done to the customer ID on the customers table is propagated to the referencing records on the orders table:

In the same way, if a record in the customers table is deleted, the referencing record(s) in the orders table are deleted as well:

One-to-one relationships

Until now, in our example we have been using a “one-to-many” type of relation between the referenced and the referencing tables: that is, for every customer there can be zero, one or more than one record in the orders table.

Example 1.

Now, let’s suppose that in our database we want to avoid having more than one order per client (in this case, we want to force a “one-to-one” relationship). To do this, we start by creating a unique index on the “customer_id” field in the “orders” table, and use that index to define the constraint:

Having defined the constraint in this way, trying to insert a second order for a given customer results in an error:

Example 2.

Now, we want to allow a customer to make several orders, but not more than one order per product. We would implement this constraint by defining a unique index on the “customer_id” and “order_id” fields, and defining the constraint based on this new index:

Now we can check that, as expected, we can insert several orders for the same client, but an error happens if we try to insert a second order for the same product:

 

Example of setting constraints on more than one table

This example shows that a given table can have constraints referencing more than one table. It also shows that a constraint can be defined on more than one field:

In this example, for a record to be inserted in table product_order, the values specified for ‘product_category’ and ‘product_id’ must exist as values of the fields ‘category’ and ‘id’ in table product.

 Posted by at 1:06 pm

 Leave a Reply

(required)

(required)