2 Ways to Delete Rows from Parent and Child Tables in SQL Server when there’s a Foreign Key Relationship

When working with relational databases, it’s common to encounter situations where you need to delete data from both parent and child tables that are linked by foreign key relationships. This article explores two methods for accomplishing this task in SQL Server, providing detailed examples and explanations for each approach.

Setting Up the Example

Before we dive into the deletion methods, let’s set up an example schema with one parent table and two child tables:

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

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

-- Create the second Child table
CREATE TABLE Projects (
    ProjectID INT PRIMARY KEY,
    ProjectName VARCHAR(100),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

-- Insert some sample data
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES (1, 'IT'), (2, 'HR');
INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID) VALUES (1, 'Homer', 'Eastwood', 1), (2, 'Clint', 'Simpson', 2);
INSERT INTO Projects (ProjectID, ProjectName, DepartmentID) VALUES (1, 'Database Migration', 1), (2, 'Employee Onboarding', 2);

So just to be clear, the Departments table is the parent table, and the other two (Employees and Projects) reference it via their own respective foreign key. Each foreign key references the same column in the parent table (the DepartmentID column).

We’ve also inserted a small amount of data into the tables.

OK, so now that we have our schema and sample data, let’s explore two methods to delete data from these related tables.

The Challenge

The reason I wrote this article is because you may have tried to delete data from the parent table and gotten an error, due to data being in the child tables. Something like this:

DELETE FROM Departments WHERE DepartmentID = 1;

Output:

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

This occurred because I tried to delete data from the parent table when that table is being referenced by a foreign key constraint, and there’s data in the table using that foreign key.

Let’s look at two ways of overcoming this issue.

Method 1: Delete Child and Parent Data Separately

Our first method involves deleting the child data first, and then the parent data.

We also use a transaction to ensure that all deletions occur atomically. We don’t want anything to fail halfway through the operation leaving us with some data deleted and some remaining. Using a transaction ensures that either the whole thing works or the whole thing fails; nothing in between. So if it does fail, no data will be deleted. If it doesn’t fail, all data will be deleted (I mean all data specified in our delete statmements, of course).

BEGIN TRANSACTION;

BEGIN TRY
    -- Delete from child tables first
    DELETE FROM Projects WHERE DepartmentID = 1;
    DELETE FROM Employees WHERE DepartmentID = 1;
    
    -- Then delete from the parent table
    DELETE FROM Departments WHERE DepartmentID = 1;

    COMMIT TRANSACTION;
    PRINT 'Transaction committed successfully.';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'An error occurred. Transaction rolled back.';
    PRINT ERROR_MESSAGE();
END CATCH

Output:

(1 row affected)
(1 row affected)
(1 row affected)
Transaction committed successfully.

Explanation:

We start a transaction using BEGIN TRANSACTION. This ensures that either all of our delete operations succeed, or none of them do.
We use a TRY...CATCH block to handle any potential errors.
Inside the TRY block, we first delete from the child tables (Projects and Employees). It’s crucial to delete from the child tables first to avoid violating the foreign key constraints.
After deleting from the child tables, we delete from the parent table (Departments).
If all deletions are successful, we commit the transaction using COMMIT TRANSACTION.
If an error occurs at any point, the CATCH block is executed. It rolls back the transaction using ROLLBACK TRANSACTION, ensuring that no partial deletions occur.

This method is safe and ensures data integrity, but it requires us to manually specify the delete operations for each table involved.

Method 2: Dropping and Recreating the Foreign Key with the `CASCADE` Option

If you’re getting the 547 error shown previously, then your foreign keys are probably defined using the (default) NO ACTION option, which means that if we try to delete data in the parent table that is referenced by at least one child row, then an error is raised and the delete is rolled back.

There are various options that we can specify for this scenario when creating foreign keys. These include NO ACTION, SET NULL, SET DEFAULT, and CASCADE. Seeing as this article is about deleting the rows from both the parent and child tables, we’ll use the CASCADE option.

The ON DELETE CASCADE option specifies that any deletes from the parent table must cascade down to all child tables. When this is enabled, we don’t get an error when trying to delete data in the parent table. Instead, SQL Server takes care of all deleting all related data too.

