runnuger
asked on
MySQL Delete
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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.
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.
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
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