One of the neat features in SQL Server is the ability to set up cascading deletes on foreign keys. Instead of writing a bunch of manual DELETE statements to keep related tables in sync, you can let the database handle it for you. This is especially useful when you’re working with parent-child relationships, where deleting a parent should also remove all of its children automatically.
How Cascading Deletes Work
When you set up a foreign key relationship with ON CASCADE DELETE, SQL Server automatically deletes all child records whenever their parent record gets deleted. This happens in a single transaction, so either everything gets deleted successfully or nothing gets deleted at all.
An important point to remember is that cascading deletes follow the relationship chain. If Table A cascades to Table B, and Table B cascades to Table C, then deleting a record from Table A will automatically delete related records from both Table B and Table C.
Setting Up Cascading Deletes
You can enable cascading deletes when creating a foreign key constraint by adding the ON DELETE CASCADE clause. Here’s the basic syntax:
ALTER TABLE ChildTable
ADD CONSTRAINT FK_ChildTable_ParentTable
FOREIGN KEY (ParentId) REFERENCES ParentTable(Id)
ON DELETE CASCADE;Example
Here’s an example to demonstrate. We’ll use a gaming platform database in order to demonstrate how ON DELETE CASCADE works in a parent-child relationship.
Create the database:
-- Create the main Games table
CREATE TABLE Games (
GameId INT IDENTITY(1,1) PRIMARY KEY,
GameName NVARCHAR(100) NOT NULL,
Genre NVARCHAR(50) NOT NULL,
ReleaseYear INT NOT NULL,
Publisher NVARCHAR(100) NOT NULL
);
-- Create PlayerSessions table with cascading delete
CREATE TABLE PlayerSessions (
SessionId INT IDENTITY(1,1) PRIMARY KEY,
GameId INT NOT NULL,
PlayerId NVARCHAR(50) NOT NULL,
SessionStart DATETIME2 NOT NULL,
SessionEnd DATETIME2 NULL,
ScoreAchieved INT DEFAULT 0,
CONSTRAINT FK_PlayerSessions_Games
FOREIGN KEY (GameId) REFERENCES Games(GameId)
ON DELETE CASCADE
);
-- Create Achievements table with cascading delete
CREATE TABLE Achievements (
AchievementId INT IDENTITY(1,1) PRIMARY KEY,
SessionId INT NOT NULL,
AchievementName NVARCHAR(100) NOT NULL,
AchievementType NVARCHAR(50) NOT NULL,
PointsEarned INT NOT NULL,
UnlockedAt DATETIME2 DEFAULT GETDATE(),
CONSTRAINT FK_Achievements_PlayerSessions
FOREIGN KEY (SessionId) REFERENCES PlayerSessions(SessionId)
ON DELETE CASCADE
);Populate the database with test data:
-- Insert sample games
INSERT INTO Games (GameName, Genre, ReleaseYear, Publisher) VALUES
('Quantum Realms', 'RPG', 2023, 'Nebula Studios'),
('Speed Circuit', 'Racing', 2022, 'Velocity Games'),
('Mystic Towers', 'Strategy', 2021, 'Ancient Path Interactive'),
('Neon Fighters', 'Action', 2023, 'Chrome Entertainment'),
('Puzzle Nexus', 'Puzzle', 2020, 'Mind Bender Games');
-- Insert player sessions
INSERT INTO PlayerSessions (GameId, PlayerId, SessionStart, SessionEnd, ScoreAchieved) VALUES
(1, 'alex92', '2025-01-15 14:30:00', '2025-01-15 16:45:00', 15420),
(1, 'alex92', '2025-01-16 19:20:00', '2025-01-16 21:10:00', 18750),
(1, 'sarah_k', '2025-01-15 20:15:00', '2025-01-15 22:30:00', 12890),
(2, 'mike_z', '2025-01-14 13:45:00', '2025-01-14 14:20:00', 8940),
(2, 'alex92', '2025-01-17 16:30:00', '2025-01-17 17:15:00', 11250),
(3, 'sarah_k', '2025-01-13 10:00:00', '2025-01-13 12:30:00', 7650),
(3, 'jenny_x', '2025-01-18 15:45:00', NULL, 5430),
(4, 'mike_z', '2025-01-12 21:00:00', '2025-01-12 22:45:00', 9870),
(5, 'jenny_x', '2025-01-19 11:20:00', '2025-01-19 12:00:00', 4560);
-- Insert achievements
INSERT INTO Achievements (SessionId, AchievementName, AchievementType, PointsEarned) VALUES
(1, 'First Quest Complete', 'Story', 100),
(1, 'Level 10 Reached', 'Progression', 50),
(2, 'Perfect Combat Chain', 'Combat', 75),
(2, 'Rare Item Found', 'Discovery', 25),
(2, 'Speed Runner', 'Challenge', 150),
(3, 'Exploration Master', 'Discovery', 80),
(4, 'Track Record', 'Racing', 120),
(5, 'Lap Perfectionist', 'Racing', 90),
(6, 'Strategic Mind', 'Strategy', 110),
(8, 'Combo Master', 'Combat', 85),
(8, 'Boss Defeated', 'Combat', 200),
(9, 'Pattern Solver', 'Puzzle', 60);Before we go ahead and test the cascading delete action, let’s take a look at some of the existing data:
-- Check current data counts
SELECT
(SELECT COUNT(*) FROM Games) as TotalGames,
(SELECT COUNT(*) FROM PlayerSessions) as TotalSessions,
(SELECT COUNT(*) FROM Achievements) as TotalAchievements;
-- Look at data for Quantum Realms specifically (we will delete this game later)
SELECT g.GameName, COUNT(ps.SessionId) as SessionCount
FROM Games g
LEFT JOIN PlayerSessions ps ON g.GameId = ps.GameId
WHERE g.GameName = 'Quantum Realms'
GROUP BY g.GameName;
-- Check achievements linked to Quantum Realms sessions
SELECT ps.SessionId, COUNT(a.AchievementId) as AchievementCount
FROM PlayerSessions ps
LEFT JOIN Achievements a ON ps.SessionId = a.SessionId
WHERE ps.GameId = 1
GROUP BY ps.SessionId;Output:
TotalGames TotalSessions TotalAchievements
---------- ------------- -----------------
5 9 12
1 row(s) returned
GameName SessionCount
-------------- ------------
Quantum Realms 3
1 row(s) returned
SessionId AchievementCount
--------- ----------------
1 2
2 3
3 1
3 row(s) returned
We were mainly focused on the “Quantum Realms” game because that’s the one we’re going to delete. We can see that it currently has had 3 player sessions (recorded in the PlayerSessions table), and we can see the achievements have been recorded in the Achievements table.
Test the Cascading Delete
Now let’s delete the “Quantum Realms” game and watch the cascade effect:
DELETE FROM Games WHERE GameName = 'Quantum Realms';Now run the previous queries again:
-- Check current data counts
SELECT
(SELECT COUNT(*) FROM Games) as TotalGames,
(SELECT COUNT(*) FROM PlayerSessions) as TotalSessions,
(SELECT COUNT(*) FROM Achievements) as TotalAchievements;
-- Look at data for Quantum Realms specifically (we will delete this game later)
SELECT g.GameName, COUNT(ps.SessionId) as SessionCount
FROM Games g
LEFT JOIN PlayerSessions ps ON g.GameId = ps.GameId
WHERE g.GameName = 'Quantum Realms'
GROUP BY g.GameName;
-- Check achievements linked to Quantum Realms sessions
SELECT ps.SessionId, COUNT(a.AchievementId) as AchievementCount
FROM PlayerSessions ps
LEFT JOIN Achievements a ON ps.SessionId = a.SessionId
WHERE ps.GameId = 1
GROUP BY ps.SessionId;Result:
TotalGames TotalSessions TotalAchievements
---------- ------------- -----------------
4 6 6
1 row(s) returned
0 row(s) returned
0 row(s) returned
When we deleted the “Quantum Realms” game, SQL Server automatically removed the game record, all three related player sessions, and all five achievements that were tied to those sessions. We didn’t need to write separate DELETE statements for the child tables.
Benefits and Considerations
Cascading deletes offer several advantages. They maintain data consistency automatically, reduce the amount of cleanup code you need to write, and ensure that delete operations happen within a single transaction. This means you won’t end up with orphaned records cluttering your database.
However, cascading deletes can be dangerous if you’re not careful. They can lead to accidental mass deletions if your foreign key relationships aren’t designed thoughtfully. Always test your cascading delete behavior thoroughly, especially in complex database schemas with multiple levels of relationships.
Performance Considerations
Large cascading deletes can impact database performance since SQL Server needs to identify and remove all related records. If you’re dealing with tables containing millions of records, consider implementing soft deletes (using a status flag) instead of physical deletes, or break large delete operations into smaller batches.
Alternative Approaches
If cascading deletes seem too aggressive for your use case, SQL Server offers other options:
ON DELETE SET NULL: Sets foreign key values toNULLwhen parent record is deletedON DELETE SET DEFAULT: Sets foreign key values to their default valueON DELETE NO ACTION: Enforces that if a deletion would result in orphaned records in the child table/s, the deletion operation is prohibited, and an error is raised. This is the default behavior. In other words, if we don’t specify anyON DELETEaction, this is the one that’s used. This option enables us to manually delete child records if required. In this case we’d need to write explicit code to handle related record cleanup.
The choice depends on your business requirements and how you want to handle data relationships when parent records are removed.
Checking Existing Constraints
You can query system views to see which foreign keys in your database have cascading delete enabled:
SELECT
fk.name AS ForeignKeyName,
tp.name AS ParentTable,
tc.name AS ChildTable,
fk.delete_referential_action_desc AS DeleteAction
FROM sys.foreign_keys fk
JOIN sys.tables tp ON fk.referenced_object_id = tp.object_id
JOIN sys.tables tc ON fk.parent_object_id = tc.object_id
WHERE fk.delete_referential_action = 1;Output:
ForeignKeyName ParentTable ChildTable DeleteAction
------------------------------ -------------- -------------- ------------
FK_PlayerSessions_Games Games PlayerSessions CASCADE
FK_Achievements_PlayerSessions PlayerSessions Achievements CASCADE
Here, we narrowed the results down to those foreign keys that have ON DELETE CASCADE enabled. We determined this by checking the delete_referential_action column in the foreign_keys view. A value of 1 in this column means that it has ON DELETE CASCADE enabled. This view also has a delete_referential_action_desc column, which contains a description of the action (in this case CASCADE).
A query like this helps you audit your database and understand which relationships will trigger cascading deletes, which can be very useful for maintaining data integrity and avoiding surprises during delete operations.
Dealing with Updates
It’s also possible to specify an action to take place when records are updated (as opposed to deleted). For a cascade, you’d simply use ON UPDATE CASCADE. So you could have ON DELETE CASCADE to handle deletions, and ON UPDATE CASCADE to handle updates.