If you’re getting SQL Server error 547 that reads something like “The DELETE statement conflicted with the REFERENCE constraint “FK_Projects_DepartmentID”. The conflict occurred in database “test”, table “dbo.Projects”, column ‘DepartmentID’“, you’re probably trying to delete data from a parent table of a foreign key relationship.

Example of Error

Here’s an example of code that produces the error:

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_DepartmentID". 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.

This error will happen whenever you try to delete data from a parent table when the child table has ON DELETE NO ACTION specified in its foreign key (and it has at least one row that references the parent table). This is the default option, so unless another option was explicitly set for the foreign key, then it will raise the above 547 error whenever you try to delete data from the parent table that’s referenced by at least one row in a foreign key.

Solution

One way to fix this error is to delete the child data first, before deleting the parent data:

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.

In this case, we had two child tables referencing the parent table. So we deleted data from those first, followed by the data in the parent table. Doing it this way ensures that by the time we delete the data in the parent table, there are no child tables referencing it, and therefore we don’t get the error.

We also used a transaction to ensure that all deletions occurred atomically – we don’t want it to fail halfway through the operation leaving us with some data deleted and some remaining.

Another Option

Another way is to drop and recreate the foreign key constraints so that they’re defined with an option other than the ON DELETE NO ACTION option. This might be a preferred option if you anticipate that you’ll need to delete referential data again in the future.

Therefore, we could do the following:

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

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

In this case we recreated the foreign keys with ON DELETE CASCADE. What this means is that, whenever we delete data from the parent table, SQL Server will automatically delete data from any child tables that reference it. Therefore we won’t need to delete data from the child tables first, like we did in the first solution.

SQL Server also provides a couple of other options in addition to the CASCADE option:

• We can use ON DELETE SET NULL to set all the values that make up the foreign key to NULL.
• Or we could use ON DELETE SET DEFAULT to set the values that make up the foreign key to their default value (or NULL if there’s not explicit default value).