Currently, only the InnoDB storage engine is storing information about FOREIGN KEY constraints. The metadata is stored in two hidden tables that are only accessible to InnoDB. The tables are created in the InnoDB internal SQL parser as follows:

CREATE TABLE SYS_FOREIGN(ID CHAR, FOR_NAME CHAR, REF_NAME CHAR, N_COLS INT);

CREATE UNIQUE CLUSTERED INDEX ID_IND ON SYS_FOREIGN (ID);

CREATE INDEX FOR_IND ON SYS_FOREIGN (FOR_NAME);

CREATE INDEX REF_IND ON SYS_FOREIGN (REF_NAME);

CREATE TABLE SYS_FOREIGN_COLS(ID CHAR, POS INT, FOR_COL_NAME CHAR, REF_COL_NAME CHAR);

CREATE UNIQUE CLUSTERED INDEX ID_IND ON SYS_FOREIGN_COLS (ID, POS);

It is a bad idea to identify the tables and columns by names, because it forces InnoDB to update these tables when tables or columns are renamed. Furthermore, the names are stored and sorted as if they were encoded in latin1_swedish_ci, while they should really have been handled as utf8_unicode_ci (system_charset_info).

The SYS_FOREIGN.ID consists of the schema name and a user-specified constraint name. If no constraint name is specified by the user, then it will be generated like childtablename_ibfk_1, childtablename_ibfk_2, and so on. The ID must be unique within the database.

In main memory, InnoDB stores the foreign key metadata in a data structure dict_foreign_t. This structure stores both table names and table pointers. Tables that are attached to foreign key constraints cannot be evicted from the InnoDB table cache. The reason for this is that whenever a table that is attached to a foreign key constraint is modified, we may have to read or modify records in other tables.

As part of this task, the FOREIGN KEY metadata would be stored in the SQL layer. A natural place could be the .frm file. Alas, if we only stored the information in the .frm file of one of the tables that are part of the constraint, then we would have to read all .frm files before any table can be modified, because we must be aware of all tables that the current table REFERENCES to or that are pointing to the current table by REFERENCES clauses.

If we stored the FOREIGN KEY metadata in the .frm files of both the child and the parent tables, then RENAME TABLE would have to update a large number of .frm files in an atomic and crash-safe fashion. This would require transactional logging, which is currently missing.

Proposal: Store FOREIGN KEY Constraints in .frk Files

It would seem simplest to introduce a new type of files for storing FOREIGN KEY constraints. All these files would be read at startup, and all .frm files pointed to by them would be opened. If there is a mismatch (say, one or both of the tables does not exist, or column names or types do not match, or the columns are not covered by appropriate indexes), a warning would be issued to the server error log, and the constraint would be ignored.

Columns that are part of the constraint could be identified either by name or by position in the SHOW CREATE TABLE output. Position could be preferred, because the .frk files would not have to be updated when renaming columns. But, then they would have to be updated when dropping, adding or reordering columns.

Updates of the .frk files must not only be crash-safe, but transactional and atomic together with updates of .frm files and the storage engine data. This will require some form of logging that is synchronized with InnoDB transaction commit, also when it happens inside ha_innobase::commit_inplace_alter_table().

Option: constraintname.frk (one REFERENCES clause per file)

Each constraintname.frk file would have to contain both the child and parent table names, and identify the participating columns.

We would potentially have a very large number of files, and many of them would have to be atomically and transactionally updated in RENAME TABLE.

Option: childtablename.frk (multiple REFERENCES clauses per file)

The child table name would be implied by the file name; only the parent table names would be stored in the file, along with the identities of the columns. The uniqueness of constraint identifiers would be enforced in memory only. (We have to keep all foreign key constraints in memory anyway.)

RENAME TABLE of a child table would only have to transactionally rename childtablename.frk and childtablename.frm. Renaming a parent table will have to transactionally rewrite the childtablename.frk to update the REFERENCES clauses.

With childtablename.frk files (and assuming that the InnoDB internal data dictionary will some day be removed in MDEV-11655), users could manually rename child tables by renaming files, but renaming parent tables would still require editing the .frk file. We might consider using an editing-friendly file format. In that case, the transactional logging and crash recovery for .frk file operations must be made as robust as possible.