from the Artful MySQL Tips List
MariaDB before version 10.2 and MySQL before 8.0.16 accepted limited
syntax but ignored it. Now
[CONSTRAINT [symbol_name]] CHECK ( expr ) [[NOT] ENFORCED]
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.
must evaluate to True, False or Null, and unless
is specified, column values for which expr returns False are rejected.
- nongenerated and generated columns
- Literals, deterministic built-in functions, operators
Not allowed in
- 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
Unless the constraint is
, 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
does not include
, 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