Joe Graves

Cascade Delete

Prevent orphan records: Cascade Delete


ALTER TABLE dbo.Images  WITH CHECK 
	ADD CONSTRAINT [FK_Message_Images] FOREIGN KEY([MessageId])
	REFERENCES dbo.messages ([MessageId])
ON DELETE CASCADE

Orphan records are created when you have two tables with a parent child relationship. As an example pretend that we have a parent table called messages and a child table called images. The tables are connected through a primary key in the message table called messageid and foreign key in the the image table also label messageId. When we delete a message from the message table we also want to remove any images associated with that message. If you were to delete a message and not delete the images associated with it the table could quickly be come full of data that no longer belongs to anything.

So to prevent orphan records you could create a second delete command in your code after deleting the message that would delete the images for that message. The down side is that if another application deletes a message or you need to have that delete function in more than one place then the image records might not get deleted.


DELETE 
FROM [dbo].messages
WHERE messageId=2

DELETE 
FROM [dbo].images
WHERE messageId=2

You could use a trigger to remove the records in the images table. The downside being that if you need to add addtional child tables or logic in the delete trigger it can create larger code blocks.


CREATE TRIGGER [dbo].[TR_deleteMessages]
   ON  [dbo].[messages]
   AFTER DELETE
AS 
BEGIN

	SET NOCOUNT ON;

DELETE
FROM dbo.images
WHERE messageId=(SELECT MessageId FROM deleted)



END