Solved

MySQL Delete

Posted on 2010-09-21
5
388 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 7

Accepted Solution

by:
mmr159 earned 500 total points
ID: 33723915
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
ID: 33723935
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
ID: 33723937
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
ID: 33723953
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
ID: 33723996
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

740 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