-
Dmitry Lenev authored
VALUES FOR FOREIGN KEYS". ALTER TABLE that changes a column definition may lead to changes in values stored in this column. If such a column participates in a foreign key (which is not added by this ALTER TABLE) changes in the column might break referential integrity. Same could have happened when ALTER IGNORE TABLE removed some rows from the table that served as a parent in a foreign key. This has happened because there was no check that for a new column value there is a matching parent key value (or if parent table was changed that such a change doesn't leave child key values as orphans). This patch tries to address the problem for the case when ALTER TABLE is executed using COPY-algorithm. The corresponding problem for the INPLACE algorithm should be handled inside of storage engine calls implementing it. Incompatible changes: 1) We no longer allow dropping or renaming of columns in a foreign key when ALTER is executed using COPY algorithm (Rename is disallowed since we miss an API to communicate information about new names of columns to the InnoDB engine). 2) We do not allow changes to parent and child columns which theoretically might result in value change. "Insignificant" changes that can't result in value change are still allowed. 3) An error is emitted when ALTER IGNORE TABLE tries to delete a row from a table which is a parent in a foreign key. Checks 2-3) are not carried out when @@foreign_key_checks are off (=0). Long-term we can allow "significant" changes to child columns. To implement this we need a storage engine method that will temporarily copy info about affected foreign keys from the old version of table to the new version, so foreign key checks can be performed during copy.
Dmitry Lenev authoredVALUES FOR FOREIGN KEYS". ALTER TABLE that changes a column definition may lead to changes in values stored in this column. If such a column participates in a foreign key (which is not added by this ALTER TABLE) changes in the column might break referential integrity. Same could have happened when ALTER IGNORE TABLE removed some rows from the table that served as a parent in a foreign key. This has happened because there was no check that for a new column value there is a matching parent key value (or if parent table was changed that such a change doesn't leave child key values as orphans). This patch tries to address the problem for the case when ALTER TABLE is executed using COPY-algorithm. The corresponding problem for the INPLACE algorithm should be handled inside of storage engine calls implementing it. Incompatible changes: 1) We no longer allow dropping or renaming of columns in a foreign key when ALTER is executed using COPY algorithm (Rename is disallowed since we miss an API to communicate information about new names of columns to the InnoDB engine). 2) We do not allow changes to parent and child columns which theoretically might result in value change. "Insignificant" changes that can't result in value change are still allowed. 3) An error is emitted when ALTER IGNORE TABLE tries to delete a row from a table which is a parent in a foreign key. Checks 2-3) are not carried out when @@foreign_key_checks are off (=0). Long-term we can allow "significant" changes to child columns. To implement this we need a storage engine method that will temporarily copy info about affected foreign keys from the old version of table to the new version, so foreign key checks can be performed during copy.
Loading