|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]
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. A table
CHECK CONSTRAINT is one that is not within a column definition; in it,
expr may refer to any table column(s). A column
CHECK CONSTRAINT occurs within a column definition and may not refer to other columns.
expr must evaluate to True, False or Null, and unless
NOT ENFORCED is specified, column values for which expr returns False are rejected.
Not allowed in
- nongenerated and generated columns
- Literals, deterministic built-in functions, operators
Unless the constraint is
- stored routine parameters, system variables, user-defined variables, stored program local variables
- stored and user-defined functions
- On Update and On Delete clauses
- columns referenced in foreign keys and columns of other table
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.
CHECK CONSTRAINT cannot be coded as a table constraint, a convenient workaround is to write it as a Trigger on the relevant column, and have the Trigger invoke SIGNAL. For an example see "Prevent cycles in trees" under "Graphs and hierarchies".
Last updated 7 May 2022