MariaDB Check Constraint

Summary: in this tutorial, you will learn how to use the MariaDB check constraint to enforce domain integrity.

Introduction to MariaDB check constraint

A check constraint checks a value in a column or group of columns before inserting or updating to make sure that this value satisfies a Boolean expression.

See the following classes table:

create table classes(
    class_id int auto_increment,
    class_name varchar(255) not null,
    student_count int check(student_count >0),
    primary key(class_id)
);
Code language: SQL (Structured Query Language) (sql)

In this classes table, the data type of the student_count column is integer. To ensure the number of students is positive, we added the following check constraint:

check(student_count > 0)
Code language: SQL (Structured Query Language) (sql)

The check constraint appears in the column definition after the data type. It consists of the check keyword followed by a Boolean expression in parentheses.

Once the check constraint is in place, you cannot insert or update a value to a column that causes the condition evaluates to false.

The general syntax for the check constraint is:

column_name datatype check(expression)
Code language: SQL (Structured Query Language) (sql)

If you create a check constraint without a name, MariaDB will automatically generate a name for it. However, you can explicitly specify a name for the check constraint by using the constraint clause as follows:

drop table classes;

create table classes(
    class_id int auto_increment,
    class_name varchar(255) not null,
    student_count int 
        constraint positive_student_count 
        check(student_count >0),
    primary key(class_id)
);
Code language: SQL (Structured Query Language) (sql)

In this case, the name of the constraint is positive_student_count. If you assign a check constraint a specific name, it is easier to refer to it when you want to drop the constraint.

The following shows the syntax of the check constraint with a constraint name:

column_name datatype 
    constraint constraint_name 
    check(expression)
Code language: SQL (Structured Query Language) (sql)

When you place the check constraint in the column definition, the check constraint is a column constraint. It is possible to define a check constraint as a table constraint:

drop table classes;

create table classes(
    class_id int auto_increment,
    class_name varchar(255) not null,
    student_count int,
    constraint positive_student_count 
        check(student_count >0),
    primary key(class_id)
);
Code language: SQL (Structured Query Language) (sql)

In this case, the check constraint appears after all the column definitions.

The following statement inserts a row into the classes table with the value of zero in the student_count column.

insert into classes(class_name, student_count)
values('MariaDB for Developers',0);
Code language: SQL (Structured Query Language) (sql)

MariaDB issued the following error:

SQL Error (4025): CONSTRAINT `positive_student_count` failed for `nation`.`classes`
Code language: SQL (Structured Query Language) (sql)

The following statement works because the value in the student_count column satisfies the Boolean expression in the check constraint.

insert into classes(class_name, student_count)
values('MariaDB for Developers',100);
Code language: SQL (Structured Query Language) (sql)

Defining a check constraint that references to multiple columns

A check constraint can refer to multiple columns. In this case, you must define the check constraint as a table constraint.

See the following new classes table:

drop table classes;

create table classes(
    class_id int auto_increment,
    class_name varchar(100) not null,
    begin_date date not null,
    end_date date not null,
    student_count int,
    constraint positive_student_count 
        check(student_count >0),
    constraint valid_date
        check(end_date >= begin_date),
    primary key(class_id)
);
Code language: SQL (Structured Query Language) (sql)

In this example, the valid_date check constraint specifies that the end date must be the same or later than the beginning date:

constraint valid_date
    check(end_date >= begin_date)
Code language: SQL (Structured Query Language) (sql)

Adding check constraints to an existing table

The alter table add constraint statement allows you to add a check constraint to an existing table:

alter table table_name
add constraint constraint_name 
check(expression);
Code language: SQL (Structured Query Language) (sql)

For example, the following example adds a check constraint to the classes table:

alter table classes
add constraint valid_begin_date 
check(begin_date >= '2019-01-01');
Code language: SQL (Structured Query Language) (sql)

Remove check constraints

To remove a check constraint from a table, you use the alter table drop constraint statement:

alter table table_name
drop constraint constraint_name;
Code language: SQL (Structured Query Language) (sql)

The following example drops the valid_begin_date constraint:

alter table classes
drop constraint valid_begin_date;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the MariaDB check constraint to limit the values that can be inserted or updated to one or more columns in a table.