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

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.