MySQL Check Constraints

Introduction

Data integrity is a fundamental concept in database design. One of the ways MySQL helps maintain data integrity is through check constraints, which allow you to define conditions that must be met before data can be inserted or updated in a table.

Check constraints ensure that only valid data according to your business rules enters your database. Think of them as gatekeepers that examine each piece of data before allowing it to be stored.

In this tutorial, you'll learn how to create, modify, and remove check constraints in MySQL to enforce data validation rules in your tables.

What are Check Constraints?

A check constraint is a type of constraint that allows you to specify a condition that must evaluate to TRUE for each row in the table. If the condition evaluates to FALSE or UNKNOWN, the insert or update operation fails.

Check constraints are only fully supported in MySQL 8.0.16 and later. In earlier versions, the syntax was parsed but not enforced.

Basic Syntax for Check Constraints

Adding a check constraint when creating a table

sql

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...,
    CONSTRAINT constraint_name CHECK (condition)
);

Adding a check constraint to an existing table

sql

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (condition);

Creating Tables with Check Constraints

Let's create a sample employees table with check constraints:

sql

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE NOT NULL,
    salary DECIMAL(10, 2),
    department VARCHAR(50),
    CONSTRAINT chk_salary CHECK (salary > 0),
    CONSTRAINT chk_hire_date CHECK (hire_date > '2000-01-01'),
    CONSTRAINT chk_department CHECK (department IN ('HR', 'IT', 'Finance', 'Marketing', 'Operations'))
);

In this example, we've created three check constraints:

1. chk_salary - ensures that salary is always greater than zero
2. chk_hire_date - ensures employees were hired after January 1, 2000
3. chk_department - limits department values to only those in our specified list

Testing Check Constraints

Let's see what happens when we try to insert data that violates our constraints:

Valid data insertion

sql

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary, department)
VALUES (1, 'John', 'Smith', '[email protected]', '2020-06-15', 65000.00, 'IT');

Output:

Query OK, 1 row affected (0.02 sec)

Invalid data - negative salary

sql

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary, department)
VALUES (2, 'Mary', 'Johnson', '[email protected]', '2021-03-10', -5000.00, 'HR');

Output:

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

Invalid data - hire date before 2000

sql

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary, department)
VALUES (3, 'Robert', 'Davis', '[email protected]', '1999-11-20', 72000.00, 'Finance');

Output:

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

Invalid data - department not in list

sql

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary, department)
VALUES (4, 'Lisa', 'Brown', '[email protected]', '2022-01-05', 58000.00, 'Research');

Output:

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

Complex Check Constraints

You can create more complex check constraints using logical operators like AND, OR, and NOT. Here's an example:

sql

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2),
    discount_percent DECIMAL(5, 2),
    category VARCHAR(50),
    in_stock BOOLEAN,
    CONSTRAINT chk_price CHECK (price > 0),
    CONSTRAINT chk_discount CHECK (discount_percent >= 0 AND discount_percent <= 75),
    CONSTRAINT chk_product_status CHECK (
        (in_stock = TRUE AND price IS NOT NULL) OR
        (in_stock = FALSE)
    )
);

In this example:

• chk_price ensures prices are positive
• chk_discount keeps discounts between 0% and 75%
• chk_product_status ensures that in-stock products must have a price

Adding Check Constraints to Existing Tables

If you already have a table and want to add a check constraint, you can use the ALTER TABLE statement:

sql

ALTER TABLE customers
ADD CONSTRAINT chk_age CHECK (age >= 18);

This would add a constraint ensuring that all customers are at least 18 years old.

Removing Check Constraints

To remove a check constraint, use the ALTER TABLE statement with DROP CONSTRAINT:

sql

ALTER TABLE employees
DROP CONSTRAINT chk_salary;

For older MySQL versions where named constraints might not work as expected, you can use:

sql

ALTER TABLE employees
DROP CHECK chk_salary;

Viewing Check Constraints

To view all check constraints in a database, you can query the INFORMATION_SCHEMA:

sql

SELECT TABLE_NAME, CONSTRAINT_NAME, CHECK_CLAUSE
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS USING (CONSTRAINT_SCHEMA, CONSTRAINT_NAME)
WHERE CONSTRAINT_SCHEMA = 'your_database_name';

Replace 'your_database_name' with your actual database name.

