Geek Answers Handbook

Component primary keys and foreign key constraint error

I have an SQL table defined below:

CREATE TABLE [TestComposite] (  
    ID int,  
    SiteUrl nvarchar(255),  
    Name nvarchar(max) NOT NULL,  
    ParentID int NULL,  
    PRIMARY KEY (ID, SiteUrl)  
);

Items and folders are stored inside the same table, if the item is inside a folder, the ParentID column is the folder identifier. And I would like to be able to delete CASCADE items / folders when deleting a folder.

An example may be more explicit:

INSERT INTO [TestComposite] VALUES (1, 'site1', 'Item1', NULL)
INSERT INTO [TestComposite] VALUES (2, 'site1', 'Item2', NULL)
INSERT INTO [TestComposite] VALUES (3, 'site1', 'Folder1', NULL)
INSERT INTO [TestComposite] VALUES (4, 'site1', 'Folder1.Item1', 3)
INSERT INTO [TestComposite] VALUES (5, 'site1', 'Folder1.Item2', 3)
INSERT INTO [TestComposite] VALUES (6, 'site1', 'Folder1.Folder1', 3)
INSERT INTO [TestComposite] VALUES (7, 'site1', 'Folder1.Folder1.Item1', 6)
etc...

So, if I delete item 3 (folder), I want items / folders 4, 5, 6 and 7 to also be deleted.

I tried to add a restriction like this:

ALTER TABLE [TestComposite] 
ADD CONSTRAINT fk_parentid 
FOREIGN KEY (ParentID, SiteUrl) 
REFERENCES [TestComposite] (ID, SiteUrl) ON DELETE CASCADE;

But this gives me this error:
Representation of the FOREIGN KEY constraint "fk_parentid" in the table "TestComposite" can cause loops or several cascading paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION or change other FOREIGN KEY constraints.

SiteUrl ParentSiteUrl, , , FK/PK, .

- ?

,

+5
4

ON DELETE NO ACTION , :

WITH    q AS
        (
        SELECT  id, SiteURL
        FROM    TestComposite
        WHERE   id = 3
                AND SiteURL = 'site1'
        UNION ALL
        SELECT  tc.id, tc.SiteURL
        FROM    q
        JOIN    TestComposite tc
        ON      tc.ParentID = q.Id
                AND tc.SiteURL = q.SiteURL
        )
DELETE
FROM    TestComposite
WHERE   EXISTS
        (
        SELECT  id, SiteURL
        INTERSECT
        SELECT  *
        FROM    q
        )
+5

SQL Server 2008, HierarchyID .

+1

, , ParentId, . , , , .

0
source

The problem is that you create the possibility of a recursive cascade - when each remote cascade can create any number of subsequent deletes. MS SQL does not support it. Try deleting them manually. By the way, I do not recommend cascading deletions.

http://support.microsoft.com/kb/321843

0
source

More articles:


All Articles