SQL Cascade Delete with multiple columns

Question

I have a SQL table called [Content] which has columns like:

• ID PrimaryKey, uniqueidentifier, not null
• File uniqueidentifier, not null
• FileHighResolutionID uniqueidentifier, not null
• FileHighResolutionProID uniqueidentifier, not null
• FileVectorID uniqueidentifier, not null
• ThumbnailID uniqueidentifier, not null

The first column value is an auto-generated. The remaining five column values reference the ID column of a table called [File]

When I delete a row from [Content], I would like to also delete the rows from [File] where [File].[ID] matches a value in [Content].[File], [Content].[HighResolutionID], etc., etc.

As this is tied to multiple columns, will a cascade delete work? If so, what is the syntax.

The best I could come up with:

ALTER TABLE [dbo].[File]
ADD CONSTRAINT fk_content_file
FOREIGN KEY (FileID)
REFERENCES [dbo].[File] (ID)
ON DELETE CASCADE;

ALTER TABLE [dbo].[File]
ADD CONSTRAINT fk_content_filehighresolution
FOREIGN KEY (FileHighResolutionID)
REFERENCES [dbo].[File] (ID)
ON DELETE CASCADE;

ALTER TABLE [dbo].[File]
ADD CONSTRAINT fk_content_highresolutionpro
FOREIGN KEY (FileHighResolutionProID)
REFERENCES [dbo].[File] (ID)
ON DELETE CASCADE;

ALTER TABLE [dbo].[File]
ADD CONSTRAINT fk_content_vector
FOREIGN KEY (FileVectorID)
REFERENCES [dbo].[File] (ID)
ON DELETE CASCADE;

ALTER TABLE [dbo].[File]
ADD CONSTRAINT fk_content_thumbnail
FOREIGN KEY (ThumbnailID)
REFERENCES [dbo].[File] (ID)
ON DELETE CASCADE;

... but I am not sure if this is correct. I'd rather know more definitively than lose the data that has been populated into the app so far.

Answer 1

I think that in your situation ON DELETE CASCADE is not the correct tool. What cascade delete would do for you is it would delete rows from [Content] if you delete a row from [File], but I think you want the other way around.

This can be implemented with a trigger on [Content], but to be sure that it's safe to delete the row from [File] you'll need to check that it isn't being referenced somewhere else in your [Content] table first (unless you are really, really sure each file is referenced only once).

Here is an example of a trigger which could do this for you. Note that I've only implemented a couple of fields in my [Content] table, I'm sure you can fill in the rest.

Setup

Create Table [file] (
    FileID int Primary Key Clustered);
Create Table [content] (
    ID int,
    [File] int Constraint fFile_content References [file] (FileID),
    [FileHighResolution] int Constraint fFileHighResolution_content References [file] (FileID));

Insert Into [file] (FileID)
Values  (1), (2), (3);

Insert Into [content] (ID, [File], FileHighResolution)
Values  (1, 1, 2),
        (2, 1, 3);

Trigger

Create Trigger trg_Delete_Remove_Unused_File On [content] After Delete As
Begin
    Delete From [file]
      From [file] As f
      Join Deleted As d
        On f.FileID = d.[File]
      Where Not Exists (Select 1 
                          From [content]
                          Where [file] = d.[File]
                             Or FileHighResolution = d.[File]);

    Delete From [file]
      From [file] As f
      Join Deleted As d
        On f.FileID = d.FileHighResolution
      Where Not Exists (Select 1 
                          From [content]
                          Where [file] = d.FileHighResolution
                             Or FileHighResolution = d.FileHighResolution);
End

Before

FileID  
1
2
3

ID   File   FileHighResolution
1    1      2
2    1      3

Verification

Delete From [content] Where ID = 1;

After

FileID
1
3

ID   File   FileHighResolution
2    1      3

File 2 was deleted from the [File] table because it was referenced by the row deleted from [Content] AND it wasn't referenced by any other row in [Content]. File 1 was not deleted from [File] even though it was referenced by the deleted row, because it was referenced by another row which wasn't deleted.

You may have to optimise this for performance, but this should be logically sound.