MySQL CHECK Constraint: Syntax, Examples, and How to Use It

What is a CHECK constraint in MySQL?

In MySQL, a CHECK constraint is a rule that restricts the values that can be inserted into a table. When you set a CHECK constraint, you apply certain conditions that the value must meet to pass the check; otherwise, it will be rejected. A CHECK constraint is defined in CREATE TABLE or ALTER TABLE statements, restricting the values allowed in the table.

For example, if you want to maintain a table listing all company employees, it may be a good idea to set a CHECK constraint rejecting any age value lower than 18. This way, you ensure compliance with the labor laws and enforcement of the corresponding procedures in the organization. To validate the age values in the database, you can set the CHECK constraint as follows:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    CHECK (age >= 18)
);

This constraint will ensure that any new records inserted into the employees table have the age value equal to or higher than 18.

MySQL supports other constraints besides CHECK. While all of them are used to define and validate data in tables, these constraints serve different purposes:

• CHECK - enforces a condition on table values
• NOT NULL - ensures that a table column has no NULL (missing) values
• UNIQUE - guarantees that no duplicate values exist
• PRIMARY KEY - uniquely identifies each record in the table
• FOREIGN KEY - maintains relationships between tables
• DEFAULT - sets a default value

Before MySQL 8.0.16, when CHECK constraints were first introduced, developers had to use other, more complex data validation techniques, such as triggers, throwing errors if a specified condition was not met, or stored procedures containing validation logic that was applied to all data inserts and updates. If a CHECK constraint was added to a table, it was ignored. Now, with CHECK constraints available, data validation has become simpler and faster.

Why use CHECK constraints

CHECK constraints are a useful tool that helps you ensure that data in your tables always meets the specified criteria. There are many practical use cases for CHECK constraints, for example:

• Restricting values in the salary column to be higher than 0
• Ensuring that the dates are in the accepted format: European (DD/MM/YYYY) or American (MM/DD/YYYY)
• Making sure that dates correlate in time; for example, an employee's hiring date cannot be earlier than their date of birth

In other words, CHECK constraints improve data quality, reduce errors, and make databases a reliable source of information.

CHECK constraint syntax in MySQL

CHECK constraints are specified during table creation or modification, setting the rules for any further data insertions into the table. Therefore, they are defined in either CREATE TABLE or ALTER TABLE statements.

For example, you can create a new table named products and set a CHECK constraint to make sure that all prices are positive values:

CREATE TABLE products (
  id INT PRIMARY KEY,
  price DECIMAL(10,2) CHECK (price > 0)
);

With an ALTER TABLE statement, you can modify an existing table to enforce a validation rule. In the following example, a CHECK constraint is added to the payment table to make sure all payment amount values are equal to or higher than 0:

ALTER TABLE payment
ADD CONSTRAINT chk_payment_amount_positive CHECK (amount >= 0);

Now, if you try to insert a negative amount value into the table, the query results in an error, and the data insertion is rejected:

To disable a CHECK constraint, you need to completely remove it. Unlike SQL Server, MySQL does not support disabling and enabling for CHECK constraints; you can only add them or drop them fully. To drop a CHECK constraint, use an ALTER TABLE statement:

ALTER TABLE payment
DROP CHECK chk_payment_amount_positive;

Dropping a CHECK constraint completely removes it from the table. To re-enable it, you need to recreate the constraint from the beginning.

MySQL CHECK constraint examples

CHECK constraints can be set up to enforce different conditions and validate different value types. Let's look at some typical examples of how CHECK constraints can be used to ensure that only the data meeting the conditions is inserted.

Example 1 - Numeric value constraint

With a CHECK constraint, you can make sure that numeric values meet a certain condition: being positive or negative, being within a specified range, etc.

In the following example, let's add a CHECK constraint to the rental_rate column of the film table, restricting the values to positive ones:

ALTER TABLE film
ADD CONSTRAINT chk_rental_rate_positive CHECK (rental_rate > 0);

An attempt to insert a negative value into the rental_rate column results in an error:

Example 2 - String value constraint

A CHECK constraint can be set up to only allow specific values in a column. This may be useful when you want the table to display, for example, a list of active customers or full-time employees.

As an example, let's add a CHECK constraint to the status column of the employees table to make it only allow active as a value. This way, no other string values will be accepted:

