MySQL - Check Constraints

MySQL - CHECK Constraint

A CHECK constraint is a type of database constraint that allows you to specify a condition that must be true for any data being inserted or updated in a column. This is a powerful feature for enforcing data integrity and business rules directly at the database level.

### Important Version Note

Historically, MySQL would parse the `CHECK` constraint syntax but did not enforce it. **Since MySQL 8.0.16, `CHECK` constraints are fully supported and enforced.** This tutorial focuses on the modern, native implementation. The older workaround using triggers will be discussed as an alternative for complex scenarios.

Adding a CHECK Constraint on a Single Column

You can define a CHECK constraint when creating a table. The constraint ensures that a specific condition is met for a single column’s value.

Syntax

You can define the constraint inline with the column definition or at the table level.

-- Inline syntax
CREATE TABLE table_name (
    column1_name data_type,
    column2_name data_type CHECK (condition),
    ...
);

-- Table-level syntax
CREATE TABLE table_name (
    column1_name data_type,
    column2_name data_type,
    ...,
    [CONSTRAINT constraint_name] CHECK (condition)
);

Example

Let’s create an Products table where the stock_quantity must not be negative and the price must be greater than zero.

CREATE TABLE Products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    stock_quantity INT NOT NULL CHECK (stock_quantity >= 0),
    price DECIMAL(10, 2),
    CONSTRAINT price_must_be_positive CHECK (price > 0.00)
);

Now, let’s test the constraint. An attempt to insert a product with a negative quantity will fail.

INSERT INTO Products (product_name, stock_quantity, price)
VALUES ('Laptop', -5, 999.99);

Output

MySQL will reject the insert and return an error because the CHECK constraint is violated.

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

Similarly, inserting a product with a zero or negative price will violate the named constraint.

INSERT INTO Products (product_name, stock_quantity, price)
VALUES ('Mouse', 100, 0.00);

Output:

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

Adding a CHECK Constraint on Multiple Columns

A CHECK constraint can also reference multiple columns in the same table. This is useful for enforcing rules that depend on the relationship between different fields.

Example

Let’s create a table for Campaigns with a start_date and end_date. We want to ensure the end_date is always after the start_date.

CREATE TABLE Campaigns (
    campaign_id INT AUTO_INCREMENT PRIMARY KEY,
    campaign_name VARCHAR(255) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    CONSTRAINT valid_date_range CHECK (end_date >= start_date)
);

A valid insert will succeed:

INSERT INTO Campaigns (campaign_name, start_date, end_date)
VALUES ('Summer Sale', '2024-06-01', '2024-08-31');
-- Query OK, 1 row affected (0.01 sec)

An invalid insert where end_date is before start_date will be rejected:

INSERT INTO Campaigns (campaign_name, start_date, end_date)
VALUES ('Winter Promo', '2024-12-01', '2024-11-30');
-- ERROR 3819 (HY000): Check constraint 'valid_date_range' is violated.

Adding a CHECK Constraint to an Existing Table

You can add a CHECK constraint to an existing table using the ALTER TABLE statement. When you do this, MySQL checks all existing rows to ensure they comply with the new constraint. If any row fails the check, the operation will fail.

Syntax

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (condition);

Example

Let’s first create a CUSTOMERS table without a constraint.

CREATE TABLE CUSTOMERS(
    ID INT NOT NULL PRIMARY KEY,
    NAME VARCHAR(20) NOT NULL,
    AGE INT NOT NULL
);

Now, let’s add a CHECK constraint to ensure all customers are at least 18 years old.

ALTER TABLE CUSTOMERS
ADD CONSTRAINT check_customer_age CHECK (AGE >= 18);

Dropping a CHECK Constraint

To remove a CHECK constraint, you must first know its name. You can find it in the information_schema.table_constraints table or by using SHOW CREATE TABLE.

Syntax

ALTER TABLE table_name
DROP CHECK constraint_name;

Example

Let’s drop the check_customer_age constraint from the CUSTOMERS table.

ALTER TABLE CUSTOMERS
DROP CHECK check_customer_age;

Advanced: Simulating CHECK with Triggers

Before MySQL 8.0.16, developers used triggers to emulate CHECK constraint functionality. While native constraints are now preferred for their simplicity and performance, triggers are still useful for implementing complex business logic that a CHECK constraint cannot handle (e.g., rules that require querying another table).

Example: Enforcing a Rule with a Trigger

Imagine we have a rule that a customer’s salary cannot be increased by more than 20% in a single update. A CHECK constraint cannot handle this because it cannot compare the old and new state of a row. A trigger is perfect for this.

DELIMITER //

CREATE TRIGGER check_salary_increase
BEFORE UPDATE ON CUSTOMERS
FOR EACH ROW
BEGIN
    -- Compare the new salary with the old one
    IF NEW.SALARY > (OLD.SALARY * 1.20) THEN
        -- Raise an error to prevent the update
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Salary increase cannot exceed 20% at once.';
    END IF;
END;
//

DELIMITER ;

This trigger will intercept UPDATE operations on the CUSTOMERS table and enforce our custom rule.

Handling Constraint Violations in Client Programs

When a CHECK constraint is violated, the database server returns an error. Your application code should be prepared to catch and handle these errors gracefully, for instance, by providing clear feedback to the user.

NodeJS (using mysql2/promise)
Python (using mysql-connector-python)



```javascript
// handleConstraint.js
// Prerequisite: npm install mysql2 dotenv

require('dotenv').config();
const mysql = require('mysql2/promise');

async function testCheckConstraint() {
  let connection;
  try {
    connection = await mysql.createConnection({
        host: process.env.DB_HOST, user: process.env.DB_USER,
        password: process.env.DB_PASSWORD, database: 'test_db'
    });
    console.log('Connected to test_db.');

    // Setup: Table with a CHECK constraint
    await connection.execute(`DROP TABLE IF EXISTS Products`);
    await connection.execute(`
        CREATE TABLE Products (
            product_name VARCHAR(100),
            stock_quantity INT CHECK (stock_quantity >= 0)
        );
    `);
    console.log('Products table created.');

    // Attempt to violate the constraint
    console.log('Attempting to insert invalid record...');
    await connection.execute(
      `INSERT INTO Products (product_name, stock_quantity) VALUES (?, ?)`,
      ['Gadget', -1]
    );

  } catch (err) {
    // MySQL error for check constraint is 3819
    if (err.errno === 3819) {
        console.error('Caught expected error:');
        console.error(`Error: ${err.message}`);
        console.error('This happened because the stock_quantity was negative, violating the CHECK constraint.');
    } else {
        console.error(`An unexpected error occurred: ${err.message}`);
    }
  } finally {
    if (connection) await connection.end();
  }
}

testCheckConstraint();

Expected Output:

Connected to test_db.
Products table created.
Attempting to insert invalid record...
Caught expected error:
Error: Check constraint 'products_chk_1' is violated.
This happened because the stock_quantity was negative, violating the CHECK constraint.

handle_constraint.py

# Prerequisite: pip install mysql-connector-python
import mysql.connector
from mysql.connector import errorcode

def test_check_constraint():
    try:
        # Assuming a database 'test_db' exists
        cnx = mysql.connector.connect(user='root', password='password', host='127.0.0.1', database='test_db')
        cursor = cnx.cursor()
        print("Connected to test_db.")

        # Setup table
        cursor.execute("DROP TABLE IF EXISTS Products")
        cursor.execute("CREATE TABLE Products (price DECIMAL(10,2) CHECK (price > 0))")
        print("Products table created.")

        # Attempt to violate constraint
        print("Attempting to insert invalid record...")
        cursor.execute("INSERT INTO Products (price) VALUES (-50.00)")
        cnx.commit()

    except mysql.connector.Error as err:
        # Error code for CHECK constraint violation is 3819
        if err.errno == 3819:
            print("Caught expected error:")
            print(f"Error: {err.msg}")
            print("This is because the price was not positive.")
        else:
            print(f"An unexpected error occurred: {err}")
    finally:
        if 'cursor' in locals(): cursor.close()
        if 'cnx' in locals() and cnx.is_connected(): cnx.close()

test_check_constraint()

Expected Output:

Connected to test_db.
Products table created.
Attempting to insert invalid record...
Caught expected error:
Error: Check constraint 'products_chk_1' is violated.
This is because the price was not positive.