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 );
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?