How to remove orphaned foreign key constraint in MySQL?

Question

Using MySQL 5.5. I have a table for which I can't add a foreign key:

ALTER TABLE `SOURCE_TABLE` 
    ADD CONSTRAINT `ConstraintFK`
        FOREIGN KEY (`otherTableID`)
        REFERENCES `OTHER_TABLE` (`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE;

MySQL returns the following error:

Error Code: 1005. Can't create table 'my_schema.#sql-4c0c_b6fc8ca' (errno: 121)

Looking at SHOW ENGINE INNODB STATUS I get:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
220523 16:34:36 Error in foreign key constraint creation for table `my_schema`.`#sql-4c0c_b6fc8ca`.
A foreign key constraint of name `my_schema`.`ConstraintFK`
already exists. (Note that internally InnoDB adds 'databasename'
in front of the user-defined constraint name.)
Note that InnoDB's FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.

Of course, there's no constraint named ConstraintFK defined in this schema, I checked both the information schema and the SHOW CREATE TABLE SOURCE_TABLE output. The latter shows that the index for the foreign key exists, but the foreign key constraint seems not to be there:

-- only relevant info shown
CREATE TABLE `SOURCE_TABLE` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `otherTableID` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ConstraintFK_idx` (`otherTableID`)
) ENGINE=InnoDB AUTO_INCREMENT=4089 DEFAULT CHARSET=utf8;

CREATE TABLE `OTHER_TABLE` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=58108 DEFAULT CHARSET=utf8;

Indeed, if I try to drop that constraint:

ALTER TABLE `SOURCE_TABLE`
   DROP FOREIGN KEY `ConstraintFK`;

I get:

Error Code: 1025. Error on rename of './my_schema/SOURCE_TABLE' to './my_schema/#sql2-4c0c-b6fc8ca' (errno: 152)

I looked at the file system and I see no reasons for which renaming of the table should fail.

Unfortunately, trying to query the information schema does not help:

SELECT * FROM information_schema.TABLE_CONSTRAINTS 
    WHERE information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY' 
    AND information_schema.TABLE_CONSTRAINTS.CONSTRAINT_NAME = 'ConstraintFK';

returns an empty set, while:

SELECT * FROM information_schema.TABLE_CONSTRAINTS 
    WHERE information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY' 
    AND information_schema.TABLE_CONSTRAINTS.CONSTRAINT_SCHEMA = 'my_schema';

returns all the foreign key constraints in my_schema that I can also get from SHOW CREATE TABLE statements, but no sign of ConstraintFK...

Looking at this and this, I suspect that something occurred in the past that caused that foreign key to be orphaned: indeed, this SOURCE_TABLE was renamed some time ago and I'm pretty sure the foreign key I'm trying to add was there in the past. The suggested workaround is to drop the schema and re-create from a dump file. Is there anything else I can try which does not involve dropping this schema? It's quite huge and the downtime would be relevant.

Answer 1

In the end, I was forced to dump the whole schema and restore it with another name, then drop the schema with the old name (and containing the problematic/corrupted SOURCE_TABLE).

Indeed, I found this answer from dba.stackexchange.com which describes a way that might probably solve this and indeed it seemed to work... until a certain point:

• recreate the table SOURCE_TABLE with the EXACT SAME structure but the name it had before renaming; ensure to also specify the foreign keys (otherwise MySQL will return error 150) but with a different name (otherwise MySQL will return error 121). Also make sure index names reflect the new names of the foreign key constraints. So, in my example, the re-created table with the old name must have the same foreign key (as described above) but with a different name (say ConstrFK instead of ConstraintFK); also the index must be named ConstrFK_idx (otherwise you'll get error 121 again)
• if all the above is satisfied, MySQL should allow you to create the table with the old name
• then you can drop the table just created
• then go back to SOURCE_TABLE and retry to create the ConstraintFK as desired... now it should work

In my case, however, with MySQL 5.5.62 (Percona Server), the DBMS crashed completely in the last step, some instants after I saw that the creation of the foreign key was indeed successful. On restart (after crash recovery finished), the table was in an inconsistent state (it made MySQL Workbench crash, while my applications seemed to complain that SOURCE_TABLE didn't exist any more). So, to get out from that terrible situation, I was indeed forced to dump the whole schema (I worked on an up-to-date replica, which had the SOURCE_TABLE still intact), re-import as a new schema, then drop the schema with the problematic table and get rid of all of this headache... It of course required at least a couple of hours of downtime...