FK stopping a delete - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

FK stopping a delete

Author

Topic

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2012-06-14 : 05:14:32

Hi all,

Need to delete a row in a log shipping sys table,

DELETE sysdbmaintplans
WHERE Plan_name = 'Test'

But got this error,

Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__sysdbmain__plan___3D2915A8'. The conflict occurred in database 'msdb', table 'sysdbmaintplan_jobs', column 'plan_id'.
The statement has been terminated.

So I deleted the corresponding row as follows;

Delete sysdbmaintplan_jobs
Where plan_ID = '8AF2B576-1F6A-475C-99B0-C3CD8F0B30D4'

However when I rerun the original delete, the same error message comes up!

Please advise as this fix is necessary to allow new log shipping pairs to be created. Do I need to drop and re-establish the FK? If so, how?

Cheers,

JB

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-14 : 12:13:34

why are you deleting data directly from system table like this? you should be doing this modification from maintenance plan GUI

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-14 : 13:45:14

Never delete from the system tables for jobs, maintenance plans, or log shipping. Always use the GUI or use the system procedures to do deletes. I seriously corrupted my msdb one time doing the same thing you're trying to do and had to rebuild it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources