Adding and dropping a foreign key in the same ALTER TABLE  statement is supported for ALTER TABLE ... ALGORITHM=INPLACE  but not for ALTER TABLE ... ALGORITHM=COPY . 

The server prohibits changes to foreign key columns that have the potential to cause loss of referential integrity. A workaround is to use ALTER TABLE ... DROP FOREIGN KEY  before changing the column definition and ALTER TABLE ... ADD FOREIGN KEY  afterward. Examples of prohibited changes include: 

•Changes to the data type of foreign key columns that may be unsafe. For example, changing VARCHAR(20)  to VARCHAR(30)  is permitted, but changing it to VARCHAR(1024)  is not because that alters the number of length bytes required to store individual values. 

•Changing a NULL  column to NOT NULL  in non-strict mode is prohibited to prevent converting NULL values to default non- NULL  values, for which there are no corresponding values in the referenced table. The operation is permitted in strict mode, but an error is returned if any such conversion is required. 

ALTER TABLE tbl_name  RENAME new_tbl_name  changes internally generated foreign key constraint names and user-defined foreign key constraint names that begin with the string “ tbl_name _ibfk_”  to reflect the new table name. InnoDB  interprets foreign key constraint names that begin with the string “ tbl_name _ibfk_”  as internally generated names. 

Prior to MySQL 8.0.16, ALTER TABLE  permits only the following limited version of CHECK  constraint- adding syntax, which is parsed and ignored: 

ADD CHECK ( expr ) 

As of MySQL 8.0.16, ALTER TABLE  permits CHECK  constraints for existing tables to be added, dropped, or altered: 

•Add a new CHECK  constraint: 

ALTER TABLE tbl_name ADD CONSTRAINT [ symbol ] CHECK ( expr ) [[NOT] ENFORCED]; 

The meaning of constraint syntax elements is the same as for CREATE TABLE . See Section 13.1.20.6, “CHECK Constraints” . 

•Drop an existing CHECK  constraint named symbol : 

ALTER TABLE tbl_name DROP CHECK symbol ; 

•Alter whether an existing CHECK  constraint named symbol  is enforced: 

ALTER TABLE tbl_name ALTER CHECK symbol  [NOT] ENFORCED; 

The DROP CHECK  and ALTER CHECK  clauses are MySQL extensions to standard SQL. 

As of MySQL 8.0.19, ALTER TABLE  permits more general (and SQL standard) syntax for dropping and altering existing constraints of any type, where the constraint type is determined from the constraint name: 

•Drop an existing constraint named symbol : 

ALTER TABLE tbl_name DROP CONSTRAINT symbol ; 

If the sql_require_primary_key  system variable is enabled, attempting to drop a primary key produces an error. 

