Per-table unique FOREIGN KEY constraint names

Starting with MySQL 4.0.18, InnoDB can store user specified FOREIGN KEY constraint names. But, for no good reason, these names must be unique within a schema. Let us allow the constraint names to be unique within a table.

Furthermore, let us make the generated FOREIGN KEY constraint names be simply ASCII numbers 1, 2, 3, and so on. Previously, the user-visible generated names were tablename_ibfk_1, tablename_ibfk_2 and so on, while the internal names were like schemaname/tablename_ibfk_1. In the new scheme, the internal names that are only visible in the InnoDB system tables SYS_FOREIGN and SYS_FOREIGN_COLS are prefixed with schemaname/tablename and the impossible UTF-8 sequence 0xff. This prefix is hidden from the SQL layer.

Upgrade considerations

• Foreign key constraints will no longer be displayed as schemaname/constraintname to the users. Only the constraintname part will be displayed.
• On ALTER TABLE…ALGORITHM=COPY, all internal names of constraints will be adjusted: the schenamame/ prefix will be changed to schemaname/tablename\377.
• The user-visible part of any constraints of the form oldtablename_ibfk_1 will no longer be changed on RENAME TABLE oldtablename TO newtablename.
  • The internal names will be changed, e.g. from schemaname/oldtablename_ibfk_1 to schemaname/newtablename\377oldtablename_ibfk_1.
• ALTER TABLE…DROP FOREIGN KEY will attempt to drop both old and new format constraint names.

Downgrade considerations

• Foreign key constraints are expected to work, but the error messages may display an incorrect constraint name.
  • If the invalid UTF-8 sequence 0xff is being treated as a string terminator, the constraint name would not be reported as schemaname/constraintname but schemaname/tablename.
• Both DROP TABLE and DROP DATABASE will be able to drop the associated FOREIGN KEY constraints.
  • This can be tested by running ./mtr --manual-gdb innodb.innodb-fk with two versions of the server.
• ALTER TABLE…DROP FOREIGN KEY will be unable to drop constraints that are in the new format, because it will be impossible to input the invalid UTF-8 sequence 0xff as part of the constraint name.