WL#12798: Implement ALTER TABLE ... DROP/ALTER CONSTRAINT syntax.

Affects: Server-8.0   —   Status: Complete

Currently MySQL supports only non-SQL standard syntax to DROP and     
ALTER table constraints.                                              
                                                                      
Goal of this WL is to support SQL standard syntax,                    
                                                                      
  "ALTER TABLE tbl_name DROP CONSTRAINT symbol"                       
                                                                      
to drop constraint and                                                
                                                                      
  "ALTER TABLE tbl_name ALTER CONSTRAINT symbol [NOT] ENFORCED"       
                                                                      
to alter constraint, where type of a constraint is automatically      
determined from the constraint name.                                  
                                                                      
However, the latter doesn't mean that we will support disabling       
constraints of types other than CHECK constraint. The plan is to only 
support generic syntax at this point.

Following bug is fixed by this WL:
BUG#3742 Foreign keys: DROP CONSTRAINT as a compatibility alias for DROP FOREIGN 
KEY
FR1 : ALTER TABLE statement must support DROP CONSTRAINT clause in the
      table definition.

      alter_specification:
       ...
       DROP CONSTRAINT symbol

      Example:
        ALTER TABLE t1 DROP CONSTRAINT `primary`;
                      ^^^^^^^^^^^^^^^^^^


FR2 : DROP CONSTRAINT must support drop operation for all table
      constraint types(PRIMARY, UNIQUE, FOREIGN KEY and CHECK).

      When sql_require_primary_key=ON, attempt to drop primary
      key fails with an error (Behavior is similar to DROP
      PRIMARY KEY operation).



FR3 : ALTER TABLE statement must support ALTER CONSTRAINT clause in
      the table definition.

      alter_specification:
       ...
       ALTER CONSTRAINT symbol [NOT] ENFORCED

      Example:
        ALTER TABLE t1 ALTER CONSTRAINT t1_chk_1 NOT ENFORCED;
                      ^^^^^^^^^^^^^^^^^^


FR4 : ALTER CONSTRAINT must support altering enforcement state of
      table constraints.

      With the introduction of check constraints feature, non-standard
      clause "ALTER CHECK  [NOT] ENFORCED" is supported in
      the ALTER TABLE statement. As part of this WL, only SQL standard
      clause will be supported to alter enforcement state. But behavior
      still remains the same, i.e. only check constraint's enforcement
      state can be altered and all other constraints are in *enforced*
      state always.


FR5 : If enforcement state alter is not supported for a table constraint
      type then an error must be reported.


FR6 : If more than one table constraints are defined with same name
      then DROP/ALTER CONSTRAINT operation must report an error.
      (Please refer Section A in HLS)


FR7 : If no constraint is found with specified name then DROP/ALTER
      CONSTRAINT operation must report an error.
To drop a table constraint currently following clauses are supported
in the ALTER TABLE statement,

  PRIMARY KEY CONSTRAINT:
    ALTER TABLE tbl_name DROP PRIMARY KEY;

  UNIQUE CONSTRAINT:
    ALTER TABLE tbl_name DROP {INDEX/KEY} index_name;

  FOREIGN KEY CONSTRAINT:
    ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

  CHECK CONSTRAINT:
    ALTER TABLE tbl_name DROP CHECK symbol;

To alter a constraint currently following clause is supported in the
ALTER TABLE statement,

  CHECK CONSTRAINT:
    ALTER TABLE tbl_name ALTER CHECK symbol [NOT] ENFORCED;

Currently enforcement state of only CHECK constraint can be altered.
All other table constraints are always in enforcement state.

Aim of this WL is to support SQL standard syntax

    ALTER TABLE  DROP CONSTRAINT ;

to drop constraint and

    ALTER TABLE  ALTER CONSTRAINT  [NOT] ENFORCED;

to alter constraints, where type of the constraint is automatically
determined from the name.  

However, the latter doesn't mean that we will support disabling
constraints of types other than CHECK constraint. The plan is to only
support generic syntax at this point.


A. Syntax changes:
------------------
  The ALTER TABLE statement is extended in the following way to
  support new clauses,

  A.1 For DROP CONSTRAINT clause:

      alter_specification:
       ...
       DROP CONSTRAINT symbol

      Example:
        ALTER TABLE t1 DROP CONSTRAINT `primary`;
                      ^^^^^^^^^^^^^^^^^^

  A.2 For ALTER CONSTRAINT clause:

      alter_specification:
       ...
       ALTER CONSTRAINT symbol [NOT] ENFORCED

      Example:
        ALTER TABLE t1 ALTER CONSTRAINT t1_chk_1 NOT ENFORCED;
                      ^^^^^^^^^^^^^^^^^^


B. Constraint Namespace:
------------------------
   The SQL standard specifies that all type of constraints (PRIMARY,
   UNIQUE, FOREIGN KEY and CHECK CONSTRAINTS) belongs to the same
   namespace. In MySQL, each constraint type has a separate namespace.

   B.1 Primary Constraint and Unique constraint:
        Primary and Unique constraint shares namespace with the index/key
        namespace. Namespace of primary and unique constraint is per
        table. Name "primary" is reserved for the primary
        constraint(/index) in this namespace. Unique constraints or
        indexes can not have "primary" as a name.

   B.2 Foreign Key: 
        Foreign key has its own namespace per schema.

   B.3 Check constraint:
        Check constraint has its own namespace for schema.

   Constraints of different types can have same name because of separate
   namespaces.

   Such ambiguous situation are handled by emitting a error, forcing
   user to resort to constraint specific DROP/ALTER syntax.


C. Details of changes to ALTER TABLE implementation:
----------------------------------------------------
Following changes are done to support DROP/ALTER CONSTRAINT clause
in the ALTER TABLE statement.

  C.1 Parser creates list of
      a) Alter_drop elements with new type CONSTRAINT for DROP
         constraint operation.
      b) Alter_state elements with new type CONSTRAINT for ALTER
         constraint operation.

  C.2 In the prepare phase of ALTER TABLE, i.e. before preparing
      check constraints list and keys for operation, resolve type
      of constraint.
      C.2.1 Find constraint by name in
              1. KEY list available with TABLE instance.
              2. Foreign key list dd::Table instance.
              3. Check constraint list available with TABLE instance.

      C.2.2 Report an error if no constraint is found.                  

      C.2.3 If more than one constraint is found then report an
            error.

      C.2.4 For drop operation, update resolved type in Alter_drop
            element.
      
      C.2.5 For alter operation, if type is not a check constraint
            then report an error.

      C.2.6 For alter operation, update resolve type in Alter_state
            element.

      C.2.7 Continue to prepare check constraints, keys step.

Original Alter_drop and Alter_element is *not* modified to make changes
safe for re-execution of PS/routine.


D. Replication:
---------------
    Replication upgrade from OLD to NEW server is unaffected. Replication
    from NEW to OLD is not supported for DDL statements using new syntax.


E. Upgrade:
-----------
    No changes to data-dictionary and INFORMATION_SCHEMA. Upgrade is
    unaffected. Since WL does not change semantics of any pre-existing
    statements, such upgrade should not affect any user application
    either.