Posted inMySQL

MySQL 8 Check Constraints: Stop Junk Data at the Gate

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Why You Shouldn’t Rely Entirely on the Backend

If you’ve ever used older versions of MySQL (like 5.7), you’ve likely experienced the feeling of being “tricked” when writing a CHECK statement, only to have the database silently accept invalid data anyway. Before version 8.0.16, MySQL only accepted the syntax for show but didn’t actually enforce it. All logic like “price must be positive” or “age must be 18+” was pushed entirely to the Application layer.

Trusting the Backend implicitly is a costly mistake. I once handled an incident at an e-commerce platform where an employee accidentally entered a product price as a negative number through a direct management tool. Because there was no constraint in the database, the system accepted a price of -100k, leading to hundreds of erroneous orders within just 10 minutes. Check Constraints were designed to be the final shield, stopping all junk data whether it comes from code, import scripts, or manual operations.

When you define rules directly in the table structure, MySQL will flatly reject any INSERT or UPDATE commands that violate them. This ensures data remains clean and consistent, reducing the validation burden for backend developers.

Basic Implementation: Don’t Forget to Name Your Constraints

Prerequisite: You must be running MySQL 8.0.16 or higher. Older versions will silently ignore the constraints without any warning, which is extremely dangerous for your system.

Here is how I usually set up a products table to ensure safety:

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2),
    stock_quantity INT,
    -- Constraint: price must be greater than 0
    CONSTRAINT chk_price_positive CHECK (price > 0),
    -- Constraint: stock cannot be negative
    CONSTRAINT chk_stock_min CHECK (stock_quantity >= 0)
);

A small but crucial note: Always give your constraints explicit names (like chk_price_positive). If you let MySQL auto-generate names like products_chk_1, you will have a real struggle when you need to debug or delete them later.

Try inserting a record that violates the rules:

INSERT INTO products(product_name, price, stock_quantity) 
VALUES ('iPhone 15', -100, 10);

MySQL will block it immediately with the message: Check constraint 'chk_price_positive' is violated.

3 Real-World Application Scenarios

In actual projects, logic is often more complex than simple greater-than/less-than comparisons. Here are 3 more flexible ways to use them.

1. Multi-column Constraints

In a promotions table, the end date must be after the start date. Check Constraints handle this very cleanly:

CREATE TABLE promotions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    start_date DATE,
    end_date DATE,
    CONSTRAINT chk_promo_dates CHECK (end_date >= start_date)
);

2. Replacing ENUM with the IN Operator

The ENUM type in MySQL can sometimes be rigid when changes are needed. I often use VARCHAR combined with a Check Constraint for more flexibility:

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    status VARCHAR(20),
    CONSTRAINT chk_order_status CHECK (status IN ('pending', 'processing', 'shipped', 'cancelled'))
);

3. Applying to Existing Tables

When a table is already large—for example, a users table with millions of rows—adding a constraint requires caution. Use ALTER TABLE to add new rules:

ALTER TABLE users 
ADD CONSTRAINT chk_user_age CHECK (age >= 18);

Warning: If the table contains even a single row of violating data (e.g., a 15-year-old user), the above command will fail immediately. You must clean up the junk data before applying the rule.

Performance and Key Limitations

Many worry that Check Constraints will slow down the system. In reality, the impact is minimal, usually under 1%, and much more efficient than using Triggers (which can cause 20-30% overhead). Checking at the engine layer ensures the fastest possible error response.

However, this tool is not a silver bullet. Keep these 3 major limitations in mind:

  • Cannot use non-deterministic functions like NOW() or CURRENT_TIMESTAMP().
  • Cannot reference columns from other tables (use Foreign Keys for that).
  • Subqueries are not allowed in the check expression.

Advice from my experience: Prioritize Check Constraints for immutable rules like price, quantity, or fixed statuses. For business logic that changes seasonally, keep it at the Application layer to avoid frequent database schema changes.

Share:
Tags: