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).
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”.
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.
Our objective is to create a relationship between the “Parent_No” Field in the “Child” and “Parent” tables.
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.
If I attempt to remove any of the Parent Records, I will now receive an error from the Database Engine (RDMS).
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.
