none
Same table foreign key constraint cascade delete RRS feed

  • Question

  • Hi!

     

    First off: Is it possible to do what the subject says in SQL 2005? (Be able to set delete rules to cascade in a table that references it self with relations)

     

    My guess is no, so thats why I put the thread in this forum. Because my following question will then be, Does anyone know a smart and easy way around this problem? If I have to do it with code in C# wich is the best way?

     

    This is what I got:

    * One table with Categories

    * The table has one Id column and one ParentId column and a relation between the two, ParentId could be either another CategoryId or null if its a root category.

     

    So you could pretty much see my table and its records as nodes of a treeview, I now want to be able to "select any node" in this "treeview" and delete it. In the process all childnodes and subchildnodes .... should be deleted.

     

    In my C# code against the SQL 2005 table I cannot delete my records in order, I have to do it backwards because of the constraint rules of the relationship, and delete the "nodes" at the very outest branches of the tree first. This poses for some obvious problems of course.

     

    Any suggestions?

     

    /Leyan

     

    Friday, August 24, 2007 9:25 AM

Answers

  • You must do this using trigger. Deleting parent in table that stores hierarchical data should mean that all childs for this parent and also all childs of parent childs, and that in recursion, need to be deleted.
    But recursion query in SQL Server 2005 is posible using CTE and specific UNION ALL join of two queries. Here is a example of using recursion query:

    --get all records using recursive query
    WITH Childs (Id, ParentId) AS

    (

          SELECT a.Id, a.ParentId

          FROM deleted a

          UNION ALL

          SELECT

                a.Id, a.ParentId

          FROM dbo.MainTable a

           INNER JOIN Childs b ON a.ParentId = b.Id

    )


    --delete some other tables data that have foreign key to this table

    DELETE      OtherTable

    FROM  OtherTable, Childs

    WHERE OtherTable.Id = Childs.Id


    --delete data from main table 

    DELETE      MainTable

    FROM  MainTable, Childs

    WHERE MainTable.ParentId = Childs.Id

     

    Friday, August 24, 2007 9:44 AM

