Improve MySQL Data Integrity With Check Constraints

Introduction

Starting from MySQL 8.0.16 database server and later versions, a CHECK constraint is a feature that allows you to specify a condition that validates data during an INSERT or UPDATE command. If the constraint is violated when an SQL statement is issued against a table, MySQL raises a general error and aborts the operation altogether. You can use the MySQL CHECK constraints to enforce business logic at the backend of your application to achieve data integrity. Also, by defining specific data restrictions in the database server, you can be sure that all data administrators in a multi-user environment adhere to the set business rules when making modifications directly in the database without going through a user interface.

For instance, in your organization, you can place a policy that requires all employees to come from the state of CALIFORNIA. To avoid this rule from being violated in the system, you can code a validation logic in your database. Any database administrator registering employees from the non-allowlisted states should get an error. To put this in a better perspective, you'll create a sample_company database and an employees table in this guide. You'll then put a CHECK constraint to limit the value range that can be placed in the state column for new staff members joining your hypothetical company.

Prerequisites

To follow along with this tutorial, make sure you have the following:

• An Vultr Ubuntu 20.04 server.
• A sudo user.
• A LAMP Stack. You can also follow this tutorial on a Vultr One-Click LAMP server.

Create a sample_company Database

Connect to your server and log in to MySQL as root.

$ sudo mysql -u root -p

Enter the root password of your MySQL server and press Enter to proceed. Then, issue the command below to create a sample_company database`.

mysql> CREATE DATABASE sample_company;

Switch to the sample_company database.

mysql> USE sample_company;

Create an employees Table

You'll create an employees table. This table acts as an employee register, and new staff members' details must be inserted here. The employee_id will be a unique key to identify each employee. Then, the first_name and last_name fields will record the full names of the employees. Finally, you'll use the state column to record the employees' localities.

Run the command below to create the employees table.

mysql> CREATE TABLE employees (
       employee_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
       first_name VARCHAR(50),
       last_name VARCHAR(50),
       state  VARCHAR(50)
       ) ENGINE = InnoDB;

Before entering any records into the table, you'll set a validation rule in the next step.

Set a Check Constraint in the employees Table

With the employees table in place, you're going to set a CHECK constraint that only allows staff members from the CALIFORNIA state.

Run the command below to add the constraint.

mysql> ALTER TABLE employees
       ADD CHECK (state = 'CALIFORNIA');

Make sure you get the output below to confirm the table has been updated with a new constraint.

Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Test the CHECK Constraint

Create a valid record in the employees table. For this entry, don't violate the state column's CHECK constraint.

mysql> INSERT INTO employees
       (
       first_name,
       last_name,
       state
       )
       VALUES
       (
       'JOHN',
       'DOE',
       'CALIFORNIA'
       );

Your record should be inserted into the employees table without any problems.

Query OK, 1 row affected (0.00 sec)

Query the employees table to confirm if the record was inserted into the table.

mysql> SELECT
       employee_id,
       first_name,
       last_name,
       state
       FROM employees;

The output below confirms that the record is in place.

+-------------+------------+-----------+------------+
| employee_id | first_name | last_name | state      |
+-------------+------------+-----------+------------+
|           1 | JOHN       | DOE       | CALIFORNIA |
+-------------+------------+-----------+------------+
1 row in set (0.00 sec)

Next, attempt to insert a new employee from the FLORIDA state and see if the CHECK constraint for the state column will be violated.

Run the INSERT command below.

mysql> INSERT INTO employees
       (
       first_name,
       last_name,
       state
       )
       VALUES
       (
       'MARY',
       'SMITH',
       'FLORIDA'
       );

The command above should fail, and you should receive a standard error message displaying that the constraint has been violated.

ERROR 3819 (HY000): Check constraint 'employees_chk_1' is violated.

Map Multiple Constraints

For this demonstration, you may use the MySQL IN clause to create an allowlist of multiple states that you want to be accepted in the employees table.

To do this, you'll need to delete the first CHECK constraint and create a new one. Confirm the name of the constraint by running the SELECT command below against the INFORMATION_SCHEMA table.

mysql> SELECT
       CONSTRAINT_NAME,
       TABLE_SCHEMA,
       TABLE_NAME
       FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
       WHERE TABLE_NAME = 'employees'
       AND CONSTRAINT_SCHEMA = 'sample_company'
       AND  CONSTRAINT_TYPE = 'CHECK';

Check the name of the constraint as displayed in the CONSTRAINT_NAME column as shown below.

+-----------------+----------------+------------+
| CONSTRAINT_NAME | TABLE_SCHEMA   | TABLE_NAME |
+-----------------+----------------+------------+
| employees_chk_1 | sample_company | employees  |
+-----------------+----------------+------------+
1 row in set (0.01 sec)

Then, DROP the constraint. In this case, delete the employees_chk_1.

mysql> ALTER TABLE employees
       DROP CHECK  employees_chk_1;

The output below confirms the deletion of the employees_chk_1 constraint.

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Create a new constraint with the new rules.

mysql> ALTER TABLE employees
       ADD CHECK (state IN ('CALIFORNIA', 'COLORADO', 'ARIZONA'));

Ensure you get the output as shown below.

Query OK, 1 row affected (0.23 sec)
Records: 1  Duplicates: 0  Warnings: 0

Next, try to insert new records from both the COLORADO and ARIZONA state.

mysql> INSERT INTO employees
       (
       first_name,
       last_name,
       state
       )
       VALUES
       (
       'JANE',
       'MARK',
       'COLORADO'
       );

mysql> INSERT INTO employees
       (
       first_name,
       last_name,
       state
       )
       VALUES
       (
       'PETER',
       'SMITH',
       'ARIZONA'
       );

You should get a success message from both INSERT statements above.

 Query OK, 1 row affected (0.00 sec)

Confirm the data by running a SELECT statement against the employees table.

mysql> SELECT
       employee_id,
       first_name,
       last_name,
       state
       FROM employees;

You should now see a list of 3 employees as shown below.

+-------------+------------+-----------+------------+
| employee_id | first_name | last_name | state      |
+-------------+------------+-----------+------------+
|           1 | JOHN       | DOE       | CALIFORNIA |
|           2 | JANE       | MARK      | COLORADO   |
|           3 | PETER      | SMITH     | ARIZONA    |
+-------------+------------+-----------+------------+
3 rows in set (0.00 sec)

Again, try to violate the CHECK constraint with an employee from a non-allowlisted state such as HAWAII) and see if an error will be thrown.

mysql> INSERT INTO employees
       (
       first_name,
       last_name,
       state
       )
       VALUES
       (
       'FRANK',
       'JACOB',
       'HAWAII'
       );

You should get an error as shown below because FRANK JACOB does not come from CALIFORNIA, COLORADO or ARIZONA.

ERROR 3819 (HY000): Check constraint 'employees_chk_1' is violated.

This confirms that your CHECK constraint has indeed mapped multiple allowlist values and is working as expected.

Conclusion

In this tutorial, you've used MySQL CHECK constraint to improve your MySQL database's data integrity. You should always take advantage of this feature to enforce data quality and improve your business processes' logic.