MySQL: Referential Integrity

I recently wrote an article covering Referential Integrity within Microsoft SQL Server.

This article covers the same subject within MySQL Server. The principle of using “Foreign Keys” is exactly the same.

We will be using phpMyAdmin to add the key(s). This will be familiar to anyone who maintains a MySQL Database. For the purpose of this exercise, I made a “test” database containing a “parent” table and a “child” table (and added a small amount of test data).

MySQL Foreign Keys 006

I would recommend practicing on a test system. If you intend to make changes to a live / production database, ensure that the data and schema are fully backed up!

Using phpMyAdmin, select the Database you wish to work on.

The first stage of the process is to check that BOTH tables in the relationship are using the “InnoDB” storage engine (MyISAM will not work). Within phpMyAdmin, select each table in turn, then set: Operations => Storage Engine = “InnoDB”.

MySQL Foreign Keys 005

If you remember from my previous article, the “Foreign Key” is always added to the “Child” table. So, if you have an “Orders” Table and an “Order Lines” table, the “Foreign Key” should be added to the “Order Lines” Table.

Select the “Child” table and then go to: Structure => Relation View.

MySQL Foreign Keys 007

Our objective is to create a relationship between the “Parent_No” Field in the “Child” and “Parent” tables.

MySQL Foreign Keys 009

Remember, we are working on the “Relation View” of the “Child” table. From here it is a simple process of completing the “Foreign Key” (INNODB) fields for the “Parent_No” field (see screen capture above). I would recommend setting the “On Delete” and “On Update” actions to “Restrict” – this will prevent any parent records from being removed if there are any associated child records.

If you are feeling brave (and have backed up your system), you can test your changes.

I have two “Parent” records in my test system, both of which relate to a number of records in the “Child” table.

MySQL Foreign Keys 003

If I attempt to remove any of the Parent Records, I will now receive an error from the Database Engine (RDMS).

MySQL Foreign Keys 004

That’s basically it! The database will no longer permit the removal of records from the “Parent” table if associated records still exists within the “Child” table.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s