SQL Server Cascading

Question

I am making a website where users can post 'Posts' and then users can 'Comment' on these posts. I have a database with 3 tables. One contains User Information, one contains Post Information and the final one contains Comment Information.

I want to set up rules so that if a User is deleted, all of their posts and comments are deleted, and if the user deletes one of their posts then all of the associated comments are deleted. This however sets up 'Multiple CASCADE paths'.

I have been looking for a solution and found some information on triggers. Would they be the best thing to use? If I used them, would I have to change all of the CASCADES to be done by triggers?

Thanks

Clivest

Answer 1

I think this can be setup fairly easily, without the error you encountered, and without the use of triggers, as follows:

1) The foreign key between Users and Posts should be set up to be cascade delete
2) The foreign key between Posts and Comments should be set up to be cascade delete

Answer 2

use declarative referential integrity.

create table foo
(
  id   int         not null primary key ,
  foo  varchar(32) not null ,
)
create table bar
(
  id     int not null primary key ,
  foo_id int     null foreign key references foo ( id ) on delete cascade ,
)

deleting a row from foo will delete all related rows in bar.

Be careful with cascading deletes, though -- fat-fingering a delete statement can cause a whole lot of damage very quickly, not unlike rm(1) in *nix. Cascading deletes can also chew up your transaction log pretty quickly, if you delete a lot of data in one fell swoop.

Answer 3

Another option would be to create two dedicated stored procedures that would do these delete steps as needed

CREATE PROCEDURE dbo.DeleteUser @UserID VARCHAR(50)
AS BEGIN
   DELETE FROM dbo.Comments
   WHERE Author = @UserID

   DELETE FROM dbo.Posts
   WHERE Author = @UserID

   DELETE FROM dbo.User
   WHERE UserID = @UserID
END

and for the second rule:

CREATE PROCEDURE dbo.DeletePost @PostID INT
AS BEGIN
   DELETE FROM dbo.Comments
   WHERE PostID = @PostID

   DELETE FROM dbo.Posts
   WHERE ID = @PostID
END

In this case, you have total control over what really happens, there's no unexpected surprises from cascading deletes, and no need to use triggers either.