Archiving question

[03Explorer]

I am by roll a SQL report analyst who has been tasked with writing a script to delete rows of data that meet a timestamp criteria. Pulling of data is done, but the issues is I had not done anything for deleting data. One of the database DBAs said it is my roll to write a script to delete data so I use 'cascade Delete' due to foreign keys. My first reaction was simply, I don't do deletes, but that does not work. I have researched and found some code that modifies constraints on Foreign Keys (FK). If I use the following code.

--Coverage_audit_logging (Foreign Keys = 2 )
--Key 1
  ALTER Table [uvpstg].[dbo].[COVERAGE_AUDIT_LOGGING]
  ADD CONSTRAINT fk_Coverage_Audit_ELEM_EXIT_DIM_ID
  FOREIGN KEY (ELEM_EXIT_DIM_ID)
  REFERENCES ELEMENT_EXIT_DIM (Elem_Exit_Dim_id) ON DELETE CASCADE

--Key 2
  ALTER Table [uvpstg].[dbo].[COVERAGE_AUDIT_LOGGING]
  ADD CONSTRAINT fk_Coverage_Audit_PHYSICIAN_DIM_ID
  FOREIGN KEY (PHYSICIAN_DIM_ID)
  REFERENCES PHYSICIAN_DIM (PHYSICIAN_DIM_ID) ON DELETE CASCADE

I should be able to delete (unsing delete from command) and the children to the foreign keys are automatically removed at the same time (using the code below)?

DELETE FROM [uvpstg].[dbo].[COVERAGE_AUDIT_LOGGING]
WHERE uu_seq_id in (Select * FROM #TempSeqIDs)

Where #TEmpSeqIDs is a temp table I created with the IDs to be removed)

** note I did not include in the delete from statement here the two tables I used in the Delete Cascade above, is that all that is needed to remove the records from all three tables?

 

[yelworcm]

Instead of messing around with the constraints (which may have unpredictable effects on the application connected to it), why not delete the appropriate rows from the child tables first, then delete rows from the main table? If you change the constraints, you can run this with no problem

delete coverage_audit_logging

and it will run like a champ. Your career, on the other hand.....