Results 1 to 3 of 3

Thread: Cascade Delete

  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Unanswered: Cascade Delete

    I am developing a site of E-learning. I am facing a problem. The problem is like this. I have a main category like for example

    1 Courses offered by University of XYZ
    2 Workshops
    3 Courses offered by University of ABC

    now category 1 has sub categories like
    4 Master degree courses
    5 Bachelors degree courses
    6 Diploma courses

    now category 4 again has sub categories like
    7 Master in Computer Science
    8 Master Computer Management
    9 Master of Business Administration
    ....
    (this level can go upto nth level)

    now for any category there are courses attached to it lets say for category 7 3 courses are attached and they are

    1 crs1
    2 crs2
    3 crs3
    (here I would like to mention one thing that once a course is attached to any particular category then the category can not be divided further)

    now for every course there subjects are attached lets say for crs1 3 subjects are attached.

    1 sub1
    2 sub2
    3 sub3

    now for each subject user can assign tutorials, assignment etc....

    My problem is If I want to delete main category then I want to delete all the related sub-categories, courses, subjects, assignments, tutorials attached to it, which I am not able to do.

    I am using MySQL dabase and front-end is in ASP.
    MySQL 4.0.12-nt running on localhost.
    I am using single table to store categories by assigning parentcategoryId to any particular category, and for courses I have categoryId as one field and for subject table I have courseId as one field.

    Can anybody help me in this regard. I hope I am very much clear in
    posting the problem.

  2. #2
    Join Date
    Sep 2003
    Location
    London
    Posts
    56
    You should investigate using the foriegn key constraints provided by using InnoDB Tables.

    Regards,
    Matt.

  3. #3
    Join Date
    Sep 2003
    Posts
    2

    RE:Cascade Delete

    Yes I have added constraints foreign key and on delete cascade. One problem got solved, but can anybody tell me the logic of finding child category and then finding child category of child category and so on....
    because the levels can go to any depth

    Due to foreign key and cascade delete I can delete course, related subjects and related assignment, tutorials for that particular category,

    eg.

    CatId CatDesc parentCatId

    1 First Cat 0
    2 Second Cat 0
    3 Third Cat 0
    4 Forth Cat 1
    5 Fifth Cat 4
    6 Sixth Cat 5
    7 Seventh Cat 6
    .....

    and the chain can go to any level... ok I can find out for particular category what are child categories by selecting * from tbCat where parentCategory=category
    Lets say I got 3 child categoried, now I need to find out for each and every child category whether it is having a child category or not and so on....


    Cat1
    --sCat1
    --ssCat1
    --sssCat1
    ......
    --sCat2
    --.....

    Cat2
    --....

    I thought of writting a recurssive function for finding child categories but it falied

    how to go about finding chain of related categories ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •