MySQL CHECK Constraint

  1. Overview
  2. MYSQL
  3. MySQL Database
  4. MySQL CHECK Constraint

The CHECK constraint is used to limit the values that can be inserted into a column or set of columns. It ensures that the data meets a specified condition before it is allowed to be entered into the table. This can be used to enforce data integrity by restricting the type or range of values that are valid.

Key Characteristics of the CHECK Constraint:

  • Column-Level or Table-Level: The CHECK constraint can be applied to a single column or to multiple columns in the table.
  • Enforces Data Integrity: It ensures that data entered into the table satisfies specific conditions.
  • Range of Values: It is commonly used to ensure that the values in a column are within a valid range (e.g., ensuring an age column only accepts values greater than or equal to 18).
  • Multiple Conditions: You can combine multiple conditions within a single CHECK constraint.

Using the CHECK Constraint

1. CHECK Constraint on Table Creation

You can define the CHECK constraint when you first create the table. It ensures that only values satisfying the condition are allowed for the column(s).

Example: CHECK Constraint on a Single Column
CREATE TABLE Persons (
    ID INT NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255),
    Age INT,
    CHECK (Age >= 18)
);

In this example:

  • The CHECK constraint ensures that the Age column only accepts values 18 or older.
Example: Named CHECK Constraint on Multiple Columns
CREATE TABLE Persons (
    ID INT NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255),
    Age INT,
    City VARCHAR(255),
    CONSTRAINT CHK_Person CHECK (Age >= 18 AND City = 'Sandnes')
);

In this example:

  • The CHECK constraint is named CHK_Person and ensures that Age must be 18 or older, and City must be 'Sandnes'.

2. CHECK Constraint on Existing Table

You can add a CHECK constraint to an existing table using the ALTER TABLE statement.

Example: Add CHECK Constraint on a Column
ALTER TABLE Persons
ADD CHECK (Age >= 18);

In this example:

  • The CHECK constraint is added to the Age column, ensuring that only values 18 or older can be inserted into the Age column.
Example: Named CHECK Constraint on Multiple Columns
ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age >= 18 AND City = 'Sandnes');

In this example:

  • The CHECK constraint is named CHK_PersonAge and ensures that Age must be 18 or older, and City must be 'Sandnes'.

3. Dropping a CHECK Constraint

If you no longer need a CHECK constraint, you can remove it using the ALTER TABLE statement.

Example: Drop a CHECK Constraint
ALTER TABLE Persons
DROP CHECK CHK_PersonAge;

In this example:

  • The CHECK constraint named CHK_PersonAge is dropped from the Persons table.

Important Notes:

  • Compatibility: While MySQL supports the CHECK constraint, it historically ignored CHECK constraints in earlier versions. Starting with MySQL 8.0.16, MySQL began enforcing CHECK constraints. Make sure to check the version of MySQL you are using.
  • Performance: The CHECK constraint helps ensure data integrity, but excessive use of complex checks can impact performance, especially when the table grows large.
  • Multiple Conditions: The CHECK constraint allows you to define complex conditions, such as ensuring one column's value is greater than another or that a column's value falls within a specific range.

Summary of CHECK Constraint Usage:

  • On Table Creation: The CHECK constraint can be defined when creating a table to enforce specific value limits on columns.
  • On Table Modification: You can add a CHECK constraint to an existing table using the ALTER TABLE statement.
  • Dropping a CHECK Constraint: If no longer needed, the CHECK constraint can be dropped with the ALTER TABLE statement.

The CHECK constraint is a powerful tool for maintaining data integrity by ensuring that the data entered into the table adheres to business rules or requirements.

Was this article helpful?