# MySQL complains key exists but I can’t find it

constraintforeign keyMySQL

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\$';