We have a database housekeeping operation that the SQL Server agent runs overnight (SQL Server 2005). Basically it compiles a table of record ID’s that are old enough to be deleted and then first deletes any child records before deleting the main record.
Recently after adding a new table dbo.IssuePrice, which has a foreign key reference to the dbo.Issue table, the housekeeping has been failing every night with the error: ‘The DELETE statement conflicted with the REFERENCE constraint "FK_IssuePrice_Issue". The conflict occurred in database "db", table "dbo.IssuePrice", column 'IssueId'. [SQLSTATE 23000] (Error 547) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.’
Seems straightforward enough but querying the database I can find no IssuePrice records for any of the Issue records being deleted. In fact the procedure still fails even when it does not have any IssueId’s in the housekeeping table to delete. However, if I run the procedure step by step manually it completes without any errors.
The following shows how the procedure works.
insert
into dbo.HousekeepingTarget (TableName, RecordIdentity)
select 'Issue', IssueId
from dbo.Issue
where datediff(day, RecallDate, @CutOff) > 0
/* <delete from various dependant tables> */
...
...
/* - deletes from over 30 tables in all */
...
delete
from dbo.IssuePrice
where IssueId in (select RecordIdentity from dbo.HousekeepingTarget where TableName = 'Issue')
/* delete issues */
delete
from dbo.Issue
where IssueId in (select RecordIdentity from dbo.HousekeepingTarget where TableName = 'Issue')
I have tried using transactions, moving the final delete statement to its own procedure, putting the IssuePrice delete higher up in the procedure, recreating the FK (which it allowed me to do so there can’t be any orphan records), and nothing seems to work. This is a procedure that has been running for years, the only change is the IssuePrice delete, which has been added in the same format as all the others.
Anyone got any ideas or had the same problem?