By default, if we try to delete a row in the parent table of a referential relationship in SQL Server, we’ll get an error. That’s because the default action is NO ACTION. This means that the delete doesn’t happen, and an error is raised.

But we’re not quite doomed yet. We can use the ON DELETE CASCADE option to ensure that the delete operation does happen, and that no error is returned. This option automatically deletes related records in the child table when a record in the parent table is deleted.

If we’re going to use this option, we need to define it when creating the foreign key. That’s because ON DELETE CASCADE is an optional argument that we can provide when creating the foreign key.

How It Works

When you create a foreign key with ON DELETE CASCADE:

• The child table’s foreign key column(s) reference the primary key column(s) of the parent table (as with any referential relationship).
• If a record in the parent table is deleted, all corresponding records in the child table are automatically deleted.

This maintains referential integrity without requiring manual deletion of child records. It’s particularly useful in scenarios where child records should not exist without their parent record.

Without this option, we would need to delete the child rows first, then delete the parent rows. Otherwise we’d get an error.

This option also works if there are multiple child tables. All related data in all child tables are deleted, allowing the data in the parent table to be deleted.

Example

Let’s create two tables to demonstrate ON DELETE CASCADE:

-- Create the Parent table
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
);

-- Create the Child table with ON DELETE CASCADE
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
        ON DELETE CASCADE
);

Now, let’s insert some sample data and select it:

-- Insert data into Departments
INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES (1, 'IT'), (2, 'HR'), (3, 'Sales');

-- Insert data into Employees
INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID)
VALUES (1, 'Leesa', 'Pratchit', 1),
       (2, 'Jock', 'Lee', 1),
       (3, 'Dave', 'Cannard', 2),
       (4, 'Margaret', 'Nguyen', 3);

-- Select all rows that we just inserted
SELECT * FROM Employees;

Output:

EmployeeID  FirstName  LastName  DepartmentID
----------  ---------  --------  ------------
1           Leesa      Pratchit  1           
2           Jock       Lee       1           
3           Dave       Cannard   2           
4           Margaret   Nguyen    3           

So we’ve got four employees across three departments.

Delete With CASCADE

To see ON DELETE CASCADE in action, let’s delete a department:

-- Delete the IT department
DELETE FROM Departments WHERE DepartmentID = 1;

-- Check the Department table
SELECT * FROM Departments;

-- Check the Employees table
SELECT * FROM Employees;

Output:

DepartmentID  DepartmentName
------------  --------------
2             HR            
3             Sales         


EmployeeID  FirstName  LastName  DepartmentID
----------  ---------  --------  ------------
3           Dave       Cannard   2           
4           Margaret   Nguyen    3           

We can see that the IT department has been deleted from the Departments table, and the two IT employees have been deleted from the Employees table.

Delete using the Default (NO ACTION)

To fully appreciate the CASCADE option, suppose we had created the table using the default option (which is NO ACTION). When we tried to delete data in the parent table that was referred to by at least one foreign key row, we would have received an error like this:

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK__Employees__Depar__2116E6DF". The conflict occurred in database "test", table "dbo.Employees", column 'DepartmentID'.

As mentioned, this is the default option, so if we don’t explicitly choose ON DELETE CASCADE or another ON DELETE option, then we would see this error.

All is not lost though. If we see this error, we can still proceed by deleting the child data first, then moving on to the parent data. If we do this, we should wrap it in a transaction in case anything goes wrong. Using a transaction will ensure that the whole thing is rolled back in the event that it can’t complete the transaction. In other words, it won’t leave some rows deleted and others undeleted.

Considerations

Before implementing the ON DELETE CASCADE option on your foreign keys, here are some things to consider:

• Performance: ON DELETE CASCADE can impact performance on large tables or complex relationships (see Adventures In Foreign Keys 3: Why Cascading Deletes Perform Slowly over at Brent Ozar’s blog for an interesting example).
• Data Loss: Be cautious, as it can lead to unintended data loss if not properly managed.
• Circular References: Avoid circular CASCADE paths, as they can cause errors (a table cannot appear more than one time in a list of all the cascading referential actions).
• Triggers: ON DELETE CASCADE can’t be defined if an INSTEAD OF trigger ON DELETE already exists on the table.
• Updates: We can also define CASCADE to update operations. That is, we can define our foreign keys with ON UPDATE CASCADE to ensure that any change to data in the parent table is also updated in the child tables.

Conclusion

The ON DELETE CASCADE option can be handy for maintaining referential integrity, particularly when it comes time to deleting related data. But as with anything, it has its pros and cons, so it pays to be mindful of these when deciding whether or not to use this option.

And as mentioned, if we need to related delete data that doesn’t use the ON DELETE CASCADE option, we can try deleting the child data first, followed by the parent data.