CODEWITHSUNDEEP
c#sqlsql-servert-sqldapper
Caster Troy
Caster Troy

Reputation: 2866

Advanced cascade delete

I have a very simple database whose schema is defined as follows:

CREATE TABLE dbo.Tags
(
    TagName NVARCHAR(100) PRIMARY KEY
);

CREATE TABLE dbo.Posts
(
    PostSlug NVARCHAR(100) PRIMARY KEY,
    Title NVARCHAR(100) NOT NULL
);

CREATE TABLE dbo.PostTagJunction 
(
    PostSlug NVARCHAR(100),
    TagName  NVARCHAR(100)
    PRIMARY KEY (PostSlug, TagName),
    FOREIGN KEY (PostSlug) REFERENCES dbo.Posts (PostSlug)
        ON DELETE CASCADE,
    FOREIGN KEY (TagName)  REFERENCES dbo.Tags (TagName)
);

I want to make it so that when I delete a post from dbo.Posts that resulting non-referenced tags in dbo.Tags are also deleted.

Consider the following code: 

INSERT INTO dbo.Posts
    VALUES ('hello-world', 'Hello World');
INSERT INTO dbo.Tags
    VALUES ('Introduction');
INSERT INTO dbo.PostTagJunction
    VALUES ('hello-world', 'Introduction'); 
DELETE 
FROM dbo.Posts
WHERE postslug = 'hello-world'

Because I use the cascade delete option, the post is removed from dbo.Posts and the record is removed from dbo.PostTagJunction. However, despite being an orphan, the hello-world record in dbo.Tags remains. I want to delete the tag too. How?

I should emphasizes that if the tag is being used by another post, it should not be deleted. Thank you.

I am using Dapper and C# to communicate with the database.

Upvotes: 2

Views: 1083

Answers (2)

SelvaS
SelvaS

Reputation: 2125

Its not possible to delete the parent table records by deleting child table records using cascading option. Create a new stored procedure to delete the Posts, Tags, PostTagJunction based on the PostSlug (@PostSlug) passing from C# application as parameter.

Drop the Cascading constraint in the PostTagJunction table and try something like this.

DECLARE @PostSlug NVARCHAR(100) = 'hello-world'
DECLARE @TagName NVARCHAR(100) = ''

SELECT @TagName= TagName FROM PostTagJunction WHERE PostSlug =@PostSlug
DELETE PostTagJunction WHERE PostSlug =@PostSlug

IF NOT EXISTS(SELECT 1 FROM PostTagJunction WHERE TagName =@TagName)
DELETE Tags WHERE TagName =@TagName

DELETE Posts WHERE PostSlug =@PostSlug

Hope this helps.

Upvotes: 2

Dn24Z
Dn24Z

Reputation: 149

You can denormalize your database using a trigger, in other words you can create a FOR DELETE trigger on dbo.Posts which will automatically delete your record in an orphan dbo.Tags table, example:

CREATE TRIGGER triggerName
    ON dbo.Posts
    FOR DELETE
AS
    DELETE FROM dbo.Tags
    WHERE TagName IN (SELECT TagName FROM DELETED)
GO

Upvotes: 3

Related Questions