How to delete items with a many-to-many relationship with 'WHERE' clause in a stored procedure?

Question

In my MSSQL database I have the following three tables:

• Tasks
• Files
• TaskFile

As one can imagine, TaskFile simply maps IDs of the Tasks table to the IDs of the Files table having foreign key constraints to them. I do most of my database operations using stored procedures. Deleting a single file by ID is then easy, I simply have to delete the entries in TaskFile first:

CREATE PROCEDURE [dbo].[spFile_Delete]
    @ID INT = 0
AS
BEGIN
    DELETE
    FROM dbo.[TaskFile]
    WHERE FileID = @ID;


    DELETE
    FROM dbo.[File]
    WHERE ID = @ID;
END

However, I'm wondering what the recommended way is to delete multiple files. For example, file entries belong to a dataset and therefore hold a reference to a DatasetID. I have the following procedure to delete by DatasetID however this needs to be adapted to work with the foreign key constraint:

CREATE PROCEDURE [dbo].[spFile_DeleteByDataset]
    @DatasetID NVARCHAR(128)
AS
BEGIN
    DELETE
    FROM dbo.[File]
    WHERE DatasetID = @DatasetID;
END

The most straight forward way I can think of, is to first query all the FileIDs with the given DatasetID and then use them to delete all entries in TaskFile first. This should work, but doesn't seem like the best solution, especially if I want to go further and have for example have a stored procedure to delete datasets. Now I would have to:

1. Find all the files belonging to the dataset
2. Find all the references in TaskFile and delete dem
3. Delete all File entries
4. Delete the dataset entry

and for each relationship this would go further and further and get more and more complicated.

Basically I'm asking for some best practices in such cases. I'm fairly new when it comes to database management and these convolution seem like a good source for errors. Should each stored procedure only handle it's specific table and I have to manage the correct order of entries/updates/deletions in the data access layer code? Is there a way to automate changes with foreign relationships? Or should you simply never delete anything from the database and only have a flag 'deleted' in a separate column?

Answer 1

Oh I believe you are talking about DELETE/UPDATE CASCADE. It's usually not advised because in general you don't want to delete more than you are targeting in the moment. But depending on the scenario it can be used. For example in your case i believe it's ok: if FileTask has ON DELETE CASCADE in the reference to File, when you delete straight the table File, all FileTask related to this file would be deleted together and you wouldn't need to worry about deleting FileTask before. In your case when you want to delete a file, you also want to delete related FileTasks.