ALTER TABLE employees
ADD CONSTRAINT chk_employee_status CHECK (status = 'active');

Now, if you insert any other string value into the status column, for example, full-time, this entry will be rejected:

Example 3 - Multiple conditions with AND/OR

You can enforce more than one condition with a CHECK constraint, making sure that the inserted data matches all of them.

In the following example, let's add a constraint to the rental table to restrict new entries to allow only positive values for the rental_rate column and make sure that the return_date is not earlier than the rental_date:

ALTER TABLE rental
ADD CONSTRAINT chk_multiple_columns CHECK (rental_rate > 0 AND return_date > rental_date);

When the inserted data does not meet the conditions set in the CHECK constraint, it is rejected:

Similarly, you can set up a CHECK constraint with an OR operator that demands that either of the criteria be met.

Example 4 - Nested CHECK constraint with NOT

You can use a CHECK constraint to specify not only which data should be included, but also which data should be excluded. In this case, the constraint contains the NOT operator that defines the conditions that must not be met.

To try this approach, let's reverse the previous example, specifying the data that should not be allowed in the table:

ALTER TABLE rental
ADD CONSTRAINT chk_multiple_columns CHECK (NOT (rental_rate <= 0 AND return_date <= rental_date));

When enabled, this CHECK constraint will reject any entries containing negative values of the rental_rate and the return_date preceding the rental_rate.

How to show constraints in MySQL

When working with MySQL database tables, it may be necessary to see which constraints have been applied to a table. There are several ways to view the existing constraints by using SQL statements.

SHOW CREATE TABLE

The SHOW CREATE TABLE statement displays the whole definition used to create the table. It returns the basic table data, including the currently existing constraints:

INFORMATION_SCHEMA.TABLE_CONSTRAINTS

To find whether a specific constraint is used in a table, you can run the INFORMATION_SCHEMA.TABLE_CONSTRAINTS query. It must specify the database and table you want to query and the type of constraint you need to display:

SELECT
  CONSTRAINT_NAME,
  CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'sakila'
AND TABLE_NAME = 'rental'
AND CONSTRAINT_TYPE = 'CHECK';

The result contains the CHECK constraints currently enabled for the table:

INFORMATION_SCHEMA.CHECK_CONSTRAINTS

To see all CHECK constraints currently enabled in a database, you can use the INFORMATION_SCHEMA.CHECK_CONSTRAINTS query:

SELECT 
    CONSTRAINT_NAME,
    CHECK_CLAUSE
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'sakila';

The result lists all the CHECK constraints currently enabled in the requested database:

Limitations of the CHECK constraint in MySQL

While being a useful feature helping validate table data, CHECK constraints in MySQL have some limitations:

No subqueries are allowed

MySQL does not allow subqueries inside CHECK constraints. For example, the following CHECK expression containing a SELECT query is invalid:

CHECK (price > (SELECT AVG(price) FROM products))

Expression support is limited

In MySQL, CHECK constraints must contain a boolean expression based on:

• Column references
• Literals
• Deterministic functions
• Simple comparisons

A CHECK constraint containing a non-deterministic function, for example, RAND(), a stored function, or any complex logic is invalid and will be rejected.

Enforcement depends on the storage engine

Currently, CHECK constraints are only enforced in InnoDB. Other MySQL storage engines, such as MyISAM or MEMORY, and others, parse CHECK constraints, but ignore them.

CHECK constraints cannot be temporarily disabled

In MySQL, if you want to disable a CHECK constraint, you need to drop it, which results in its complete removal. To re-enable the constraint, you have to recreate it later.

Using CHECK with other constraints

To enforce multiple layers of data integrity, you can combine CHECK constraints with others, for example, NOT NULL, UNIQUE, or FOREIGN KEY. In this case, each constraint will handle a different aspect of validation, improving the overall data quality.

Let's look at some examples of using several constraints together.

CHECK + NOT NULL

When used together, CHECK and NOT NULL ensure that a column must not contain NULL values and that all values meet the specified condition:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    age INT NOT NULL CHECK (age >= 18),
    salary DECIMAL(10,2) NOT NULL CHECK (salary > 0)
);

In this example, the CHECK constraint ensures that all age values are equal to or higher than 18 and that the salary value is always positive. The NOT NULL constraint makes sure that these values are always provided.

