3

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?

  • Anything from SHOW CREATE TABLE BAZ; or SHOW CREATE TABLE foo;? – Rick James Jul 12 '17 at 19:43
  • Both negative: ERROR 1146 (42S02): Table '...' doesn't exist. – user39517 Jul 12 '17 at 19:49
  • Does it work if you define the table with CREATE TABLE foo ( bar int UNSIGNED NOT NULL REFERENCES qux (bar) ON DELETE CASCADE ON UPDATE CASCADE ); ? – joanolo Jul 12 '17 at 20:19
  • 2
    Does SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME LIKE "BAZ"; show anything? What version of MySQL do you use? – ypercubeᵀᴹ Jul 17 '17 at 10:08
  • 1
    My point was, does the CREATE TABLE foo run in this database ("my_db")? The error says that you already have "BAZ" as a foreign key in the database that the statement run. Can you try with CREATE TABLE my_db.foo ...? – ypercubeᵀᴹ Jul 17 '17 at 11:55
2
+150

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

Any chance you're configured for case-insensitivity?

Does the following return any records:

SELECT   CONSTRAINT_SCHEMA,
         CONSTRAINT_NAME
FROM     information_schema.REFERENTIAL_CONSTRAINTS
where    lower(CONSTRAINT_SCHEMA) like "my_db"
and      lower(CONSTRAINT_NAME) like "baz";

Some references:

mysql error code 1022 duplicate key in table

Case insensitive constraint names in MySQL

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy