Deleting hierarchical data in SQL table.

Question

I have a table with hierarchical data.
A column "ParentId" that holds the Id ("ID" - key column) of it's parent.

When deleting a row, I want to delete all children (all levels of nesting).

How to do it?

Thanks

Answer 1

Depends how you store your hierarchy. If you only have ParentID, then it may not be the most effective approach you took. For ease of subtree manipulation you should have an additional column Parents that wouls store all parent IDs like:

/1/20/25/40

This way you'll be able to get all sub-nodes simply by:

where Parents like @NodeParents + '%'

Second approach
Instead of just ParentID you could also have left and right values. Inserts doing it this way are slower, but select operations are extremely fast. Especially when dealing with sub-tree nodes... http://en.wikipedia.org/wiki/Tree_traversal

Third approach
check recursive CTEs if you use SQL 2005+

Fourth approach
If you use SQL 2008, check HierarchyID type. It gives enough possibilities for your case. http://msdn.microsoft.com/en-us/magazine/cc794278.aspx

Answer 2

Add a trigger to the table like this

create trigger TD_MyTable on myTable for delete as -- Delete one level of children delete M from deleted D inner join myTable M on D.ID = M.ID

Each delete will call a delete on the same table, repeatedly calling the trigger. Check books online for additional rules. There may be a restriction to the number of times a trigger can nest.

ST

Answer 3

Depends on your database. If you are using Oracle, you could do something like this:

DELETE FROM Table WHERE ID IN (
  SELECT ID FROM Table
  START WITH ID = id_to_delete
  CONNECT BY PRIOR.ID = ParentID
)

ETA:

Without CONNECT BY, it gets a bit trickier. As others have suggested, a trigger or cascading delete constraint would probably be easiest.

Answer 4

Add a foreign key constraint. The following example works for MySQL (syntax reference):

ALTER TABLE yourTable
ADD CONSTRAINT makeUpAConstraintName
FOREIGN KEY (ParentID) REFERENCES yourTable (ID)
ON DELETE CASCADE;

This will operate on the database level, the dbms will ensure that once a row is deleted, all referencing rows will be deleted, too.

Answer 5

On SQL Server: Use a recursive query. Given CREATE TABLE tmp(Id int, Parent int), use

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

Answer 6

What you want is referential integrity between these tables.

Answer 7

When the number of rows is not too large, erikkallen's recursive approach works.

Here's an alternative that uses a temporary table to collect all children:

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)

It starts with the row with @delete_id and descends from there. The where statement is to protect from recursion; if you are sure there is none, you can leave it out.

Answer 8

Triggers can only be used for hierarchies 32 levels deep or less:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/05/11/defensive-database-programming-fun-with-triggers.aspx