MySQL complains key exists but I can't find it

I have a large (in both schema and data) MySQL database with lots of foreign key constrains. Recently I have discovered that some script can not create a table because key with given name already exists.

I traced down the problem to the following:

If I run something like this:

CREATE TABLE `foo` (
  `bar` int UNSIGNED NOT NULL,
  CONSTRAINT `BAZ` FOREIGN KEY (`bar`) REFERENCES `qux` (`bar`) ON DELETE CASCADE ON UPDATE CASCADE
);

I'm getting:


ERROR 1022 (23000): Can't write; duplicate key in table 'foo'

But if I:

SELECT *
  FROM information_schema.REFERENTIAL_CONSTRAINTS
 WHERE CONSTRAINT_SCHEMA = "my_db"
   AND CONSTRAINT_NAME LIKE "BAZ";

I'm getting an empty set.

I have also tried to dump the schema and search for "BAZ" there but found nothing.

Creating a table naming foreign key anything but "BAZ" goes through.

How could it be?

You likely have an index for a foreign key on some other table in your DB, which has the same constraint name and is thus causing a namespace collision.

If you are using all InnoDB tables (as you should in 2017), try this to locate the offending table:

SELECT t.name
  FROM information_schema.innodb_sys_indexes i
  JOIN information_schema.innodb_sys_tables t
 USING (table_id)
  WHERE i.name = 'BAZ';

Or this may work, too:

SELECT *
FROM information_schema.innodb_sys_foreign
WHERE id REGEXP 'BAZ;

StackExchange Database Administrators Q#179686, answer score: 3