If you do have ON DELETE CASCADE specified on all foreign keys, then deleting data shouldn’t be an issue – and you probably aren’t even reading this article 🙂

However, when we created the above tables for our examples, we didn’t use the CASCADE option. We used the default option (the NO ACTION option), which is why we got the error when we tried to delete data from the parent table.

If we want to change this, we can drop the existing foreign keys, then recreate them with the CASCADE option.

Let’s first find out the names of our foreign keys:

SELECT 
  name AS 'Constraint',
  OBJECT_NAME(parent_object_id) AS 'Table'
FROM sys.foreign_keys
WHERE OBJECT_NAME(referenced_object_id) = 'Departments';

Output:

Constraint                      Table    
------------------------------  ---------
FK__Employees__Depar__0E04126B  Employees
FK__Projects__Depart__10E07F16  Projects

The constraint names are FK__Employees__Depar__0E04126B and FK__Projects__Depart__10E07F16. These are system-generated names, because we didn’t provide a name when we created them. If you’re following along with these examples, your foreign keys will probably have a different name, so be sure to use the names that are returned in the Constraint column from the above query.

Now let’s use that information to construct our commands to drop the constraints and recreate them:

-- Step 1: Drop existing foreign key constraints
ALTER TABLE Employees DROP CONSTRAINT FK__Employees__Depar__0E04126B;
ALTER TABLE Projects DROP CONSTRAINT FK__Projects__Depart__10E07F16;

-- Step 2: Recreate foreign key constraints with CASCADE DELETE option
ALTER TABLE Employees ADD CONSTRAINT FK_Employees_DepartmentID 
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) ON DELETE CASCADE;

ALTER TABLE Projects ADD CONSTRAINT FK_Projects_DepartmentID 
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) ON DELETE CASCADE;

-- Step 3: Delete from the parent table (child records will be automatically deleted)
DELETE FROM Departments WHERE DepartmentID = 1;

Explanation:

We start by dropping the existing foreign key constraints.
We recreate the foreign key constraints with the ON DELETE CASCADE option. This tells SQL Server to automatically delete child records when a parent record is deleted. We also took this opportunity to provide a more user friendly name for the constraints.
We then delete data from the parent table (Departments). Because of the CASCADE option, SQL Server automatically deletes the corresponding records in the child tables.

This method relies on SQL Server to handle the deletions of child records automatically. However, it does involve modifying the table structure, which may not be desirable or possible in all situations.

We also have the option of dropping the CASCADE option after we’ve deleted the data:

-- Step 4: (Optional) Drop the CASCADE constraints and recreate the original ones
ALTER TABLE Employees DROP CONSTRAINT FK_Employees_DepartmentID;
ALTER TABLE Projects DROP CONSTRAINT FK_Projects_DepartmentID;

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_DepartmentID 
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);

ALTER TABLE Projects ADD CONSTRAINT FK_Projects_DepartmentID 
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);

The Result

Regardless of the method you use to delete the data, the end result should look like this:

SELECT * FROM Departments;
SELECT * FROM Employees;
SELECT * FROM Projects;

Result:

DepartmentID  DepartmentName
------------  --------------
2             HR            

EmployeeID  FirstName  LastName  DepartmentID
----------  ---------  --------  ------------
2           Clint      Simpson   2           

1 row(s) returned

ProjectID  ProjectName          DepartmentID
---------  -------------------  ------------
2          Employee Onboarding  2

We can see that there’s no longer an IT department, and there are no longer any IT employees or IT projects.

Database.Guide

2 Ways to Delete Rows from Parent and Child Tables in SQL Server when there’s a Foreign Key Relationship

Setting Up the Example

The Challenge

Method 1: Delete Child and Parent Data Separately

Explanation:

Method 2: Dropping and Recreating the Foreign Key with the `CASCADE` Option

Explanation:

The Result

More on Foreign Key Relationships

Setting Up the Example

The Challenge

Method 1: Delete Child and Parent Data Separately

Explanation:

Method 2: Dropping and Recreating the Foreign Key with the CASCADE Option

Explanation:

The Result

More on Foreign Key Relationships

Method 2: Dropping and Recreating the Foreign Key with the `CASCADE` Option