WL#929: CHECK constraints
Affects: Server-8.0
—
Status: Complete
The aim of the WL is to implement the CHECK constraint. This
WL supports the clause:
[CONSTRAINT [constraint_name]] CHECK (condition) [[NOT] ENFORCED]
in CREATE TABLE and ALTER TABLE statements.
A check constraint is satisfied if and only if the specified
condition evaluates to TRUE or UNKNOWN(for NULL column value)
for row of the table. The constraint is violated otherwise.
For example:
CREATE TABLE t (s1 INT, CHECK (s1 > 0))
INSERT INTO t VALUES (-1)
/* this should fail, the condition is FALSE */
INSERT INTO t VALUES (NULL)
/* this should succeed, the condition is UNKNOWN */
This is an ANSI/ISO requirement, supported by the Oracle, PostgreSQL,
DB2 and SQL Server.
FR1: CREATE TABLE/ ALTER TABLE statement must support column check
clause in the column definition.
column_definition: column_name data_type [NOT NULL | NULL]
[DEFAULT default_value]
...
[check_constraint_definition]
check_constraint_definition: [CONSTRAINT [symbol]] CHECK (condition) [[NOT]
ENFORCED]
Example:
CREATE TABLE t (f1 INT CHECK (f1 > 10));
^^^^^^^^^^^^^^^^^^
CREATE TABLE t (f1 INT CONSTRAINT t1_ck CHECK (f1 > 10));
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
ALTER TABLE t ADD COLUMN f1 INT CHECK (f1 > 10);
^^^^^^^^^^^^^^^^^^
ALTER TABLE t ADD COLUMN f1 INT CONSTRAINT t1_ck CHECK (f1 > 10);
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Currently MySQL parses check constraint in CREATE TABLE/ALTER
TABLE statement but it is ignored.
FR2: CREATE TABLE / ALTER TABLE statement must support check
clause in the table definition.
create_definition:
...
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...) reference_definition
| [CONSTRAINT [symbol]] CHECK (condition) [[NOT] ENFORCED]
alter_specification:
...
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
reference_definition
| ADD [CONSTRAINT [symbol]]
CHECK (condition) [[NOT] ENFORCED]
Example:
CREATE TABLE t1 (f1 INT, f2 INT,
CHECK (f1 < 929),
CONSTRAINT ck_cons CHECK (f1 + f2 <= 929));
^^^^^^^^^^^^^^^^^^^^^^^^^^^
ALTER TABLE t1 ADD CONSTRAINT ck_cons CHECK (f1 + f2 <= 929);
^^^^^^^^^^^^^^^^^^^^^^^^
FR3: Check constraint must be created but not enforced if NOT ENFORCED
clause is used.
If enforcement is not specified or if ENFORCED clause is specified
then check constraint must be created and enforced.
Enforcement of check constraint can be altered using the ALTER
CONSTRAINT syntax. Please refer FR33.
SHOW CREATE TABLE lists the check constraints in not enforced
state too. Please refer FR41.
FR4: CONDITION of the CHECK constraint must support boolean type
expression.
FR5: CONDITION of the CHECK constraint must support forward column
reference.
Example:
CREATE TABLE t1 (CHECK (f1 > 10), f1 INT);
^^^^^^^^^^^^^^^^^^
FR6: CONDITION of CHECK constraint must support multiple column
reference.
Example:
CREATE TABLE t1 (f1 INT, f2 INT, CHECK (f1 + f2 <= 929);
^^^^^^^^^^^^^^^^^
FR7: CHECK constraint must support literal, deterministic builtin
function and operators in the CONDITION.
FR8: CHECK constraint must not support non-deterministic builtin
function in the CONDITION.
FR9: CHECK constraint must not support sub-queries in the CONDITION.
FR10: CHECK constraints must not support variables in the CONDITION.
FR11: CHECK constraints must not support parameters in the CONDITION.
FR12: CHECK constraint must not support stored function in the
CONDITION.
FR13: CHECK constraint must not support user-defined functions in
the CONDITION.
FR14: CHECK constraint must not support expression referencing
AUTO_INCREMENT column.
FR15: CHECK constraint must support reference to the generated
columns in the CONDITION expression.
Example:
CREATE TABLE t (s1 int, s2 int as (s1 * 3), CHECK (s2 < 929));
^^^ ^^^^^^^^^^^^^^^^^
FR16: CHECK constraint expression must be syntactically valid always.
Irrespective of the state(enforced/not enforced) of the constraint,
expression of the CHECK constraint must be syntactically valid.
FR17: CREATE TABLE / ALTER TABLE statement must support option to
specify the check constraint name.
FR18: If constraint name is omitted for the check constraint then name
must be generated by the Server.
FR19: INSERT statement must evaluate check constraint search condition
and report an error if condition is evaluated to FALSE.
FR20: INSERT statement must continue if check constraint search
condition evaluated to TRUE or UNKNOWN (for NULL value).
FR21: UPDATE statement must evaluate check constraint search condition
and report an error if condition is evaluated to FALSE.
FR22: UPDATE statement must continue if check constraint search
condition evaluated to TRUE or UNKNOWN (for NULL value).
FR23: REPLACE statement must evaluate check constraint search condition
and report an error if condition is evaluated to FALSE.
FR24: REPLACE statement must continue if check constraint search
condition evaluated to TRUE or UNKNOWN (for NULL value).
FR25: INSERT IGNORE/UPDATE IGNORE statements must report a warning
if check constraint check condition evaluates to FALSE. In this
row insert to table or update to table row is skipped.
FR26: LOAD DATA INFILE must evaluate check constraint search condition
for each row and report an error if condition is evaluated to
FALSE.
FR27: LOAD DATA INFILE must continue if check constraint search
condition evaluated to TRUE or UNKNOWN (for NULL value).
FR28: LOAD DATA INFILE IGNORE statement must report a warning if
check constraint check condition evaluates to FALSE. In this
case row insert to table is skipped.
FR29: LOAD XML INFILE must evaluate check constraint search condition
for each row and report an error if condition is evaluated to
FALSE.
FR30: LOAD XML INFILE must continue if check constraint search
condition evaluated to TRUE or UNKNOWN (for NULL value).
FR31: LOAD XML INFILE IGNORE statement must report a warning if
check constraint check condition evaluates to FALSE. In this case
row insert to table is skipped.
FR32: ALTER TABLE statement must support a option drop check constraint.
Example:
CREATE TABLE t1 (f1 int, CONSTRAINT ck_cons CHECK (f1 > 10));
ALTER TABLE t1 DROP CHECK symbol;
^^^^^^^^^^^^^^^^^^^
WL uses separate namespace for the check constraint. Similar to
other constraint drop clauses, DROP CHECK (non standard but inline
to our DROP FOREIGN KEY, DROP KEY clause) clause is implemented
by WL to drop the constraint.
FR33: ALTER TABLE statement must support option to alter the check
constraint state.
Following syntax is implemented to support option to alter
the check constraint state.
ALTER TABLE .. ALTER CHECK symbol [NOT] ENFORCED;
Example:
ALTER TABLE t1 ALTER CHECK ck_cons ENFORCED;
ALTER TABLE t1 ALTER CHECK ck_cons [NOT] ENFORCED;
ALTER CHECK is a non-standard clause but inline with DROP CHECK
clause.
FR34: ALTER TABLE statement to RENAME or DROP column must report an
error if column is used in the check constraint.
But if check constraint refers to only one column and it is being
dropped as part of alter operation then check constraint should
also be dropped.
FR35: ALTER TABLE statement to add a AUTO_INCREMENT clause to the
column must report an error if column is used in the check
constraint.
FR36: ALTER TABLE to ADD or ENFORCE check constraints must report an
error if existing rows violated the check constraint.
ALTER TABLE statement does not support WITHOUT VALIDATION
(NO VALIDATION) clause for now.
(WL12802 is opened for this task.)
FR37: ALTER TABLE to modify column used by check constraint must
report an error if check constraint condition is violated.
FR38: ALTER TABLE statement to change data type of column
would report an error if check constraint condition is
violated.
If operands type of check constraints mismatches then MySQL
converts type to make type compatible. If conversion
fails then an error will be reported.
If change in data type happens in such way that precision
is lost then an error will be reported.
FR39: RENAME TABLE statement must update generated name of the
check constraints defined on the table.
FR40: DROP TABLE statement must drop all the check constraints defined
on the table.
FR41: SHOW CREATE TABLE statement must list all the check constraints
defined on the table. Check constraints are listed in the
table check constraints form.
Check constraint in the not enforced state must be listed with the
state in a conditional comment as below,
CONSTRAINT `t1_chk_1` CHECK (`f1` > 10) /*!80015 NOT ENFORCED */
FR42: CREATE TABLE/ALTER TABLE statements with CHECK constraint must
store rows with information about the check constraints in the
data-dictionary tables.
New data-dictionary table is added to store the CHECK constraints
information.
FR43: ALTER TABLE statement to ALTER CHECK constraint state(enforced/
not enforced) must update the state of the check constraint in
the data-dictionary tables.
FR44: ALTER TABLE statement to DROP check constraint must remove
the check constraint information from the data-dictionary tables.
FR45: RENAME TABLE statement must update check constraint information
in data dictionary table when source table is moved between
databases.
FR46: DROP TABLE statement must remove rows about the check constraints
information defined on the tables from the data-dictionary tables.
FR47: INFORMATION_SCHEMA table CHECK_CONSTRAINTS (new table) should
list all the check constraints.
FR48: INFORMATION_SCHEMA.TABLE_CONSTRAINTS should be updated to list
the table check constraints also.
FR49: Check constraints must be checked when applying SQL statements
through the replication applier thread(s).
FR50: Check constraints must be checked when applying row events
through the replication applier thread(s).
FR51: Check constraints must be checked when applying rows through
BINLOG statements.
The WL implements the check constraints feature. The WL supports core
features defined in the ANSI/ISO SQL standard E141-06 "CHECK CONSTRAINTS".
Feature is supported for all storage engines.
A. Check constraint Syntax:
=============================
The check constraint can be either "column check constraint" or "table
check constraint" in CREATE TABLE and ALTER TABLE statements.
Column check constraint immediately follow the column definition in CREATE
and ALTER TABLE statement.
Syntax:
[CONSTRAINT [symbol]] CHECK (CONDITION) [[NOT] ENFORCED]
Example:
CREATE TABLE t (f1 INT CHECK (f1>5));
Column definition syntax change:
column_definition: column_name data_type [NOT NULL | NULL]
[DEFAULT default_value]
...
[check_constraint_definition]
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
check_constraint_definition: [CONSTRAINT [symbol]] CHECK (CONDITION) [[NOT]
ENFORCED]
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
The column check clause condition refers only its column. Other columns
of the table are not allowed in the condition.
Table check clause appears separately in the CREATE TABLE and ALTER TABLE
statement.
Syntax:
[CONSTRAINT [symbol]] CHECK (CONDITION) [[NOT] ENFORCED]
Example:
CREATE TABLE t (f1 INT, CHECK (f1>5));
Statements syntax change:
create_definition:
...
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...) reference_definition
| [CONSTRAINT [symbol]] CHECK (condition) [[NOT] ENFORCED]
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
alter_specification:
...
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
reference_definition
| ADD [CONSTRAINT [symbol]] CHECK (condition) [[NOT] ENFORCED]
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
The condition in table check clause can refer multiple columns
and can have forward reference to the columns
A.1 CONDITION:
----------------
The check constraint condition is a boolean type expression.
Literals, operators and all deterministic builtin functions are
supported in the condition expression.
A.1.a) If operand types in check constraint mismatches then
implicit type conversion occurs (according to MySQL
type conversion rules Section 12.2 "Type conversion
in Expression Evaluation) to make type compatible
in evaluation.
A.1.b) If any component of the expression depends on the SQL
mode then evaluation may result in different results
unless the SQL mode is same during all uses.
Instead of using fixed or sql mode at creating for
each check constraint, WL uses SQL mode at evaluation
time for check constraints. This approach is used
by generated columns and default expressions and it is
accepted by the users. Using same approach by WL will
even help to reuse code generated columns and default
expression evaluation for the check constraints.
A.2 Check constraint naming:
------------------------------
The name for the check constraint can be specified with CHECK
clause. If constraint name is not specified then MySQL generates
name with 3 parts separated by the underscores.
Part 1: Name of the table.
Part 2: '_chk_' (CHECK CONSTRAINT SUBSTR)
Part 3: Ordinal number, minimal value 1, increasing by 1 if a
constraint already has the generated name.
For example:
CREATE TABLE t1 (f1 int CHECK (f1 > 10),
f2 int,
f3 int,
CHECK (f1 <1000),
CHECK (f1 > f2),
CHECK (f1 > f3)) engine=InnoDB;"
Generated constraint names: t1_chk_1 <---- (f1 > 10)
t1_chk_2 <---- (f1 < 1000)
t1_chk_3 <---- (f1 > f2)
t1_chk_4 <---- (f1 > f3)
Note:
a) Check constraints belongs to schema. So no two tables in
the schema can have check constraint with the same name.
According to SQL standards, all constraints should have
same namespace. All the constraints in the schema must be
identified by the unique name. But in MySQL, primary and
unique constraints are not as per the standards (and it is
not something which can be changed easily without breaking
compatibility). Foreign key also uses its own schema
namespace. Hence using separate schema namespace for the
check constraints. Please check FR32.
b) The check constraint name is of NAME_LEN length. The name
might easily cross NAME_LEN size if column name is also
used in the generated names. So to avoid such situation,
generating name with only table name, check constraint
substring and ordinal number.
A.3 [NOT] ENFORCED SYNTAX:
----------------------------
If no enforcement is specified then check constraint is
created in ENFORCED state. [NOT] ENFORCED clause in
can be used to create check constraint in ENFORCED or
NOT ENFORCED state.
B. Metadata Lock for check constraints:
=========================================
To avoid conflicts between the DDL statements on tables using same
check constraint name, MDL locking on check constraint is necessary.
New MDL namespace CHECK_CONSTRAINT is added for the check constraints
names.
C. DATA-DICTIONARY tables:
===========================
The WL introduces new data-dictionary table "check_constraints" to
store the metadata of check constraints.
C.1 TABLE DEFINITION from SQL standards:
-----------------------------------------
CREATE TABLE check_constraints {
CONSTRAINT_CATALOG INFORMATION_SCHEMA.SQL_IDENTIFIER,
CONSTRAINT_SCHEMA INFORMATION_SCHEMA.SQL_IDENTIFIER,
CONSTRAINT_NAME INFORMATION_SCHEMA.SQL_IDENTIFIER,
CHECK_CLAUSE INFORMATION_SCHEMA.CHARACTER_DATA,
}
C.2 MySQL check_constraint Definition:
---------------------------------------
CREATE TABLE mysql.check_constraints {
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
schema_id BIGINT UNSIGNED NOT NULL, /* Reference to schema */
table_id BIGINT UNSIGNED NOT NULL, /* Reference to table */
name VARCHAR(64) NOT NULL COLLATE utf8_tolower_ci,
enforced ENUM('YES', 'NO') NOT NULL, /* constraint state */
check_clause longblob NOT NULL,
check_clause_utf8 LONGTEXT NOT NULL /* clause for I_S */
...
}
Collation utf8_tolower_ci is used for the check constraint names.
Check constraint names are case in-sensitive (and accent sensitive).
Data-dictionary layer is modified to read/write check constraints
metadata. DD object for table holds the collection of check
constraints defined on the table.
Note: i) In the data-dictionary, column check constraints are not
differentiated from the table check constraint. Data-dictionary
table contains a row for each check constraint.
ii) Since we are adding a new table with this WL, DD VERSION will
be incremented.
D. Changes to CREATE TABLE:
=============================
Following changes are done to support creation of check constraints,
D.1 Parser creates list of Sql_check_constraint_spec elements to
represent each check constraint.
Class Sql_check_constraint_spec is introduced to hold the
check constraint specification (Name, expression, state).
D.2 Prepare check constraints specification for the create table,
D.2.1 Generate name if check constraint name is not specified.
D.2.2 Check length of the check constraint name.
D.2.3 Validate check constraint expression
D.2.3.a For column check clause check if expression is
not referring other columns of the table.
D.2.3.b Check if expression is boolean type.
D.2.3.c Perform per-item checks to determine if the
expression is allowed for the check constraint.
D.2.4 Acquire metadata lock on the check constraints of the table.
D.3 While creating DD::Table object, prepare collection of
DD::Check_constraint(DD class to represent check constraint)
instance for each check constraint specification from list of
Sql_check_constraint_spec.
D.4 While opening table from the share, unpack each check constraint
and also check if any non-deterministic builtin functions or
auto_increment columns are used in the expression.
Note: unpack check constraint expression uses the generated
columns and default expression unpack code with minor
adjustments.
D.5 Store check constraint metadata in the data-dictionary with
DD::Table's store.
E. Changes to CREATE TABLE LIKE:
=================================
Following changes are done to support creation of table like other
existing table,
E.1 While creating table like other existing table, if source table
has check constraints.
E.1.1 Prepare list of Sql_check_constraint_spec elements from
the source table check constraints list.
E.1.2 Generate names for all the check constraints.
Note: Same name can not be used if tables belong to same
schema. To keep the case simple, generating names
for all check constraints in CREATE TABLE LIKE.
E.1.3 Acquire MDL lock on check constraint names.
E.2 While creating DD::Table object, prepare collection of
DD::Check_constraint(DD class to represent check constraint)
instance for each check constraint specification from list of
Sql_check_constraint_spec.
E.3 While opening table from the share, unpack each check constraint
and check if any non-deterministic builtin functions or
auto_increment columns are used in the expression.
Note: unpack check constraint expression uses the generated
columns and default expression unpack code with minor
adjustments.
E.4 Store check constraint metadata in the data-dictionary with
DD::Table's store.
F. Changes to ALTER TABLE:
============================
Apart from supporting creation of column check clause with column
add to table and table check clause to add constraint, following
clauses added to the ALTER TABLE statement
F.1 To DROP check constraint:
ALTER TABLE ... DROP CHECK constraint_name;
F.2 To ALTER check constraint:
ALTER TABLE ... ALTER CHECK constraint_name [NOT] ENFORCED;
These are non-standard clauses.
F.4 Changes to ALTER TABLE statement
F.4.1 Parser creates list of
a) Sql_check_constraint_spec elements for the new check
constraints added as column or table check clause.
b) Alter_drop elements with new type CHECK_CONSTRAINT
for DROP check constraint operation.
c) Alter_state elements (New class to represent state
change) with type CHECK_CONSTRAINT for ENFORCED/
NOT ENFORCED check constraint operation.
F.4.2 Prepare check constraints list for alter
a) If table already has any check constraint then prepare
list of Sql_check_constraint_spec from it.
a.1) If table is renamed as part of alter operations
then for the generated check constraint names,
update name with target table name.
b) Append Sql_check_constraint_spec elements for newly
added check constraint.
b.1) Generate name if check constraint name is not
specified.
b.2) Check length of the check constraint name.
b.3) Validate check constraint expression
b.4) For column check clause then check if expression
is not referring other columns of the table.
b.5) Check if expression is boolean type.
b.6) Perform per-item checks to determine if the
expression is allowed for the check constraint.
c) Update state of check constraints from list of
Alter_state elements.
d) Drop check constraints from list of Alter_drop
elements.
e) If any check constraint is using a single column
and if column is dropped as part of the alter operations
then drop check constraint.
f) Acquire MDL lock on check constraints.
f.1) Acquire lock on all the existing check constraints
names of source table.
f.1) If table is renamed then acquire lock on check
constraint names generated with the new table
name.
f.2) If table is moved to other database then acquire
lock on all check constraint names.
g) Check if constraints name do not conflict with the
any of the existing check constraint names before
starting expensive copy/in-place alter operation.
F.4.3 If Alter operation is simple table rename operation then
update generated check constraint names in source table
with target table name.
F.4.4 Allow in-place alter unless there are
F.4.4.1) Operations to create new check constraint.
F.4.4.2) Operations to enforce check constraint.
F.4.4.3) Operations to change column type for the
columns used in check constraint.
F.4.4.4) When property of the column used by check
constraint is changed. (e.g. DEFAULT(f1) is
used in check constraint and default is
changed in alter operation).
In this cases check constraint re-evaluation is needed.
Hence in-place alter is not allowed.
F.4.5 Rename check constraint names with temporary names as
#cc_ in old table version to avoid check
constraint name conflicts and acquire MDL lock on the
temporary constraint name.
F.4.6 When copying table rows, validate all enforced check
constraints on the table row.
CHECK constraint(s) expression can become syntactically invalid with
the operations like,
a) RENAME or DROP columns used by check constraints
b) AUTO_INCREMENT clause added to the column used by the constraint.
In these cases error is reported by the ALTER TABLE statement.
ALTER TABLE will report a check constraint violation error in cases
like,
a) existing row violates the check constraints being added or enforced.
b) modifications to columns used by check constraint (or columns used
by the generated column) violates the check condition.
c) change in data type of column may result in operands type mismatch
in check constraint expression. If conversion fails then error is
reported.
d) change in data type may happen in such way that precision is lost
and check constraint is violated.
e.g. CREATE TABLE t (a INT, b INT, CHECK(a != b);
INSERT INTO t VALUES (2000000000, 2000000001);
ALTER TABLE t CHANGE a a FLOAT, CHANGE b b FLOAT;
# violates the constraint since
# float(2000000000) == float(2000000001)
G. Changes to RENAME TABLE:
=============================
Following changes are done for RENAME TABLE
G.1 Acquire MDL lock on the check constraints name of the source table.
G.2 For all the generated name in source table, generate check
constraint names with destination table name.
G.3 Acquire MDL lock on generated names.
G.3.1) If table is moved to other database then acquire MDL lock
on all check constraint names.
G.4 For all generated names in DD::Table object, update check
constraint name with the one which generated with destination
table name.
G.5 Update check constraint metadata in the data-dictionary table
with DD::Table's update call.
H. Changes to DROP TABLE:
==========================
Following changes are done to DROP TABLE.
H.1 Acquire MDL lock on all the check constraint defined on the
table.
H.2 Drop check constraints collection from DD::Table object in
DD::Table drop call.
I. Changes to DROP DATABASE:
==============================
Following changes are done to DROP DATABASE,
I.1 Acquire MDL lock on the check constraints defined on all
the tables of database.
I.2 Drop check constraints collection from DD::Table object in
DD::Table drop call for each table of a database.
J. Changes to DML operations:
===============================
For DML operations INSERT, UPDATE, REPLACE, LOAD DATA, LOAD XML
operations, check constraints in enforce state are evaluated.If check
constraint is evaluated to FALSE then error is reported (with IGNORE
clause, warning is reported and the offending row is skipped).
Otherwise constraint is evaluated to TRUE or UNKNOWN (for NULL values).
Following changes are done to table open logic,
J.1 Table open operation reads table metadata information from the
data-dictionary table and prepares DD::Table object.
J.2 While preparing TABLE_SHARE from DD::Table object,
J.2.1) Sql_check_constraint_share list is prepared from the
DD::Table's check constraint collection.
Sql_check_constraint_share is a new class added to
represent check constraint in a TABLE_SHARE. Instance of
class holds the information as name, state and check
clause in string form.
J.3 While preparing TABLE instance from TABLE_SHARE in
in open_table_from_share()
J.3.1) Sql_table_check_constraint list is prepared from the
Sql_check_constraint_share list elements.
Sql_table_check_constraint is a new class added to
represent check constraint in a TABLE instance. Instance
of class holds information as name, state, reference to
TABLE instance and expression in unpacked (or materialized
or itemized) from.
J.3.2) To unpack check constraint expression method
unpack_value_generator() of generated column and default
expression is used with the minor adjustments for the
check constraint.
With these steps when table is opened then a TABLE instance is
created with list of Sql_table_check_constraint instance. The unpacked
check constraint expression is evaluated on the row of table.
For DML operations after the assignments of defaults and invoking
BEFORE trigger, check constraints are evaluated. According to standards,
constraints are checked after execution of operation, before invoking
the AFTER trigger. However, thanks to the fact that we do not allow
sub-query and reference to auto-increment column in check constraint,
it is acceptable to replace standard complaint checking of check
constraint with constraint check before carrying out row operation
(after executing BEFORE trigger).
K. FK referential actions behavior:
==========================================
For the foreign key child table with referential actions ON UPDATE CASCADE,
ON DELETE SET NULL, ON UPDATE SET NULL, ON DELETE SET DEFAULT and ON
UPDATE SET DEFAULT on column used in check constraint clause is prohibited.
For example:
CREATE TABLE parent (a INT PRIMARY KEY);
CREATE TABLE child (
b INT,
INDEX(b),
FOREIGN KEY (b) REFERENCES parent(a) ON DELETE SET NULL,
CHECK (b IS NOT NULL)
);
Creation of child table reports an error as FK referential action
refers to column used in the check constraint clause.
Attempt to add constraint on columns which participate in existing
foreign key with such referential action will report error too.
Check constraints are evaluated before write_row() but referential
actions are applied by the storage engine. Check constraints
can not be evaluated for the rows modified by engine with the
referential actions. So the FK referential action on columns used
in check constraints clause is prohibited.
L. Changes to SHOW CREATE TABLE:
==================================
SHOW CREATE TABLE is adjusted to list each check constraint defined
on the table in "table check clause" form.
Check constraints in not enforced state are also listed by the SHOW
CREATE TABLE statement with the conditional comment NOT ENFORCED.
M. INFORMATION_SCHEMA tables:
===============================
New INFORMATION_SCHEMA table CHECK_CONSTRAINTS is added as part of
WL.
CREATE VIEW INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS
SELECT cat.name AS CONSTRAINT_CATALOG,
sch.name AS CONSTRAINT_SCHEMA,
cc.name AS CONSTRAINT_NAME,
cc.check_clause_utf8 AS CHECK_CLAUSE
FROM mysql.check_constraint cc
JOIN mysql.tables tbl ON cc.table_id = tbl.id
JOIN mysql.schemata sch ON cc.schema_id = sch.id
JOIN mysql.catalogs cat ON cat.id = sch.catalog_id
WHERE CAN_ACCESS_TABLE(sch.name, tbl.name) AND
IS_VISIBLE_DD_OBJECT(tbl.hidden);
INFORMATION_SCHEMA.TABLE_CONSTRAINTS is also updated to
M.1) list check constraints defined on the table(s).
M.2) list enforcement information of all constraints with a new
column "ENFORCED". For check constraints, enforcement status
(YES/NO) is listed and for other column value is always "YES".
Note: Since WL adds new INFORMATION_SCHEMA tables and modifies
existing table INFORMATION_SCHEMA version will be incremented.
N. Upgrade:
=============
Upgrade from 5.7 to 8.0 version supporting check constraints
should work without any changes. As check constraint is parsed in 5.7
but no action is performed and no metadata of it is stored in the
data-dictionary.
When upgrading from 8.0.x version without check constraint support to
8.0.y version supporting check constraint, version based handling will
be added as mysql.check_constraint table is a new table in a 8.0.y
and DD code reads/writes to new table. When syncing metadata from
8.0.x data-dictionary mysql.check_constraint is ignored till new
data-dictionary tables are created in the data-dictionary.
O. Replication:
================
For DML events, the check constraints are applied while applying rows
through the BINLOG statements and while applying SQL statements or
row events through the replication applier thread(s) at the slave(s).
O.1 Upgrade:
-------------
While upgrading a replication setup, if DDL event is from the older
server then check constraints in DDL events are ignored (not created)
at the slave(s) with check constraint feature. So replication
upgrade from OLD to NEW server is unaffected.
P. Privileges:
===============
No extra privilege checks are required.
Q. Deferring:
==============
There will be no option to defer checking till end of transaction.
That's a different task.
R. Common Restrictions:
=========================
Following are the common restriction on check constraints,
R.1 No "environmental" variables:
-----------------------------------
WL supports only core features of check constraints. So
environmental variables (as CURRENT_USER, CURRENT_DATE...) are not
supported.
R.2 No sub-queries:
--------------------
WL supports only core features of check constraints. So sub-queries
are not supported in the check constraints.
R.3 No AUTO_INCREMENT columns:
--------------------------------
Evaluation of check clause is done before write_row() but
auto_increment value is determined in write_row() by the engine.
R.4 No variables:
--------------------------------
No user or system variables are supported in the check constraints.
R.5 No parameters:
--------------------
Parameters are not supported in the check constraint.
R.6 No stored function and user defined function:
---------------------------------------------------
Usage of stored function or user defined function is not supported
in the check constraint.
R.7 No Non-deterministic built in functions :s
-----------------------------------------------
Non-deterministic built in functions are not supported in the
check constraint.
e.g connection_id(), random(), now() and so on.
R.8 FK referential action restriction:
----------------------------------------
Foreign key referential actions on columns used in check constraints
is prohibited. Please refer K.
S. Compatibility:
===================
MySQL should be able to claim support for ANSI/ISO "core" (non-optional)
feature E141-06 "CHECK constraints".
It should also be easier for connectors to say that MySQL supports the
"Integrity Enhancement Feature".
Oracle, DB2, SQL Server, PostgreSQL, Informix, and Sybase support
CHECK constraints.
T. References:
================
Feature requests and enquiries:
BUG#3465 Check constraint
BUG#22759 check constraint: missing feature and documentation
"oracle's check constraint - mysql ?"
http://forums.mysql.com/read.php?61,119745,119745
"Check Constraint" http://forums.mysql.com/read.php?100,35344,35344#msg-35344
"Check Constraints in Roadmap?"
http://forums.mysql.com/read.php?60,69472,69472#msg-69472
"CHECK constraint" http://forums.mysql.com/read.php?35,48832,48832s
Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.