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