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

Slightly confused about your question. But you can do a LeftOuterJoin from your main table, To a table that it should supposedly have a foreign key. You can then use a Where statement to check for null values inside the connecting table.

Check here for outer joins : http://en.wikipedia.org/wiki/Join_%28SQL%29#Left_outer_join

You should also write up triggers to do all this for you when a record is deleted or set to false etc.

Answer 2

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

Answer 3

Let us we have parent table with the name Parent and it has at "id" field of any type and an "Active" field of the type bit. We have also a second Child table with his own "id" field and "fk" field which is the reference to the "id" field of the Parent table. Then you can use following statement:

DELETE Parent
FROM Parent AS p LEFT OUTER JOIN Child AS c ON p.id=c.fk
WHERE c.id IS NULL AND p.Active=0