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.
