Solved

MySQL Delete

Posted on 2010-09-21
5
383 Views
Last Modified: 2012-05-10
Hi EE,

I am trying to delete a record from a MySQL parent table that has five foreign keys using workbench.  When I use the statement DELETE FROM table WHERE id = 24 I get this error - Error Code: 1451
Cannot delete or update a parent row: a foreign key constraint fails.  I am hoping that the delete from the parent table will remove the child table records as well. Any insights?

Thanks Runnuger
0
Comment
Question by:runnuger
5 Comments
 
LVL 7

Accepted Solution

by:
mmr159 earned 500 total points
Comment Utility
Foreign keys enforce relational integrity.  You must either remove the foreign keys (probably not a good idea), or first delete the records referenced by the foreign keys.
0
 
LVL 14

Expert Comment

by:Farzad Akbarnejad
Comment Utility
Hello,
Delete on child rows will be happened if you define cascade reference option in your FOREIGN KEY Constraints. This URL may be helped you.

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

-FA
0
 
LVL 14

Expert Comment

by:john-formby
Comment Utility
Hi,

You can use cascade delete for this.  Check out: http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

Hope this helps,

John
0
 
LVL 2

Expert Comment

by:supersepp
Comment Utility
you probably forgot to set your foreign key to ON DELETE CASCADE

you can fix that by using: ALTER TABLE referencingtable DROP FOREIGN KEY fk_symbol;

and then ALTER TABLE referencingtable
    ADD FOREIGN KEY idx_fk (idxcolname)
    REFERENCES table (idxcolname)
    ON DELETE CASCADE;

or you simply delete all rows from the other tables before deleting from the parent.
0
 
LVL 2

Expert Comment

by:Sandeepratan
Comment Utility
to delete the records from the child table eaither you have to delete the same from parent table or desable the foreignkey constraint.  the automatic delete is depend on the enforced refrential integrity
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now