Using MySQL CHECK Constraints from Version 8.0.16 Onward

Beginning with MySQL 8.0.16, the database server introduces support for the CHECK constraint. This feature enables the enforcement of conditions on data being inserted or updated. If the constraint is not met during the execution of an SQL statement, MySQL throws a general error and halts the transaction. CHECK constraints allow you to implement backend business rules to maintain data integrity within your application. By applying constraints directly within the database, you ensure that all database users comply with established rules, even when bypassing a user interface.

As an example, consider enforcing a rule where all personnel must be residents of CALIFORNIA. To prevent violations of this rule, validation logic can be embedded directly in the database. Should a database user attempt to enter data for an employee residing in a non-permitted state, an error would be triggered. This guide will demonstrate how to create a sample_company database and an employees table, and how to apply a CHECK constraint to the state field, restricting entries to your defined criteria for new employees.

Requirements

To work through this example, ensure the following components are in place:

• A server running Ubuntu 20.04
• Access to a user with sudo privileges
• A LAMP stack installed.

Creating the sample_company Database

Begin by connecting to your server and logging into MySQL with root privileges:

$ sudo mysql -u root -p

When prompted, enter your MySQL root password and press Enter. Then execute the following command to create the sample_company database:

mysql> CREATE DATABASE sample_company;

Switch the context to the newly created database using:

mysql> USE sample_company;

Define the employees Table

Next, you’ll construct an employees table to serve as a register for new personnel. Every entry in the table represents an employee, identified by a unique employee_id. The first_name and last_name columns will store the names of staff members, while the state field will indicate their location.

Execute the command below to establish the table structure:

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 populating the table with data, you will define a validation rule in the following step.

Apply a CHECK Constraint to Limit State Values

Now that the employees table exists, you’ll apply a CHECK constraint to enforce that only entries from the CALIFORNIA state are allowed.

Use the SQL command below to set this restriction:

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

To verify that the constraint was successfully added, you should see output resembling the following:

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

Verify the CHECK Constraint Functionality

To confirm that the CHECK constraint on the state column is functioning correctly, begin by inserting a valid entry into the employees table. Ensure that the state value matches the allowed condition.

Use the following SQL command to add a compliant record:

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

This record should be added to the employees table without triggering any errors.

Query OK, 1 row affected (0.00 sec)

Now, verify that the data has been successfully inserted by querying the table:

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

If the entry was successful, the output should appear similar to this:

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

Next, try to enter an employee whose state does not comply with the constraint—specifically, a staff member from FLORIDA. This test will demonstrate whether the CHECK constraint properly restricts invalid entries.

Execute the command below to attempt this insertion:

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

This operation should fail, and MySQL will return a general error indicating a violation of the constraint.

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

Implement Multiple State Constraints with MySQL

To expand the validation logic, you can define a list of acceptable states using the IN clause in MySQL. This adjustment lets the employees table accept entries from several predefined states.

To begin, you need to remove the current CHECK constraint before applying the new one. First, identify the existing constraint’s name by querying the INFORMATION_SCHEMA table with the command below:

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';

From the result, confirm the name of the constraint as shown under the CONSTRAINT_NAME column:

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

Proceed to delete the constraint using the following command:

mysql> ALTER TABLE employees
       DROP CHECK  employees_chk_1;

The successful removal should return this message:

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

Now, define a new CHECK constraint that allows entries only from CALIFORNIA, COLORADO, and ARIZONA:

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

After running the above, you should receive the following confirmation:

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

Next, test the newly defined rule by inserting employee records from COLORADO and ARIZONA:

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'
       );

If the insertion is successful, you will see:

Query OK, 1 row affected (0.00 sec)

Use a SELECT statement to check that all three records are now present in the table:

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

The expected output should list all three employees:

+-------------+------------+-----------+------------+
| 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)

To verify the constraint further, attempt to add an employee from a non-approved state, such as HAWAII:

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

This operation should be blocked and return the following error message:

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

Summary

In this tutorial, you’ve learned how to implement MySQL CHECK constraints to safeguard data integrity. This method ensures only approved values make it into your database, supporting the reliability and consistency of your business logic at the database level.