Skip to main content

Could Not Drop Object Referenced by Foreign Key Constraint

Symptom​

When you run the SP_DropTables job, the job fails and prompts the following error:

Could not drop object %table_name% because it is referenced by a foreign key constraint.

Cause​

The affected tables reference the foreign key constraint preventing the SP_DropTables job from running correctly.

Resolution​

Manually release the constraints for the affected tables.

  1. In SQL Server Management Studio, run the following line to output associated foreign key constraints. The example shows the SA_SPAC_ActivityEvents table—replace it with the table mentioned in the error message.
EXEC sp_fkeys 'SA_SPAC_ActivityEvents'
  1. The results will contain the FKTABLE_NAME column—run the following line to release the corresponding constraint. The example shows the SA_SPAC_ActivityEvents foreign key. Run the line with each FKTABLE_NAME specified in the results.
DROP TABLE SA_SPAC_GroupMembershipChanges
  1. After releasing the constraints, rerun the SP_DropTables job.