Limit Column Values With The MySQL CHECK Constraint [Examples]

This article will demonstrate the usage of the SQL CHECK Constraint to limit column values, as used in MySQL/MariaDB.

The SQL CHECK constraint allows you to define limitations to the value which can appear in a table column.

It also allows you to apply constraints to a whole table, letting you restrict a column’s values based on the values of other columns in the table row.

This behavior is useful for making sure you are only getting valid data inserted into your database.

For example, you might have a database that you use to store the details for apartments in an apartment building (number of rooms, number of windows, etc.). Unluckily for the ground floor tenants, there are no windows on the ground floor, and ground floor flats never have more than 2 rooms.

Your database could ensure that no value other than 0 can be entered for the window count and that no more than 2 rooms can be entered into the details on ground floor apartments, enforcing data validity.

Implementing a SQL CHECK Constraint when Creating a Table

Below, a table for apartments is created. This building has no basement, so a CHECK constraint is added, which enforces a minimum value of 0 for the floor column:

CREATE TABLE apartments (
    id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    floor INT NOT NULL,
    room_count INT NOT NULL,
    window_count INT NOT NULL,
    CHECK (floor>=0)
);

Multiple Columns/Naming CHECK Constraint

If you wish to add a CHECK constraint that involves multiple columns, you must identify the constraint with a name.

The below example adds a constraint called check_windows which ensures that ground floor (floor 0) apartments have no windows:

CREATE TABLE apartments (
    id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    floor INT NOT NULL,
    room_count INT NOT NULL,
    window_count INT NOT NULL,
    CONSTRAINT check_windows CHECK (floor_count=0 AND window_count=0)
);

Named constraints can be as complicated as you like, including boolean comparisons to add constraints that enforce different requirements based on the value of other columns.

Adding a SQL CHECK Constraint to an Existing Table

You can add a check to an existing table. Below, a check to ensure the floor column in the apartments table is greater than 0 is added:

ALTER TABLE apartments
ADD CHECK (floor>=0);

Multiple Columns/Naming CHECK Constraint

Named constraints can also be added to existing tables:

ALTER TABLE apartments
ADD CONSTRAINT check_windows CHECK (floor_count=0 AND window_count=0)

Removing a CHECK Constraint from a Table

Dropping a named check constraint named check_valid:

ALTER TABLE apartments
DROP CHECK check_valid;

There is no built-in way to drop unnamed CHECK constraints, so it’s worth giving them a name! Otherwise, the easiest method is to create a new table without the constraint, copy your data into the new table, and remove the old table.