MySQL – Key Exists Error: How to Locate Missing Keys

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?

Best Answer

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

Related Solutions

Mysql – “Cannot add or update a child row” when deleting a record

On the face of it, that does seem impossible.

The thing is, your error suggests it's not that you're trying to delete at all.

The message you're getting suggests you're trying to insert or update a row in the child table, not delete a row from the parent table. If the foreign key you posted was causing the problem relative to a delete, you should see this message, instead:

Cannot delete or update a parent row: a foreign key constraint fails (`test`.`bar`, CONSTRAINT `bar_ibfk_1` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`))

It's also possible you have some BEFORE DELETE trigger magic on survey_main that's doing something unexpected.

Right after this error occurs, try this:

SHOW ENGINE INNODB STATUS;

The LATEST FOREIGN KEY ERROR section should give you something more to go on. Failing that, you could enable the general log, which will show queries executed by triggers and other stored programs, as well as the queries you're directly executing, to shed light on what might be going on behind the scenes.

Update (#1) Things are definitely not as they seem and the full table definitions are going to be pretty critical, here.

Also, the version of MySQL you're using may also be relevant, so please mention it.

With nothing more to go on at the moment, I'm speculating that you have invalid data in the survey_id column of the survey_answers table. To test that theory:

SELECT *
  FROM survey_answers sa
  LEFT JOIN survey_main sm ON sm.id = sa.survey_id
 WHERE sm.id IS NULL;

If I understand your schema correctly, then this query will return zero rows if I am wrong. :) If you get rows returned, then those rows have survey_answers records that contain an survey_id value that doesn't exist in the id column of survey_main.

Mysql – Finding rows for a specified date range

Another way to get the result is this. It finds first all groups that the teacher has surely taught (or is going to) by checking that she has started within the month and then in another subquery it finds - for every group - the last teacher that started at the first day of the month or earlier.

With the unique index you have on the table, the second subquery should be quite efficient. The first subquery would benefit from an index on (teacherid, startdate, groupid):

SELECT groupid
FROM pupilgroupteacher
WHERE teacherid = @teacher 
  AND startdate >= @month + INTERVAL 1 DAY
  AND startdate < @month + INTERVAL 1 MONTH

UNION DISTINCT

SELECT gg.groupid
FROM 
    ( SELECT DISTINCT groupid
      FROM pupilgroupteacher
    ) AS gd
  JOIN pupilgroupteacher AS gg
    ON  gg.groupid = gd.groupid
    AND gg.startdate =
        ( SELECT MAX(gi.startdate)
          FROM pupilgroupteacher AS gi 
          WHERE gi.groupid = gd.groupid
            AND gi.startdate < @month + INTERVAL 1 DAY
        )
WHERE gg.teacherid = @teacher ;

Best Answer

Related Solutions

Mysql – “Cannot add or update a child row” when deleting a record

Mysql – Finding rows for a specified date range

Related Question