Delete all records that have no foreign key constraints

Question

I have a SQL 2005 table with millions of rows in it that is being hit by users all day and night. This table is referenced by 20 or so other tables that have foreign key constraints. What I am needing to do on a regular basis is delete all records from this table where the "Active" field is set to false AND there are no other records in any of the child tables that reference the parent record. What is the most efficient way of doing this short of trying to delete each one at a time and letting it cause SQL errors on the ones that violate constraints? Also it is not an option to disable the constraints and I cannot cause locks on the parent table for any significant amount of time.

Answer 1

If it's not likely that inactive rows which are not linked will become linked, you can run (or even dynamically build, based on the foreign key metadata):

SELECT k.*
FROM k WITH(NOLOCK)
WHERE k.Active = 0
AND NOT EXISTS (SELECT * FROM f_1 WITH(NOLOCK) WHERE f_1.fk = k.pk)
AND NOT EXISTS (SELECT * FROM f_2 WITH(NOLOCK) WHERE f_2.fk = k.pk)
...
AND NOT EXISTS (SELECT * FROM f_n WITH(NOLOCK) WHERE f_n.fk = k.pk)

And you can turn it into a DELETE pretty easily. But a large delete could hold a lot of locks, so you might want to put this in a table and then delete in batches - a batch shouldn't fail unless a record got linked.

For this to be efficient, you really need to have indexes on the FK columns in the related tables.

You can also do this with left joins, but then you (sometimes) have to de-dupe with a DISTINCT or GROUP BY and the execution plan isn't really usually any better and it's not as conducive to code-generation:

SELECT k.*
FROM k WITH(NOLOCK)
LEFT JOIN f_1 WITH(NOLOCK) ON f_1.fk = k.pk
LEFT JOIN f_2 WITH(NOLOCK) ON f_2.fk = k.pk
...
LEFT JOIN f_n WITH(NOLOCK) ON f_n.fk = k.pk
WHERE k.Active = 0
    AND f_1.fk IS NULL
    AND f_2.fk IS NULL
    ...
    AND f_n.fk IS NULL