Удаление иерархических данных в таблице SQL
У меня есть таблица с иерархическими данными.
Столбец "ParentId", который содержит идентификатор ( "ID" - ключевой столбец) его родителя.
При удалении строки я хочу удалить всех детей (все уровни вложенности).
Как это сделать?
Спасибо
Ответы
Ответ 1
Когда количество строк не слишком велико, работает рекурсивный подход erikkallen.
Здесь альтернатива, которая использует временную таблицу для сбора всех детей:
create table #nodes (id int primary key)
insert into #nodes (id) values (@delete_id)
while @@rowcount > 0
insert into #nodes
select distinct child.id
from table child
inner join #nodes parent on child.parentid = parent.id
where child.id not in (select id from #nodes)
delete
from table
where id in (select id from #nodes)
Он начинается с строки с @delete_id и спускается оттуда. Утверждение о том, чтобы защитить от рекурсии; если вы уверены, что его нет, вы можете его оставить.
Ответ 2
В SQL Server: используйте рекурсивный запрос. Учитывая CREATE TABLE tmp (Id int, Parent int), используйте
WITH x(Id) AS (
SELECT @Id
UNION ALL
SELECT tmp.Id
FROM tmp
JOIN x ON tmp.Parent = x.Id
)
DELETE tmp
FROM x
JOIN tmp ON tmp.Id = x.Id
Ответ 3
Добавьте ограничение внешнего ключа. Следующий пример работает для MySQL (ссылка на синтаксис):
ALTER TABLE yourTable
ADD CONSTRAINT makeUpAConstraintName
FOREIGN KEY (ParentID) REFERENCES yourTable (ID)
ON DELETE CASCADE;
Это будет работать на уровне базы данных, dbms будет гарантировать, что после того, как строка будет удалена, все ссылочные строки также будут удалены.
Ответ 4
Зависит от того, как вы храните свою иерархию. Если у вас есть только ParentID, возможно, это не самый эффективный подход. Для удобства манипуляции с поддеревом вы должны иметь дополнительный столбец Parents
, который хранит все родительские идентификаторы, такие как:
/1/20/25/40
Таким образом вы сможете получить все под-узлы просто:
where Parents like @NodeParents + '%'
Второй подход
Вместо ParentID вы также можете иметь значения left
и right
. Вставки, делающие это так, медленнее, но операции выбора очень быстры. Особенно при работе с узлами поддерева... http://en.wikipedia.org/wiki/Tree_traversal
Третий подход
проверять рекурсивные CTE, если вы используете SQL 2005 +
Четвертый подход
Если вы используете SQL 2008, проверьте тип HierarchyID. Это дает достаточно возможностей для вашего дела.
http://msdn.microsoft.com/en-us/magazine/cc794278.aspx
Ответ 5
Добавьте триггер в таблицу, подобную этой
создать триггер TD_MyTable для myTable для удаления в качестве - Удалить один уровень детей delete M from deleted D inner join myTable M на D.ID = M.ID
Каждое удаление вызовет удаление в той же таблице, многократно вызывая триггер. Проверяйте книги онлайн для получения дополнительных правил. Может быть ограничение на количество раз, когда триггер может гнездиться.
ST
Ответ 6
Зависит от вашей базы данных. Если вы используете Oracle, вы можете сделать что-то вроде этого:
DELETE FROM Table WHERE ID IN (
SELECT ID FROM Table
START WITH ID = id_to_delete
CONNECT BY PRIOR.ID = ParentID
)
ETA:
Без CONNECT BY это становится немного сложнее. Как предполагали другие, триггерное или каскадное ограничение удаления, вероятно, было бы самым простым.
Ответ 7
Что вы хотите, это ссылочная целостность между этими таблицами.
Ответ 8
Триггеры могут использоваться только для иерархий 32 уровня в глубину или меньше:
http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/05/11/defensive-database-programming-fun-with-triggers.aspx