CascadeDeleteJunctionTable.sql

USE master;
DROP DATABASE Experiment;

CREATE DATABASE Experiment;
USE Experiment;


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)
);

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'

SELECT *
FROM dbo.Posts

SELECT *
FROM dbo.PostTagJunction

SELECT * 
FROM dbo.Tags