RE: Cascade Delete within Self Referencing Table

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Alex Papadimoulis (alexRemovePi_at_pa3.14padimoulis.com)
Date: 09/14/04

  • Next message: David G.: "Re: Trigger Accuracy?" 
    Date: Tue, 14 Sep 2004 13:29:03 -0700

    Joe,

    How about changing your model? For what it looks like you're trying to
    implement (category tree), there are beter ways of doing it than the dreaded
    adjency-list.

    Do a search for CELKO Trees in the newsgroups or on the web. Also, he has a
    good book on the topic.

    -- Alex Papadimoulis
    http://weblogs.asp.net/alex_papadimoulis

    "Joe Veler" wrote:

    > Hello Everyone!
    >
    > I am having an incredibly difficult time and have tried one thing after
    > another in the pursuit of a solution to my problem. It seems as though the
    > problem below harasses SQL Server users more than other RDBMS's, so hopefully
    > someone here will have a solution.
    >
    > Problem: I am trying to perform a cascade delete on a self-referencing
    > table. Below are the basics:
    >
    > I have a table with the following columns:
    > idCategory (int identity), idParentCategory (int FK) and txtName (varchar(50))
    >
    > Here is some sample data
    >
    > 1, Null, Main Cat
    > 2, 1, Sub Cat1
    > 3, 1, Sub Cat2
    > 4, 1, Sub Cat3
    > 5, 2, Sub Cat1 Sub1
    > 6, 2, Sub Cat1 Sub2
    >
    > What I am trying to do is make sure that records 5 and 6 are cascade deleted
    > when I delete record 2.
    >
    > Here is what I have tried:
    >
    > 1) What I thought was the simplest. Set up a relationship contraint with
    > the following statement:
    >
    > ALTER TABLE [tblCategories] ADD CONSTRAINT
    > FK_tblCategories_tblCategories FOREIGN KEY(idParentCategory)
    > REFERENCES [tblCategories](idCategory) ON DELETE CASCADE
    >
    > I get the following error:
    >
    > Introducing FOREIGN KEY constraint 'FK_tblCategories_tblCategories' on table
    > 'tblCategories' may cause cycles or multiple cascade paths. Specify ON DELETE
    > NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
    >
    > So, even though something very similar to this works in even lowly Access, I
    > can't get it to work in SQL Server. I did remove the "ON DELETE CASCADE"
    > clause, but when I try to delete record 2, I get an error violating
    > referential integrity (because the child records are not deleted). Here is
    > that error:
    >
    > DELETE statement conflicted with COLUMN SAME TABLE REFERENCE contraint
    > "FK_tblCategories_tblCategories". The conflict occurred in database 'dbName',
    > table 'tblCategories', column 'idParentCategory'
    >
    > With this failure, I went to step 2... triggers.
    >
    > 2) Create a trigger that will delete child records from the parent table.
    > Seems simple enough. So, I create the following trigger:
    >
    > CREATE TRIGGER tblCategories_Trigger1
    > ON dbo.tblCategories
    > FOR DELETE
    > AS
    > BEGIN
    > DELETE tblCategories FROM tblCategories, deleted WHERE
    > tblCategories.idParentCategory = deleted.idCategory
    > END
    >
    > I got the same error as with #1, so I removed the constraint. I thought the
    > problem is that the trigger fires after the first deletion in which the
    > referential integrity contraint would fail. Tried again and then I got the
    > following error:
    >
    > Maximum stored procedure, function, trigger, or view nesting level exceeded
    > (limit 32)
    >
    > Now I am thoroughly confused. I am assuming triggers have the same depth
    > limitation of 32 levels, but where am I going that far? There are a total of
    > 6 records in the table and the code should only go one level down in
    > recursion.
    >
    > Creating a same-table parent-child relationship to perform cascading deletes
    > should be simple, shouldn't it? Any help on this would be most appreciated.
    >
    > Sincerely,
    >
    > Joe Veler

  • Next message: David G.: "Re: Trigger Accuracy?"

    Relevant Pages

    • Re: 1352 NUL bytes at the end of a page? (was Re: Assertion `s && s->tree failed: The sag
      ... Th ecurrent BK tree contains much of the anonvma stuff, ... need threads to trigger it and I've never seen threaded version control ... minor merging error), that's a black and white thing, it doesn't touch ... send the line "unsubscribe linux-kernel" in ...
      (Linux-Kernel)
    • Re: So, how about that old "Eagle Eyes" Cheney?
      ... pulling the trigger, then dragging the body halfway up a tree to feed ...
      (rec.motorcycles)
    • Re: [RFC] ELF Relocatable x86 and x86_64 bzImages
      ... Currently there are 33 patches in my tree to do this. ... The weirdest symptom I have had so far is that page faults did not ... trigger the early exception handler on x86_64. ...
      (Linux-Kernel)
    • Re: 2.6.0: Badness in pci_find_subsys!!
      ... > the interrupt side while something else is hotplugging a new device into ... I try to stick w/ Linus' tree, but I'll attempt to decipher the changelogs on ... It takes 47 muscles to frown, but only 4 to pull the trigger of a finely tuned ... send the line "unsubscribe linux-kernel" in ...
      (Linux-Kernel)
    • Cascade Delete within Self Referencing Table
      ... What I am trying to do is make sure that records 5 and 6 are cascade deleted ... Introducing FOREIGN KEY constraint 'FK_tblCategories_tblCategories' on table ... referential integrity. ... Create a trigger that will delete child records from the parent table. ...
      (microsoft.public.sqlserver.programming)