Go to next record when there is a constraint issue.

Question

I am trying to automate purging data from two information tables.  Table2 depends on Table1.  Both tables have timestamp columns.  I can remove the last 6 months of data from Table2, however when I try for Table1 I get a constraint issue.

Table2 has multiple rows associated to one row in Table 1.  These multiple rows in Table2 can have timestamps that do not fall within the 6 month criteria.  For example, Table1 can have a timestamp of 2012-05-31 12:35:56.963 and Table2 can have a timestamp of 2012-09-08 12:13:22.954.  So when I try to delete from Table1 I get a constraint issue.  I know I don't want to delete this record since it would create an orphaned record in Table2.

Is there a way for me to setup my DELETE statement so I will go to the next row when it hits a constraint issue?  Thereby leaving that row, but allowing me to delete the other rows that do not have a constraint issue.

SELECT L.ID, L.Table1Date, R.Table2Date
FROM database.dbo.Table1 L
	JOIN database.dbo.Table2 R
		ON L.ID = R.ID 
		AND L.Type = R.Type
WHERE DATEADD(DAY,-180,GETDATE()) > L.Table1Date

Answer 1

You can set cascading delete. Here is how to:

You can set the Delete Rule to Cascade for the foreign key. In SQL Server Management Studio, Click on your table name and find the Keys and expand i;, right click on the foreign key name in your key collection  and click Modify>> under INSERT and UPDATE specification to see the Delete Rule and Update Rule. The default value is No Action for Delete Rule. You need to change the value to Cascade.

Answer 2

Delete From T1
From database.dbo.Table1 T1
Where DateAdd(day, -180, GetDate()) > T1.Table1Date
  And Not Exists(Select * From database.dbo.Table2 T2
      Where T1.ID = T2.ID And T1.Type = T2.Type);

That way you don't attempt to delete any rows in Table1 which still have corresponding rows in Table2.

As with any update, carefully test and be sure you have a good backup before running against a production database.

Tom