Real-world Applications

Example 1: E-commerce Database

In an e-commerce system, you might use check constraints to ensure:

sql

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20),
    total_amount DECIMAL(10, 2),
    shipping_address VARCHAR(200),
    CONSTRAINT chk_order_status CHECK (
        status IN ('Pending', 'Processing', 'Shipped', 'Delivered', 'Cancelled')
    ),
    CONSTRAINT chk_order_amount CHECK (total_amount >= 0),
    CONSTRAINT chk_shipping CHECK (
        (status != 'Cancelled' AND shipping_address IS NOT NULL) OR 
        status = 'Cancelled'
    )
);

Example 2: Human Resources System

For an HR system, check constraints can enforce business rules:

sql

CREATE TABLE employee_leaves (
    leave_id INT PRIMARY KEY,
    employee_id INT,
    start_date DATE,
    end_date DATE,
    leave_type VARCHAR(50),
    status VARCHAR(20),
    CONSTRAINT chk_leave_dates CHECK (end_date >= start_date),
    CONSTRAINT chk_leave_type CHECK (
        leave_type IN ('Annual', 'Sick', 'Maternity', 'Paternity', 'Unpaid')
    ),
    CONSTRAINT chk_leave_status CHECK (
        status IN ('Pending', 'Approved', 'Rejected', 'Cancelled')
    )
);

Example 3: Banking Application

For a banking system, check constraints could enforce account rules:

sql

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    account_type VARCHAR(50),
    balance DECIMAL(15, 2),
    interest_rate DECIMAL(5, 2),
    overdraft_limit DECIMAL(10, 2),
    CONSTRAINT chk_account_type CHECK (
        account_type IN ('Checking', 'Savings', 'Fixed Deposit', 'Loan')
    ),
    CONSTRAINT chk_balance_and_type CHECK (
        (account_type = 'Loan' AND balance <= 0) OR
        (account_type IN ('Checking', 'Savings', 'Fixed Deposit') AND balance >= 0)
    ),
    CONSTRAINT chk_interest CHECK (
        (account_type = 'Loan' AND interest_rate > 0) OR
        (account_type IN ('Savings', 'Fixed Deposit') AND interest_rate >= 0) OR
        (account_type = 'Checking' AND interest_rate = 0)
    ),
    CONSTRAINT chk_overdraft CHECK (
        (account_type = 'Checking' AND overdraft_limit >= 0) OR
        (account_type IN ('Savings', 'Fixed Deposit', 'Loan') AND overdraft_limit = 0)
    )
);

Best Practices for Check Constraints

1. Use meaningful names for constraints to make them easier to identify and manage.
2. Keep constraints simple where possible - complex constraints can be harder to debug.
3. Document your constraints to help other developers understand the business rules.
4. Test thoroughly after adding constraints to ensure they work as expected.
5. Consider performance - very complex check constraints could slow down INSERT and UPDATE operations.
6. Don't rely only on check constraints for critical validation - also implement validation in your application.

Limitations of Check Constraints

1. Check constraints can't reference other tables.
2. They can't call stored procedures or user-defined functions.
3. They can't use subqueries.
4. In MySQL versions before 8.0.16, check constraints were parsed but not enforced.

Summary

Check constraints are a powerful tool for enforcing data integrity in MySQL databases. They allow you to define rules that must be followed for data to be inserted or updated in your tables. By implementing check constraints, you can:

• Prevent invalid data from entering your database
• Enforce business rules at the database level
• Reduce the need for application-level validation
• Improve overall data quality

Remember that check constraints are only fully supported in MySQL 8.0.16 and later. If you're using an earlier version, you'll need to implement validation rules at the application level or through triggers.

Additional Resources

Here are some exercises to help reinforce what you've learned:

1. Create a students table with check constraints for:

   • Age (must be between 18 and 65)
   • GPA (must be between 0.0 and 4.0)
   • Status (must be one of 'Enrolled', 'Graduated', 'On Leave', or 'Expelled')

2. Modify an existing table to add a check constraint that ensures a date field is not in the future.

3. Write a query to list all the check constraints in your database and what tables they apply to.

4. Create a complex check constraint that enforces a rule where certain combinations of values are required (e.g., if column A has value X, then column B must have value Y).

By practicing with these exercises, you'll gain confidence in implementing data validation rules with MySQL check constraints.