469,602 Members | 1,650 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,602 developers. It's quick & easy.

I need help in MySQL cascade on delete in MyISAM?

Hi

I have MyISAM tables

When I'm deleting parent I want to delete children in the same time.

How can I do It? What are possibilities?

Thanks
Jul 20 '05 #1
3 8562
saracen44 wrote:
I have MyISAM tables
When I'm deleting parent I want to delete children in the same time.
How can I do It? What are possibilities?


As far as I have read, referential constraints are not enforced in
MyISAM tables at all, so cascading referential integrity probably
doesn't work for that table type either.

Bill K.
Jul 20 '05 #2
> As far as I have read, referential constraints are not enforced in
MyISAM tables at all, so cascading referential integrity probably
doesn't work for that table type either.

Bill K.


So how can I solve this problem??

I wont to delete parent with children in the same time.

How can I connect them to make that possible?

Writing script to check if parent has child then if child has a grandchidren
and so on.... and deleting them from the end is very time consuming when
executing by client.

How can I sole it?

Could you just write me some hints.

I will be very grateful

Regards

Maciek D

Jul 20 '05 #3
saracen44 wrote:
As far as I have read, referential constraints are not enforced in
MyISAM tables at all, so cascading referential integrity probably
doesn't work for that table type either.


So how can I solve this problem??
I wont to delete parent with children in the same time.
How can I connect them to make that possible?


The simplest way to solve this is to change your tables to InnoDB
tables, which do enforce referential integrity and cascading

Read the following web pages:
http://dev.mysql.com/doc/mysql/en/Co...to_InnoDB.html
http://dev.mysql.com/doc/mysql/en/In...nstraints.html

Another way to try to solve this, if you are using MySQL 4.0 or later,
is to try using the multi-table DELETE syntax.
See http://dev.mysql.com/doc/mysql/en/DELETE.html.

If you have a requirement to run cascading DELETEs over many referencing
tables, this could become laborious to construct the correct SQL. You
might forget to include one of the dependent tables.

If you aren't using MySQL 4.0, then you can't use the multi-table DELETE
syntax.

I think it would be easier to convert your database to InnoDB, and then
you could rely on cascading referential integrity constraints.

Regards,
Bill K.
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by NotGiven | last post: by
4 posts views Thread by Chris | last post: by
3 posts views Thread by knoak | last post: by
reply views Thread by Gordon | last post: by
9 posts views Thread by Robert Schneider | last post: by
1 post views Thread by PowerLifter1450 | last post: by
1 post views Thread by webandwe | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.