Score:0

How to remove orphaned foreign key constraint in MySQL?

in flag

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.

djdomi avatar
za flag
Mauro I'm sorry for you but dba is the site for Db questions. flag your question for movement via community reason and select belongs to a other site, there you select dba and your question will be moved shortly after
ua flag
Please provide `SHOW CREATE TABLE SOURCE_TABLE;` and `SHOW CREATE TABLE other_table;`
Mauro Molinari avatar
in flag
@djdomi I'm a bit confused, honestly. I find many questions regarding MySQL here on serverfault and even on stackoverflow. So why should my question go to dba instead?
Mauro Molinari avatar
in flag
@RickJames I just added the relevant output for SHOW CREATE TABLE statements. If your doubt was about the column types, they are matching.
ua flag
`SELECT constraint_name FROM information_schema.referential_constraits WHERE constraint_schema = "my_schema";`
Score:0
in flag

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...

djdomi avatar
za flag
that's why I said, dba would be your source of solution ;)
mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.