The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id"
I was 'trying' to delete an obselete SQL Server Agent job until this nice error popped up:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop failed for Job 'Optimiser.Subplan_1'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3239.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Job&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
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. (Microsoft SQL Server, Error: 547)
The solution:
use msdb
go
select * from sysmaintplan_subplans
delete from sysmaintplan_subplans
where subplan_name = 'Subplan_1' -- 'Subplan_1' in my case is the name of the subplan which is the culprit here