Delete all records that do not have foreign key restrictions

Question

I have a SQL 2005 table with millions of rows in it that is hit by users all day and night. This table is referenced by 20 or so other tables with foreign key constraints. What I need to do on a regular basis is to 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 to do this without trying to remove each one at a time and letting it cause SQL errors for those that violate the constraints? It is also impossible to disable restrictions, and I cannot cause locks in the parent table for some significant amount of time.

Answer 1

If it is unlikely that inactive lines that are not connected will be linked, you can run (or even dynamically build 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 easily turn it into a DELETE. But many locks can be stored in a large delete, so you can put this in a table and then delete it in batches - the package should not be interrupted if the record was not connected.

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

You can also do this with left-handed connections, but then you (sometimes) have to de-cheat with DISTINCT or GROUP BY, and the execution plan is actually no better, and this does not contribute 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 2

We have a parent table called Parent and it has an id field of any type and an Active field of type bit . We also have a second Child table with its " id " and " fk " field, which is a reference to the " id " field of the Parent table. Then you can use the 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 

Answer 3

Slightly confused about your question. But you can make LeftOuterJoin from your main table, to the table in which it should have a foreign key. Then you can use the Where statement to check for null values ​​inside the join table.

Check out the external links here: http://en.wikipedia.org/wiki/Join_%28SQL%29#Left_outer_join

You must also write triggers to do all this for you when the record is deleted or set to false, etc.