Need to change my SQL structure for deletion

Question

Ok. I've tried the trigger, but it didn't work.

I have Cascades from A to Linker and from B to Linker, Cascade from Users to A, No Action from Users to B.

My trigger is on Users and is as follows:

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER TRIGGER [trig_delUser] ON [dbo].[aspnet_Users] FOR DELETE AS SET NOCOUNT ON; DELETE FROM B WHERE B.UserId = Deleted.UserId

I get the exception: The DELETE statement conflicted with the REFERENCE constraint "FK_B_aspnet_Users"

I am working with a modified aspnetdb SQL database:

[Partial DB diagram][2]

I have cascade deletion on the B_Linker relationship and the A_Linker relationship and there doesn't seem to be any danger of cycles occuring.

When I delete a user, I would like all A entries and B entries to be deleted along with any associated linker entries; unfortunately, SQL mgmt studio will only let me put a cascade delete rule on EITHER aspnet_Users_A or aspnet_Users_B, not both.

What do I need to do?

Many Thanks.

[2]: http://i48.tinypic.com/2nsnc3k.png

Answer 1

You just have to put the ON DELETE CASCADE rules on the foreign keys.

Answer 2

This is one of the unfortunate and frankly rather annoying limitations of SQL Server.

It's not the fact that there could be cycles that's the problem, it's simply as the error says - you have multiple cascade paths to the Linker table. The first is aspnet_Users -> A -> Linker and the second is aspnet_Users -> B -> Linker.

You only really have a couple of choices:

• Choose one path to implement the CASCADE on and set the other to NO ACTION. Then write a Stored Procedure that deletes the non-cascaded child entities before deleting the parent entities in order to prevent a foreign key error. Or, don't CASCADE either and have your SP do the cascading for both.

• Don't add a foreign key at all on the second relationship; instead, use a FOR DELETE trigger on the parent to delete the child entities. I very much dislike using triggers for RI, but this isn't much worse than the first option. In some ways it's better, because database clients don't have to worry about your specific implementation of the FK relationships.

Neither are ideal, but there is no perfect solution other than to change your design. If it is possible for you to change your schema such that you don't have multiple cascade paths, that would be the best thing to do - but I recognize that there are (many) real-world situations where this is not possible. Not knowing the specifics of your schema, I can't say for sure whether or not there's a more optimal design.

Answer 3

I would not implement this through on delete cascade. Or through a trigger. What would happen if someone needed to delete 40,0000 records all at once. You would lock up the parent and all the child tables possibly for minutes possibly hours while it runs through and does it's thing.

So really what you should do is write the deletes to the child tables first then the delete to the parent table and put them in a transaction. This will work well enough when you are delting one record at a time. Large deletes should be written separately (don't loop throuhg the existing proc) and are another subject, but at least now you can do them without killing your server.