deleting parent and all child records

[tty0]

right, im totally baffled now, maybe im just having an off day and its dead simple but ive lost the plot. HEres my problem.

I got a table with parent records with children who can also have children. I need to delete a parent and all child records cascading down.

1) what would the query look like
2) is the table set out correctly

------------------------------------
| ID (PK) | CHILD | PARENT | NAME |
------------------------------------

the ID is made of a trimmed out datestamp and is the primary key, the child field is simply indicating wether it has children 1 = yes / 0 = no. And the parent shows 0 for a top level record or whatever value its parent has as a its value in the ID field.

typical hierachy would be

product1
product2
product3
product4
product5
product6
product7

so if i wanted to delete productset 3 it would select product sets 3, 4 and 5

any help greatly appreciated

cheers





'mi casa es su casa'
]-=tty0=-[

 

[TonyGroves]

Regarding the table structure, the field "child" is inappropriate, since a record can have many children. But if a record can have only one direct parent, then the field "parent" is enough data to describe the record's relationships.

If you want to delete a record and all its descendants, then it can not be done in one query. You could instead use a recursive function, which would first call the function for each of its children, then delete the record.

 

[ericbrunson]

What if you put a foreign key constraint on "parent" and then made it cascade on delete?

 

[tty0]

cheers guys,

both sound spot on, tonys works well as i coded that one up last night, but i'll have a look into yours eric today and test it out as that would take some load off the server.


'mi casa es su casa'
]-=tty0=-[

 

[tty0]

eric, i take it your talking about this command:

ALTER TABLE yourtablename
ADD [CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES anothertablename(...)
[on_delete_and_on_update_actions]


'mi casa es su casa'
]-=tty0=-[