Mysql – I need help with a SQL-query that results in an error

MySQL

I get the following error when I try to delete a record in the user table that has an entry in the feedback table:

e.g. when a record in the feedback table has a reference to the user table

1451 – Cannot delete or update a parent row: a foreign key constraint
fails (kbs.feedback, CONSTRAINT fk_feedback_user1 FOREIGN KEY
(user_id) REFERENCES user (user_id) ON DELETE NO ACTION ON
UPDATE NO ACTION)

The relationship between the two tables:

I hope someone could help me into the right direction and that my question is clear enough. I've followed a database design course but forgot a lot of stuff.

Best Answer

The user you are trying to delete have rows in the feedback table. Given your foreign key definition:

CONSTRAINT fk_feedback_user1 
FOREIGN KEY (user_id) 
    REFERENCES user (user_id) 
        ON DELETE NO ACTION 
        ON UPDATE NO ACTION

You need to first delete from the feedback table:

delete from feedback where user_id = ?;
delete from user where user_id = ?;

An alternative is to change the foreign key to:

CONSTRAINT fk_feedback_user1 
FOREIGN KEY (user_id) 
    REFERENCES user (user_id) 
        ON DELETE CASCADE 
        ON UPDATE NO ACTION

Any rows in the feedback table for that user will then be deleted automatically when you delete a user

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.

Thesql replication – missing table for foreign key on slave

Since error 1452 breaks the SQL thread, you can make MySQL Replication skip that error by adding the slave-skip-errors option under the [mysqld] group header in the Slave's my.cnf as follows:

[mysqld]
slave-skip-errors=1452

You will have to restart mysql for this to take effect.

Give it a Try !!!

CAVEAT : Your data integrity will deteriorate when added new data because many INSERT queries or multiple-row INSERTs will fail because of just one row not having parent keys.

You should really consider loading all referenced tables into the slave to have good, clean data. Otherwise, it's not a true copy of the Master.

Best Answer

Related Solutions

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

Thesql replication – missing table for foreign key on slave

Related Question