MySQL Check constraints Tutorial and Examples

In this article, you will learn how to use MySQL CHECK constraints to ensure that the data inserted into a table is correct.

As with any application, the correctness of the data is required. For example, the user’s age must be greater than zero, the user’s login name must not contain spaces, the user’s password must meet a certain complexity, and so on.

For these requirements, although we can validate the data entered by the user in the application interface, this cannot replace the data validation at the database level. This can increase the security of the application.

MySQL provides CHECK constraints to ensure that the data stored in the table meets your requirements. Data that does not meet the CHECK constraints will be rejected.

Note that MySQL didn’t really support CHECK constraints until MySQL 8.0.16. In earlier versions, you could only simulate CHECK constraints through triggers or views with WITH CHECK OPTION.

MySQL CHECK syntax

The following is the syntax of MySQL CHECK:

CHECK(expr)

Here, expr is a boolean expression that evaluates to a row of data. If it returns true, then MySQL allows this row to be inserted into the table, otherwise MySQL rejects this row into the table with an error.

You can create CHECK constraints in a CREATE TABLE statement or add CHECK constraints in a ALTER TABLEstatement statement.

If you use a CHECK constraint in a column definition, the CHECK expression can only refer to this column.

If you are using stand-alone CHECK constraints, the CHECK expression can be applied to all columns on the table.

The following takes the age column needs to be greater than 0 as an example, and uses a different method to add this constraint:

  • Use CHECK constraints in column definition in a CREATE TABLE statement

    CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    age INT NOT NULL
);

  • Use CHECK constraints in the table level in a CREATE TABLE statement

    CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    age INT NOT NULL,
    CONSTRAINT CHECK(age > 0)
);

  • Add CHECK Constraint in a ALTER TABLE statement

ALTER TABLE user
ADD CONSTRAINT CHECK(age > 0);

MySQL CHECK Constraint Examples

Through the following examples, you will easily understand the usage and role of MySQL CHECK constraints.

Suppose, you need a table to store the user’s name, login name, password, and need to meet the following requirements:

  1. The user’s name cannot be null.
  2. The login name must be at least 4 characters.
  3. The password must be at least 8 characters.
  4. The password cannot be the same as the login name.

You can create a table using the following CREATE TABLE statement:

CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(45) NOT NULL,
    login_name VARCHAR(45) NOT NULL CHECK(length(login_name) >= 4),
    password VARCHAR(45) NOT NULL CHECK(length(password) >= 8),
    CONSTRAINT CHECK(login_name <> password)
);

Here, there are 3 CHECK constraints in the CREATE TABLE statement:

  1. In the login_name column definition, CHECK(length(login_name) >= 4) ensures that the length of the login name is not less than 4.
  2. In the password column definition, CHECK(length(password) >= 8) ensures that the length of the login name is not less than 8.
  3. The constraint CONSTRAINT CHECK(login_name <> password) on the table ensures that password cannot be the same as the login name.

You can view constraints on the user table with the following SHOW CREATE TABLE statement:

SHOW CREATE TABLE user\G

*************************** 1\. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `login_name` varchar(45) NOT NULL,
  `password` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `user_chk_1` CHECK ((length(`login_name`) >= 4)),
  CONSTRAINT `user_chk_2` CHECK ((length(`password`) >= 8)),
  CONSTRAINT `user_chk_3` CHECK ((`login_name` <> `password`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

As you can see in the output above, there are 3 CHECK constraints. Constraint names are generated by MySQL by default.

Note that the name column’s NOT NULL is also a special constraint.

To verify that whether the CHECK(length(login_name) >= 4) constraint is in effect, try to insert a row using the following INSERT statement:

INSERT INTO user (name, login_name, password)
VALUES ('Tim', 'tim', 'timisok');

Since tim is less than 4, MySQL gives the following error:

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

To verify that whether the CHECK(length(password) >= 8) constraint is in effect, try inserting a row using the following INSERT statement:

INSERT INTO user (name, login_name, password)
VALUES ('Tim', 'tim1', 'timisok');

Since timisok is less than 8, MySQL gives the following error:

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

To verify that whether the CONSTRAINT CHECK(login_name <> password) constraint is in effect, try inserting a row using the following INSERT statement:

INSERT INTO user (name, login_name, password)
VALUES ('Tim', 'timisgood', 'timisgood');

Since the login and password given in the above statement are both timisgood, MySQL gives the following error:

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

You can use the following statement to insert a row that meets all the CHECK constraints.

INSERT INTO user (name, login_name, password)
VALUES ('Tim', 'hitim', 'timisgood');

The row was successfully inserted into the user table.

Conclusion

MySQL provides CHECK constraints to ensure that the data stored in the table meets your requirements. Rows that do not meet the CHECK constraints are rejected for insertion into the table.

You can add CHECK constraints for a column or a table.