Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2005

    Unanswered: Tree structures in SQL


    i'm writing a app in c# and have to store Trees in a Database.

    I'm working with Datasets for the exchange between the DB and the App.

    The trees have the same options like the windows folders. If u delete a node, all subnodes should be deleted too.

    But something a Foreign Key from ParentID references (Id) with the delete-Rule on cascade seems not to be possible, because of multiple cascade Paths or cycles. Do i have to add some xtra constarins:

    Not Possible:

    create Table tree (
    Id varchar Not null,
    ParentId varchar Not null,
    Constraint pk1 Primary Key (Id),
    Constraint fk1 Foreign Key (ParentId) references tree(Id)
    On Update Cascade
    On delete CAscade

    Do i have to write triggers, which delete The subnodes too and set the Update-/deleterulr on NO Action


  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    You need to use a trigger to do this.

    In the trigger, create a temporary table to store the primary key values of the records to be deleted. Insert the pkey of the target records into the table, and then loop through adding child pkeys of records already in the temporary table until no @@ROWCOUNT returns 0. Then link your temporary table back to your production table on the pkey values and perform your delete.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

Posting Permissions

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