2

I need to model an n-level tree in SQL Server. I originally did something like this to create a Node table:

[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[ParentId] [bigint] NULL

It's a self referential table, where ParentID refers back to another Node entity (the root has a null ParentID). But when I try to add a constraint to cascade deletes, I get this:

Introducing FOREIGN KEY constraint 'FK_dbo.Nodes_dbo.Nodes_ParentId' on table 'Nodes' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

From what I read, this seems to be a pretty well known, and reasonable limitation in SQL Server. Some people suggest using a trigger to accomplish this, but I'd prefer to avoid that if possible.

So my question is - how should an n-level hierarchy or tree be modelled in SQL in order to allow cascading deletes?

I'm using SQL Server 2012.

Eric
  • 5,842
  • 7
  • 42
  • 71
  • See http://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths – JohnLBevan Nov 12 '12 at 22:51
  • ps. Are you able to use stored procedures to perform the delete operations? i.e. rather than putting this logic in a constraint / trigger and having those handle the recursive deletion of parents when a delete takes place, put all this logic into a stored proc and call that for any deletes. That won't help if people can access the database directly, but most of the time I'd assume people will be going through a front end program, so you can point it to the proc rather than writing a delete statement. – JohnLBevan Nov 12 '12 at 22:55
  • @JohnLBevan, That's essentially what I ended up doing. I'm using EntityFramework, so I just used the Repository Pattern, and made the repository's delete function recursive. I don't care about efficiency with this app, but if I did, I'd probably move the logic to a stored procedure. – Eric Nov 14 '12 at 15:58

1 Answers1

1

One of the more effective techniques I've seen for doing this is to make the clustered primary key be the variable length full tree path of the node (hat tip to Richard Henderson); to delete a subtree, one need then only delete a range of values.

Jeffrey Hantin
  • 35,734
  • 7
  • 75
  • 94