Today one of my team members was implementing a Change Control which required several jobs to be deleted and new maintenance jobs to be created. He got stuck with while deleting one of the jobs. When he tried deleting the job he got the below error.
Even executing the below query also resulted in the same error.
Msg 547, Level 16, State 0, Procedure sp_delete_job, Line 178 The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'. The statement has been terminated.
From the error message it is clear that while deleting this job there is a conflict in the foreign key defined on sysmainplan_subplans table in MSDB. While deleting the job through the GUI, it in turn executes sp_delete_job stored procedure. This stored procedure in turn deletes a row in msdb..sysjobs table among others. The error in this operation was due to the fact that msdb..sysmaintplan_subplans table has a foreign key defined on job_id column of msdb..sysjobs table.
But why the job is being referred in that table? This screenshot tells it all!
Wah! This job was created by a Maintenance Plan and the DBA was trying to delete this job without removing the subplan in the Maintenance Plan. Now it is very simple. Just delete the maintenance plan. This maintenance had only one subplan, hence it was deleted. If it had more than one subplan, deleting the relevant subplan (Subplan_1 in this case) would delete corresponding SQL Server Agent job.