All replies

  • You must do this using trigger. Deleting parent in table that stores hierarchical data should mean that all childs for this parent and also all childs of parent childs, and that in recursion, need to be deleted.
    But recursion query in SQL Server 2005 is posible using CTE and specific UNION ALL join of two queries. Here is a example of using recursion query:

    --get all records using recursive query
    WITH Childs (Id, ParentId) AS

    (

          SELECT a.Id, a.ParentId

          FROM deleted a

          UNION ALL

          SELECT

                a.Id, a.ParentId

          FROM dbo.MainTable a

           INNER JOIN Childs b ON a.ParentId = b.Id

    )


    --delete some other tables data that have foreign key to this table

    DELETE      OtherTable

    FROM  OtherTable, Childs

    WHERE OtherTable.Id = Childs.Id


    --delete data from main table 

    DELETE      MainTable

    FROM  MainTable, Childs

    WHERE MainTable.ParentId = Childs.Id

     

    Friday, August 24, 2007 9:44 AM
  • Splendid!

     

    Took me a few minutes to figure out the logic behind this and figure out where to put the first deleted record, but it works fine now.

     

    Thanks alot!

     

    Friday, August 24, 2007 10:27 AM
  •  

    I would like to take this further. If any of those categories are used in another table, constraints will give me an error. Now I can recursivly UPDATE or DELETE all child records. But if I somehow would like to iterate over the children and DELETE those categories that are never used and UPDATE (inactivating) those that are used, do you then also have any good suggestions.

     

    Cursor? (how is that implemented in your code?)

     

    /Leyan

    Friday, August 24, 2007 12:25 PM
  • I don't understand you completely. In my first example i have a code for deleting records in another table which have foreign key to main table. You will need to delete all foreign key reference rows in other tables before deleting rows in main table.
    About how to delete all Categories that are not used, you can do that with simple delete statement, and the triger again will do the job it should.
    How to delete records from one table that are not used in another:

    DELETE MainTable

    FROM MainTable AS MT

    LEFT OUTER JOIN AnotherTable AS AT ON MT.Id = AT.Id

    WHERE AT.Id IS NULL


     

    Friday, August 24, 2007 12:33 PM
  • Sorry, I probably didn't explain well enough. Its like this. First off I have another column in the category table called Active, wich is a bit holding the information wether the record is active or not.

     

    The code you supplied worked perfectly to delete one record and all of its children from the categories table. I just had to do some minor changes to fit my scenario.

     

    My categories are used in other tables, BUT records in those tables cannot and will not be deleted, ever. So if one or more of the categories in the set to be deleted is used i cannot delete it. So I want to set The Active Column to false instead, in other words inactivate the category instead of deleting it.

     

    So constraints sometimes makes it impossible for me to delete all the categories in the set. By changing your code for UPDATE instead I could inactivate all categories instead, but that leaves me with alot of uned inactivated categories that could very well have been deleted.

     

    I therefore want to iterate through the set of categories and check wether its used or not. If its used I want to set Active to false, if its not used I want the record deleted.

     

    I gues something like this would work:

     

    DECLARE @Id int

    WITH Childs (Id, ParentId) AS

    (

    SELECT a.Id, a.ParentId

    FROM category a

    Where a.Id = 12

    UNION ALL

    SELECT

    a.Id, a.ParentId

    FROM dbo.Category a

    INNER JOIN Childs b ON a.ParentId = b.Id

    )

    DECLARE childs_cursor CURSOR

    FOR SELECT * FROM childs

    OPEN childs_cursor

    FETCH NEXT FROM childs_cursor INTO @Id

    IF @@FETCH_STATUS <> 0

    BEGIN

    --delete data from main table

    --if used UPDATE Active to false, if not used DELETE record

    FETCH NEXT FROM childs_cursor INTO @Id

    END

     

     

    I can't get it to work though.

     

    /Leyan

     

    Friday, August 24, 2007 12:55 PM
  • For that functionality you will need INSTEAD OF DELETE trigger and not FOR DELETE. So when you trying to delete records you will some of records delete, and some of them update. You have all the queries in my posts how to get all childs for parent rows. You will need two joins with external tables. One that will get all rows from that child records that are not used, and you will delete them. And from second query, you will update all childs to Active='False' if that chilid is used in external tables.

    You don't need cursor for this selects, because they are very simple. I already write how to select rows from one table which keys are not used in second one.
    If you change the where condition instead of IS NULL to put IS NOT NULL you will get all rows that are used, but for this query for returning used childs, it is better to make inner join with external tables. You will get the same results.

    Friday, August 24, 2007 1:16 PM
  • Hi, im trying to use this code but i get an exception on that second delete: "Invalid object name 'Childs'." any ideas? this is my code: ALTER TRIGGER [delItemTrigger] on [dbo].[ITEMS] for delete AS BEGIN WITH Childs (itemID, parent) AS ( SELECT a.itemID, a.parent FROM [dbo].[ITEMS] a UNION ALL SELECT a.itemID, a.parent FROM [dbo].[ITEMS] a INNER JOIN Childs b ON a.itemID = b.parent ) --delete some other tables data that have foreign key to this table DELETE ITEMS_CONTENTS FROM ITEMS_CONTENTS , Childs WHERE [dbo].[ITEMS_CONTENTS].itemID = Childs.itemID --delete data from main table -- if i leave this out it work fine! but i need to delete this row as well DELETE ITEMS FROM ITEMS, Childs WHERE [dbo].[ITEMS].itemID = Childs.itemID END
    Wednesday, May 20, 2009 9:13 AM
  • You must do this using trigger. Deleting parent in table that stores hierarchical data should mean that all childs for this parent and also all childs of parent childs, and that in recursion, need to be deleted.
    But recursion query in SQL Server 2005 is posible using CTE and specific UNION ALL join of two queries. Here is a example of using recursion query:

    --get all records using recursive query
    WITH Childs ( Id, ParentId) AS

    (

          SELECT a. Id, a. ParentId

          FROM deleted a

          UNION ALL

          SELECT

                a. Id, a. ParentId

          FROM dbo. MainTable a

            INNER JOIN Childs b ON a. ParentId = b. Id

    )


    --delete some other tables data that have foreign key to this table

    DELETE       OtherTable

    FROM   OtherTable, Childs

    WHERE OtherTable.Id = Childs. Id


    --delete data from main table 

    DELETE       MainTable

    FROM   MainTable, Childs

    WHERE MainTable. ParentId = Childs. Id

     


    Hi,
    I tried your code, but for some reason i get this " Invalid object name 'deleted'. "
    I'm a newbie in SQL, and I'd appreciate any help !

    Thank you


    here's my code

    --get all records using recursive query
    WITH Childs (ID, RefID) AS
    (
    	SELECT a.ID, a.RefID
    	FROM deleted a
    	UNION ALL
    	SELECT a.ID, a.RefID
    	FROM ProductsCat a
    	INNER JOIN Childs b ON a.ParentId = b.Id
    )
    
    --delete some other tables data that have foreign key to this table
    /*
    DELETE OtherTable
    FROM  OtherTable, Childs
    WHERE OtherTable.Id = Childs.Id
    */
    
    --delete data from main table 
    DELETE ProductsCat
    FROM ProductsCat, Childs
    WHERE ProductsCat.RefID = Childs.ID
    AND ProductsCat.ID = 1

    Friday, May 22, 2009 1:33 PM