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
C# to communicate with the database.
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.
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