MySQL 8.0.16 Introducing CHECK constraint

MySQL 8.0.16 introduces the SQL CHECK constraint feature. This is one of the most requested and long awaited features for MySQL. This post describes the details of the feature. Let’s get started!

Introduction

The CHECK constraint is a type of integrity constraint in SQL. The CHECK constraint specifies a search condition to check the value being entered into a row. The constraint is violated if the result of a search condition is FALSE for any row of the table (but not if result is UNKNOWN or TRUE).

In the older versions of MySQL, only a limited syntax to create CHECK constraints is supported, and the constraint is neither created nor evaluated: the constraint definition is ignored.

MySQL 8.0.16 introduces this missing integrity constraint feature.

How to create a CHECK constraint?

To create check constraint the SQL standard syntax

is supported in the column definition and table definition of CREATE TABLE and ALTER TABLE statements.

SHOW CREATE TABLE lists all the check constraints in the table check constraints form.

Supplying a check constraint name is optional. If a constraint name is not supplied then MySQL generates a name for it.

Here t1_chk_1, t1_chk_2, t1_chk_3 are generated names for check constraints.

All check constraints are enforced by default. If the user wants to create a check constraint but does not want to enforce it, then the “NOT ENFORCED” clause is used.

How to add a check constraint to an existing table?

To add a check constraint to an existing table the following clause in ALTER TABLE statement is supported.

How to alter a check constraint enforcement state?

To alter a check constraint enforcement state (enforced / not enforced) the following clause in ALTER TABLE table statement is supported.

How to drop a check constraint?

To drop a check constraint the following clause in ALTER TABLE statement is supported.

What is the effect of CHECK constraint on DMLs?

For DML operations INSERT, UPDATE, REPLACE, LOAD DATA, LOAD XML, the
constraints in the enforced state are evaluated. If a condition is evaluated to FALSE then an error is reported (with the IGNORE clause, a warning is reported and the offending row is skipped).

Where to find information about all the defined CHECK constraints?

The INFORMATION_SCHEMA table “CHECK_CONSTRAINTS” provides information about all the check constraints defined.

In addition, the “INFORMATION_SCHEMA.TABLE_CONSTRAINTS” provides information about all the check constraints defined on the tables.

CHECK condition expression rules

CHECK condition expressions must adhere to the following rules. An error occurs if an expression contains disallowed constructs.

Please check out this new feature and let us know your feedback.

Thanks for using MySQL!

About Praveenkumar Hulakund

Praveenkumar Hulakund has been working with MySQL database team since 2011. He is currently employed by Oracle, based in India, Bangalore. He is a Senior Principal Software Developer for the Runtime Team of the MySQL database. He has a background in the database industry, working for Alcatel Lucent's DataBlitz main memory database in past.

2 thoughts on “MySQL 8.0.16 Introducing CHECK constraint

Leave a Reply

Your email address will not be published. Required fields are marked *