How to delete a record when two tables have foreign key referenced to each other?

Question

Delete any record of them will report an error like this:

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

How to overcome this problem?

Answer 1

Have you tried:

UPDATE table1 SET reference=NULL;
DELETE FROM table2 WHERE id=1;
DELETE FROM table1 WHERE id=1;

By clearing the reference, the FK constraint shouldn't be a problem any more.

Answer 2

You have two options. One is to clear the reference, as Gorilla pointed out in his post. You do this by setting the reference to null or something similar, then delete the posts in the right order, but this is not what you typically want to do.

Instead you probably want to do a cascade delete, but your schema is not correctly configured for this. Cascade delete means that the posts referencing the post you try to delete will be removed also.

The syntax to setup a table to support cascades looks something like this:

CREATE TABLE car(
        car_id serial PRIMARY KEY,
        owner_id integer NOT NULL REFERENCES owner ON DELETE CASCADE);

If you setup your tables like this deletion of the original post (an owner in the example) will result in the deletion of all posts referencing (cars in the example). You will avoid having to clear references and remove all posts by hand.

Depending on the type of database you use an ALTER TABLE command to introduce the cascade delete in your schema.