CHECK + UNIQUE

A combination of CHECK and UNIQUE constraints verifies that all values in a column are unique and meet a certain condition:

CREATE TABLE products (
  product_code varchar(20) UNIQUE CHECK (LENGTH(product_code) >= 5),
  price decimal(8, 2) CHECK (price > 0)
);

Here, the UNIQUE constraint verifies that each product code is unique, while the CHECK constraint makes sure that it has at least 5 characters and that price values are always positive.

CHECK + FOREIGN KEY

With a FOREIGN KEY constraint, you can link a column to another table, maintaining data integrity between the two tables, while setting additional conditions with a CHECK constraint:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    quantity INT CHECK (quantity > 0),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

In this example, the FOREIGN KEY constraint ensures that the customer_id for each order exists in the customers table. At the same time, the CHECK constraint verifies that the quantity value is always positive.

Best practices for using CHECK constraints with other constraints

By using multiple constraints, you ensure comprehensive validation of your data and improve its overall quality and trustworthiness. Below are useful tips to keep in mind when configuring constraints for your database:

• Keep constraints clear and explicit. Avoid including complex logic inside CHECK constraints and give them clear names to improve management and maintainability.
• Make sure that constraints do not conflict with each other.
• Use constraints that are absolutely necessary; redundant constraints may affect query performance.
• Make sure you are using MySQL version 8.0.16 or higher, as older versions do not support CHECK constraints.

Alternatives to CHECK constraints in MySQL

While CHECK constraints are a reliable feature that allows you to validate data, sometimes, you might need to look for other techniques, for example, when you are using a MySQL version earlier than 8.0.16. Although MySQL versions before 8.0.16 do not support CHECK constraints, there are ways to maintain data integrity that you can use, for example, the ENUM data type and triggers.

ENUM data type

ENUM is a data type assigned to columns that can only accept a predefined set of values. The typical use cases include such properties as status or type that have a limited set of valid options. The accepted ENUM values are defined in advance and reject any value outside the specified list, thus imitating the behavior of the CHECK constraint.

The following example shows how to assign the ENUM data type to a column:

CREATE TABLE orders (
    OrderID INT PRIMARY KEY,
    Status ENUM('Pending', 'Shipped', 'Delivered', 'Cancelled') NOT NULL
);

Triggers

MySQL triggers are database objects that run automatically in response to a predefined event. They can implement more complex data validation logic than CHECK constraints, verifying that only the allowed data is accepted into the table.

For example, you can use a trigger to validate the values in the quantity column and return an error whenever a negative value is entered:

CREATE TRIGGER trg_validate_quantity_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  IF NEW.quantity <= 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Quantity must be greater than 0';
  END IF;
END;

How to add CHECK constraints with dbForge Studio for MySQL

CHECK constraints can be added together with CREATE TABLE and ALTER TABLE SQL statements, defining data validation conditions during the creation and modification of tables. However, with MySQL GUI tools, such as dbForge Studio for MySQL, you can do it in a visual interface without writing SQL statements.

MySQL table editor, a multi-featured SQL table creation tool, supports adding and editing CHECK constraints for both new and existing tables. Database Explorer displays all table details, including the currently enabled constraints. To add a new constraint, right-click Constraints, select New Check Constraint, and proceed to edit the constraint in an intuitive visual interface. Similarly, you can edit or remove previously enabled CHECK constraints.

CHECK constraint management is only one of the features you can benefit from with dbForge Studio for MySQL. Download the 30-day free trial and explore the rich functionality of this convenient and flexible database management environment.

Conclusion

In MySQL, CHECK constraints represent an effective technique for improving the quality of data entered into tables. They help you enforce business logic at the database level, raising data reliability and trustworthiness and reducing errors in your applications. With properly configured constraints, you can boost the performance of your MySQL-based products and accelerate their implementation.

Try enabling CHECK constraints in your databases - download dbForge Studio for MySQL and see how easy and effective working with tables can be.

FAQ

ALTER TABLE payment
ADD CONSTRAINT chk_payment_amount_positive CHECK (amount >= 0);

SELECT 
    CONSTRAINT_NAME,
    CHECK_CLAUSE
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'your_database_name';

ALTER TABLE rental
ADD CONSTRAINT chk_multiple_columns CHECK (rental_rate > 0 AND return_date > rental_date);