Reputation: 32788
I have four tables: exam > objectives and > objective > objective topics details. I am not using DELETE CASCADE as during normal operation I don't want it to be possible to delete exams if there are objectives etc.
I created this SQL to do the DELETE in a SQL Server 2012 stored procedure.
BEGIN
DELETE ot
FROM ObjectiveTopic ot
INNER JOIN ObjectiveDetail od
ON ot.ObjectiveDetailId = od.ObjectiveDetailId
INNER JOIN Objective o
ON od.ObjectiveId = o.ObjectiveId
INNER JOIN Exam e
ON o.ExamId = e.ExamId
WHERE e.SubjectId = @SubjectId
DELETE od
FROM ObjectiveDetail od
INNER JOIN Objective o
ON od.ObjectiveId=o.ObjectiveId
INNER JOIN Exam e
On o.ExamId = e.ExamId
Where e.SubjectId = @SubjectId;
DELETE o
FROM Objective o
INNER JOIN Exam e
ON o.ExamId = e.ExamId
WHERE SubjectId = @SubjectId
RETURN 0;
END
This does work but rather than three deletes. Is there a way I could turn on DELETE CASCADE during a transaction, delete all objectives for a particular @SubjectId and then turn DELETE CASCADE off again ? If not is there some other way I can simplify this code or do I always have to do three deletes?
Upvotes: 4
Views: 239
Reputation: 36
Cascade delete is a performance improvement. Although it does the same thing as the deletion process from the lowest child, it gives added performance gains (execution plans can be checked).
So, the best way is to make delete cascade on but for that we'll have to drop the existing foreign key create a new one with the ON DELETE NO ACTION setting enabled. Perform your actions and then go through the same cycle and make ON DELETE CASCADE off again.
Considering these hastles, the option we have at hand is to check the architecture of the underlying tables and the code against them to ensure that switching on on delete cascade wont affect any data and then permanently switching it on.
so, it wouldnt be a feasible technique to perform the actions in one transaction.
I hope this helps. I cant comment to the post due to lack of points :) :). Sorry :)
Upvotes: 1