Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

closed as off topic by Michael Hampton, Magellan, Scott Pack, HopelessN00b, Iain Oct 18 '12 at 19:10

Questions on Server Fault are expected to relate to server, networking, or related infrastructure administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

2 Answers 2

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.

share|improve this answer

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.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.