Мне определили таблицу SQL как указано ниже:
CREATE TABLE [TestComposite] (
ID int,
SiteUrl nvarchar(255),
Name nvarchar(max) NOT NULL,
ParentID int NULL,
PRIMARY KEY (ID, SiteUrl)
);
Объекты и папки хранятся в той же таблице, если объект в папке, столбец ParentID является идентификатором папки. И я хотел бы смочь удалить КАСКАДНЫЕ объекты/папки, когда я удаляю папку.
Пример может быть более явным:
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...
Таким образом, если я удаляю объект 3 (папка), я хочу, чтобы объекты/папки 4, 5, 6 и 7 были удалены также.
Я пытался добавить ограничение, подобное:
ALTER TABLE [TestComposite]
ADD CONSTRAINT fk_parentid
FOREIGN KEY (ParentID, SiteUrl)
REFERENCES [TestComposite] (ID, SiteUrl) ON DELETE CASCADE;
Но это дает мне эту ошибку:
Представление ограничения FOREIGN KEY 'fk_parentid' на таблице 'TestComposite' может вызвать циклы или несколько каскадных путей. Укажите НА, УДАЛЯЮТ NO ACTION или ON UPDATE NO ACTION, или изменяют другие ограничения FOREIGN KEY.
Я также пытался добавить второй столбец SiteUrl под названием ParentSiteUrl, в случае, если проблема состояла в том, что столбец couln't быть частью того же FK/PK, но у меня есть то же сообщение об ошибке.
Я делаю что-то не так?
Спасибо,
Создайте ограничение 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
)
Проблема в том, что вы создаете возможность рекурсивного каскада - когда каждое удаление каскадом может создавать любое количество последующих удалений. MS SQL его не поддерживает. Попробуйте удалить их в своем коде вручную. Кстати, я не рекомендую каскадное удаление.
Если у вас SQL Server 2008, используйте для этой работы тип from HierarchyID .
Я думаю, то, что вы хотите сделать, может быть достигнуто путем добавления нового столбца под названием ParentId, а затем объявить его как внешний ключ с первичным ключом. Таким образом, проблема будет решена, и вы сможете делать все, что хотите
.