WL#6929: Move FOREIGN KEY constraints to the global data dictionary
Affects: Server-8.0 — Status: Complete
This worklog is about populating the new data dictionary tables with foreign key information. See WL#6379 for the foreign key table definitions. The scope of this worklog does not include: - Metadata locking for foreign keys - Using new fk dictionary tables for information schema - Using new fk dictionary tables for InnoDB This means that after this WL, while the new dictionary tables will be populated, they won't actually yet be used for anything. Also, for now foreign key definitions will not be updated if the parent changes since we won't lock the child (and thus the FK definitions) when altering the parent. There will still however be some test coverage as the new fk dictionary tables define quite strict constraints that checks the validity of e.g. fk names. It was discussed if the feature request BUG#38882 Allow sloppy type matches in FOREIGN KEY constraints should be implemented as part of this WL. The decision was keep current behaviour.
F-1: Adding a foreign key using CREATE TABLE or ALTER TABLE will cause new rows to be added to mysql.foreign_keys and mysql.foreign_key_column_usage representing the new foreign key. F-2: Removing a foreign key using DROP TABLE or ALTER TABLE will cause matching rows in mysql.foreign_keys and mysql.foreign_key_column_usage to be removed. F-3: Altering a column name in a table which has a foreign key using the column, will cause the foreign key definition in mysql.foreign_keys and mysql.foreign_key_column_usage to be updated with the new column name. F-4: Foreign key names must be unique in a given schema. Trying to create a duplicate name will give ER_FK_DUP_NAME error. Before this WL, ER_FK_DUP_NAME was not reported consistently for duplicate fk names (i.e. other errors could be reported instead). F-5: If foreign key name is not explicitly given by the user, a foreign key name will be generated. For now, this name will match the name generated by InnoDB. This is required to properly support DROP FOREIGN KEY as long as InnoDB still also has FKs stored in their internal dictionary. The format of this name is '(table_name)_ibfk_(counter)'. The counter is 1-based and per table. The number chosen for the counter is 1 higher than the highest number currently in use. If the resulting identifier is too long (>64 chars), ER_TOO_LONG_IDENT error will be reported. Note that this can happen even if the table name is legal (again matching current InnoDB implementation). F-6: Setting the foreign_key_checks system variable to 0 will still disable checks of the parent table when creating foreign keys (e.g. if parent exists). Such checks will be made with foreign_key_checks=1. Similarly, =0 will still allow parent tables to be dropped, while =1 will disallow it. (This WL does not touch the foreign_key_checks implementation) F-7: Foreign key definitions will not be stored for engines that do not support FKs - such as MyISAM. Note however that illegal syntax will still be checked for regardless of engine. F-8: Foreign key name, constraint name, referenced schema name, referenced table name and referenced column name are all limited to 64 characters. Trying to use longer identifiers will give an error. Note that InnoDB currently supports FK name larger than 64 characters, so this is a change of behaviour in that respect in order to get better identifier length consistency. NF-1: None of the constraints defined for mysql.foreign_keys and mysql.foreign_key_column_usage should be violated as a result of any valid SQL statement. The exception is the unique_constraint_id field and any relevant changes to the parent table. See I-3 below.
I-1: The system tables mysql.foreign_keys and mysql.foreign_key_column_usage will be populated properly. Note however that we plan to hide user access to these tables in WL#6391, so the user visible change of this WL is only temporary. I-2: "UNIQUE KEY(foreign_key_id, ordinal_position)" in mysql.foreign_key_column_usage will be changed to PRIMARY KEY as we otherwise have issues retrieving the correct tuple from the dictionary. I-3: The unique_constraint_id field in mysql.foreign_keys will be set to the ID of the same index as currently chosen by InnoDB (which is currently visible in Information Schema). This is done even if this might be a non-unique index in order to keep the current behaviour. If the parent table changes so that the id becomes invalid, the unique_constraint_id field will not be updated. This cannot be fixed until MDL for foreign keys is implemented. The same applies for the referenced_table_name and referenced_column name fields.
The constraint that foreign key names have to be unique for a given schema, creates some difficulties for ALTER TABLE. The problem is that we create temporary, persistent, copies of the table definition. A straight-forward implementation would cause the name constraint to be violated for any pre- existing foreign keys. To solve this issue, existing foreign keys will be omitted in the temporary table definition. They will only be put back once the old copy of the table definition has been deleted (so adding them back won't violate the name constraint). We also need to make sure that the code that handles this, also takes into account dropping of foreign keys and renaming of columns.
Copyright (c) 2000, 2022, Oracle Corporation and/or its affiliates. All rights reserved.