StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow do I fix a MySQL ghost foreign key constraint?
text
Body
copied!<p>I had an InnoDB table called Things. This table had foreign key constraints on Field1, Field2 and MoarFields.</p> <p>I renamed this table to <code>things</code>, and renamed the fields to <code>field1</code>, <code>field2</code>, and <code>moar_fields</code> (all lowercased).</p> <p>At this point, all my foreign keys to the newly named table <code>things</code> are suddenly gone. This is fine, I didn't want them either way, but I didn't actually delete them.</p> <p>Then I tried modifying a field name in a different table, which also needed to be lowercase, but it complains about a foreign key constraint preventing this.</p> <pre><code>ALTER TABLE `member` CHANGE COLUMN `MemberID` `member_id` INT(11) NOT NULL AUTO_INCREMENT FIRST; /* SQL Error (1025): Error on rename of './database/#sql-411_3e7' to './database/member' (errno: 150) Foreign key constraint is incorrectly formed */ </code></pre> <p><code>SHOW ENGINE INNODB STATUS</code> tells me the problem is:</p> <pre><code>121001 12:02:12 Error in foreign key constraint of table database/Things: there is no index in referenced table which would contain the columns as the first columns, or the data types in the referenced table do not match the ones in table. Constraint: , CONSTRAINT "FK_MemberRole_Member" FOREIGN KEY ("MemberID") REFERENCES "member" ("MemberID") ON DELETE CASCADE InnoDB: Renaming table `database`.`#sql-411_3e7` to `database`.`member` failed! </code></pre> <p>If I do <code>show create table things</code>, I do not see any foreign keys. Though I do see 4 normal keys with the name of my foreign keys.</p> <p>If I take a look at the <code>schema_information</code> database however, I can see that InnoDB has 4 keys stored there, for a table that does no longer exists, namely <code>Things</code>. Those are probably the problem.</p> <p>How do I remove those ghost keys? I could recreate the database and copy just the data, but it's quite large, so if possible, I'd rather not do that.</p> <p>Please let me know if you want/need any information.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload