Knowledgebase

Introduction

In MySQL, referential integrity is a concept that maintains the relationship of data across multiple tables for consistency purposes. For instance, in an e-commerce shop, the category_id column in the products table might refer to the same column in the product_categories table.

In most cases, you'll implement the consistency of references in different database tables to enforce business logic, validate users' input, and create a more accurate application. This is very useful when you're designing a database where multiple users may change data.

The only way you can enforce referential integrity in MySQL is by using a foreign key. This is an indexed column in a child table that refers to a primary key in a parent table. This is achievable if you design your tables with MySQL InnoDB database engine.

In this guide, you'll create a test database and a set of two tables, and later, you'll use a foreign key across the tables to enforce referential integrity in the MySQL database.

Prerequisites

To proceed with this tutorial, you'll require the following:

• An Ubuntu 20.04 server.
• A sudo user.
• A Lamp Stack.

While this guide takes you through the steps in an Ubuntu 20.04 server, you're free to use any OS(Operating System) that supports the MySQL database.

Set Up a Test Database

SSH to your server and run the command below to log in to the MySQL database as root.

$ sudo mysql -u root -p

Type the root password of your MySQL server and hit Enter to proceed. Once you get the mysql> prompt, create a sample_shop database.

mysql> CREATE DATABASE sample_shop;

Switch to the sample_shop database.

mysql> USE sample_shop;

Before you begin creating the linked tables, make sure that their design meets the following constraints:

• The linked tables must use the InnoDB database engine
• The related columns that you intend to use as foreign keys must be indexed
• The data types of the related columns must be the same. That is, if the parent table uses the INT data type, the same data type must be propagated across the child tables

Once you're sure that your database design meets the above conditions, you may proceed to create the tables.

Create the Parent Table

Next, create a products_categories table. This is the parent table in your database. The category_id column is the primary key on this table and will be referenced by other child tables in the database. The category_name is a descriptive human-readable name for classifying products in your sample_shop database.

So, create the products_categories table in the sample_shop database. Please note the ENGINE = InnoDB statement at the end of the command below. Although this might be the default engine, it is advisable to declare it here in case someone changed the MySQL configuration file, especially when working in a multi-user environment.

mysql> CREATE TABLE products_categories (
       category_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
       category_name  VARCHAR(50)
       ) ENGINE = InnoDB;

Populate the products_categories table with some data. Don't supply any value to category_id. The database automatically handles this because you've designed the column with the AUTO_INCREMENT keyword.

mysql> INSERT INTO products_categories (category_name) VALUES ('ELECTRONICS');
mysql> INSERT INTO products_categories (category_name) VALUES ('SOFT DRINKS');
mysql> INSERT INTO products_categories (category_name) VALUES ('FURNITURE');

Query the products_categories table to make sure that the data is in place.

mysql> SELECT
       category_id,
       category_name                   
       FROM products_categories;

Ensure you get a list showing the 3 categories that you've inserted:

+-------------+---------------+
| category_id | category_name |
+-------------+---------------+
|           1 | ELECTRONICS   |
|           2 | SOFT DRINKS   |
|           3 | FURNITURE     |
+-------------+---------------+
3 rows in set (0.00 sec)

Create the Child Table

Next, create a products table. You'll use this child table to store different items that you're offering in your hypothetical store. In this table, you'll define a category_id as a foreign column that refers back to the parent column on the products_categories table. As mentioned earlier in this guide, the child column referenced to the parent table must be indexed. In this case, you're using the statement INDEX (category_id) to index the column.

mysql> CREATE TABLE products 
       (
       product_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
       category_id INT NOT NULL, 
       product_name VARCHAR(50) NOT NULL,
       retail_price DOUBLE,      
       INDEX (category_id),
       FOREIGN KEY (category_id) REFERENCES products_categories (category_id) 
       )ENGINE = InnoDB;

Then, insert some products with a valid category_id column value.

mysql> INSERT INTO products (category_id, product_name, retail_price) VALUES ('1', 'WATCH', '23.60');
mysql> INSERT INTO products (category_id, product_name, retail_price) VALUES ('1', 'RADIO', '47.00');

mysql> INSERT INTO products (category_id, product_name, retail_price) VALUES ('2', 'ORANGE JUICE', '3.00');
mysql> INSERT INTO products (category_id, product_name, retail_price) VALUES ('2', 'LEMON JUICE', '2.70');

mysql> INSERT INTO products (category_id, product_name, retail_price) VALUES ('3', 'LEATHER COUCH', '560.00');
mysql> INSERT INTO products (category_id, product_name, retail_price) VALUES ('3', 'REXIN CHAIR', '127.00');

Query the products table to ensure the records are in place.

mysql> SELECT
       product_id,
       category_id,
       product_name,
       retail_price                 
       FROM products;

You'll get the output shown below displaying all the products in the table.

+------------+-------------+---------------+--------------+
| product_id | category_id | product_name  | retail_price |
+------------+-------------+---------------+--------------+
|          1 |           1 | WATCH         |         23.6 |
|          2 |           1 | RADIO         |           47 |
|          3 |           2 | ORANGE JUICE  |            3 |
|          4 |           2 | LEMON JUICE   |          2.7 |
|          5 |           3 | LEATHER COUCH |          560 |
|          6 |           3 | REXIN CHAIR   |          127 |
+------------+-------------+---------------+--------------+
6 rows in set (0.00 sec)

Now, the beauty of related tables comes into play when you join the tables together in a SELECT statement. In this case, if you want to get the human-readable category name of the products, you can join the products_categories and the products table in an SQL statement as shown below.

mysql> SELECT
       products.product_id,
       products.category_id,
       products_categories.category_name,
       products.product_name,
       products.retail_price                 
       FROM products
       LEFT JOIN products_categories
       ON products.category_id = products_categories.category_id;

When you run the above query, you should now see the output shown below showing the category names as they appear in the base/parent products_categories table. So, apart from enforcing referential integrity, you can leverage the power of related tables for reporting purposes.

+------------+-------------+---------------+---------------+--------------+
| product_id | category_id | category_name | product_name  | retail_price |
+------------+-------------+---------------+---------------+--------------+
|          1 |           1 | ELECTRONICS   | WATCH         |         23.6 |
|          2 |           1 | ELECTRONICS   | RADIO         |           47 |
|          3 |           2 | SOFT DRINKS   | ORANGE JUICE  |            3 |
|          4 |           2 | SOFT DRINKS   | LEMON JUICE   |          2.7 |
|          5 |           3 | FURNITURE     | LEATHER COUCH |          560 |
|          6 |           3 | FURNITURE     | REXIN CHAIR   |          127 |
+------------+-------------+---------------+---------------+--------------+
6 rows in set (0.00 sec)

Now, try to enter a product with an invalid category_id like 4 and see if the database will fire a referential integrity check.

mysql> INSERT INTO products (category_id, product_name, retail_price) VALUES ('4', 'CAR REMOTE', '20.00');

Since you don't have the category_id you've tried to insert in the base table, the INSERT statement fails with the error below.

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`sample_shop`.`products`, CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `products_categories` (`category_id`))

You're now sure that your database is working as expected, and no one can enter invalid data into the products table. Referential integrity works great in a multi-user environment and helps move the business logic to the backend of the application.

Conclusion

In this tutorial, you've set up a database, added a few tables, and tested the use of referential integrity in validating the business logic in your application. Feel free to extend the code even further when working with more tables.