How to delete foreign key with lots of references

Question

Table B have several thousand records that references table A with a foreign key.

The application sends a request to delete the row from table A. It's important that the synchronous operation would be immediate and won't be at risk for timeouts.

If a cascade delete is used, and thus several thousands of records will be deleted as well, could it cause the deletion to take a long time? (not immediate).

Assuming that it could lead to a long deletion operation, what could be done as an alternative?

I've though perhaps to delete the record as deleted and to perform the actual deletion in some background process (where the referencing rows would be deleted before the parent), but this approach feels rather error prone (since all of the existing queries will need to reference this new markedAsDeleted column.

Any ideas?

Answer 1

If the only concern is timeout there are probably 3 ways to get around that.

1. Like referenced by Lennart, proper indexing should allow even the deletion of 1000's of rows to happen fairly quickly. Assuming you can run a query like below fairly quickly, a DELETE would only be negatively impacted by the number of indexes on [Table B] that have to be updated for each row removed.

SELECT [Table B].ID
FROM [Table B]
    INNER JOIN [Table A]
        ON [Table B].[Table A ID] = [Table A].ID
WHERE [Table A].ID = @Value

2. Extend out the command timeout on the operation that triggers this delete. The user would need to wait for it to complete, but they would be notified of a failure if there was one and would know for sure that the row (and all its child data) was removed.
3. (Full-disclosure, I dislike this option but it make serve your purposes if the above two don't help enough) You can also create a Delete Log table. Where you would store whatever records from [Table A] need to be removed. Your Delete operation on the end user application would be a simple Insert into this Delete Log table. You would then have a background process (through either an Automation tool or SSIS/Sql Agent) that takes that log and removes the associated Table B and Table A records. This would then have all the time it needs to remove the records in question. The main downside is all the records exist until the background routine Runs and then completes so the end user would need to understand that their records are not removed immediately.

Hopefully this helps.

Answer 2

The best solution for deletion is a cascade deletion. It is atomic from the client's point of view. And if something goes wrong, for example, we deleted 500 rows in the secondary table, but row 501 has its own restriction and deletion failed - the whole deletion is rolled back. So if you can define foreign key with ON DELETE CASCADE - do so.

The timing here is not really an option. It will take some time, true. And the more rows you have in the secondary table - the more time it will take to delete those rows. But this is an unavoidable delay. If you delete the rows yourself, before removing the record from the dictionary table, or use manually created triggers. You still need to remove the same amount of rows... So the only way to ensure speedy deletion is to have an index on the secondary table on the foreign key column.

If the timing is really an issue - the only other real solution is to not delete anything. Just have in the dictionary a flag field deleted and set it to true for "deletion". In this case, the deletion operation will be very fast, but you would need to take care not to use the "deleted" dictionary words (can be done by additional triggers).

But this approach has additional benefit of accountability, if your flag field is a pair of dates (start/end). Then you would be able to show that "TheObject had these set of attributes before revision of dictionary, and these set after"

And you of course can combine both approaches. Use the on cascade delete in the secondary tables. And use deleted flag field in the dictionary table. Use the flag field during the day, and do a an actual clean up during the night (or busy/not busy time periods if your system is 24/7).