Check Constraint

from the Artful MySQL Tips List


MariaDB before version 10.2 and MySQL before 8.0.16 accepted limited CHECK CONSTRAINT syntax but ignored it. Now CHECK CONSTRAINT works ...

[CONSTRAINT [symbol_name]] CHECK ( expr ) [[NOT] ENFORCED]

The symbol_name must be unique in the schema; MySQL generates a schema-unique name if none is given.

The constraint may be defined for a table or a column. In a table definition, code>expr may refer to any table column(s), but in a column definition, it may not refer to other columns.

The expr must evaluate to True, False or Null, and unless NOT ENFORCED is specified, column values for which expr returns False are rejected.

Allowed in expr: Not allowed in expr: Unless the constraint is NOT ENFORCED, evaluation occurs with Insert, Update, Replace, Load Data and Load Xml. If Ignore is used, failure induces a warning, otherwiose it raises an error. If sql_mode does not include strict, MySQL may auto-adjust for some errors (e.g., truncation for data too long) and issue a warning.

Basic examples here.



Return to the Artful MySQL Tips page