CODEWITHSUNDEEP
sql-serverkeycascadecascading-deletes
osmiumbin
osmiumbin

Reputation: 339

Cascade delete on duplicate foreign keys? Is that possible?

My MSSQL DB design is as follows:

One table with Images, image_id = PK One table with Videos, video_id = PK One table with Comments, comment_id = PK, and two fields that uniquely match the parent: item_id (equals either image_id or video_id) and item_type which tells me who is the parent (either an image or a video).

How can I add a Cascading Delete, so that when I delete an image, it automatically deletes the comments matching Images.image_id = Comments.item_id AND Comments.item_type = 'image'? As you see, for this to work I have to specify both the item type and item_id since another comment can exist on the same item_id but with different item_type attribute.

Upvotes: 1

Views: 281

Answers (1)

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

Use a TRIGGER, This allows you to include the necessary c.item_type = 'image' filter:

CREATE TRIGGER [dbo].[TrgImagesDelete] ON [dbo].[Images] FOR DELETE
AS
BEGIN
    DELETE c
    FROM
        Comments c
        JOIN Deleted d
            ON c.item_id = d.image_id
            AND c.item_type = 'image'
END
GO

Upvotes: 1

Related Questions