Change dependent records on delete in SQL-CodePudding

I'm adding a new job category to a database. There are something like 20 tables that use jobCategoryID as a foreign key. Is there a way to create a function that would go through those tables and set the jobCategoryID to NULL if the category is ever deleted in the parent table? Inserting the line isn't the issue. It's just for a backout script if the product owners decide at a later date that they don't want to keep the new job category on.

CodePudding user response：

You need some action. First of all update the dirty records to NULL. For each table use:

Update parent_table
Set jobCategoryID  = NULL
WHERE jobCategoryID NOT IN (select jobCategoryID FROM Reerenced_tabble)

Then set delete rule of foreign keys to SET NULL.

If you care about performance issue, follow the below instruction too. When you have foreign key but dirty records it means, that these constraints are not trusted. It means that SQL Optimizer can not use them for creating best plans. So run these code to see which of them are untrusted to optimizer:

Select * from sys.foreign_keys Where is_not_trusted = 1

For each constraint that become in result of above code edit below code to solve this issue:

ALTER TABLE Table_Name WITH CHECK CHECK CONSTRAINT FK_Name