3

Suppose that I have many tables linked by a chain of ON DELETE CASCADE foreign keys. To the best of my knowledge and experience, deleting from one table gives me no notice at all that I have in fact affected multiple tables. For example, if deleting one row from table1 causes 500 rows to be deleted from table2 then all that SSMS will tell me is

(1 row affected)

Is there any way to know how many tables/rows my DELETE has actually changed? It seems as if, in theory, I could delete one row from one table in a sufficiently evil database and unknowingly delete 99% of the database's data.

2
  • 1
    Do you want to know which tables could be affected, or do you want to know how many rows in each of those table were actually affected? Commented Jan 29 at 3:54
  • @Charlieface Don't worry. The answers given are already sufficient.
    – J. Mini
    Commented Jan 29 at 15:51

3 Answers 3

4

If you’re just generally curious, look at the query plan. It will show all the tables involved.

If you’re looking to log or audit them, that’s a different story. You would need to use triggers, or some brand of auditing.

2

You could get this information out of the Actual query plan.

If you want to parse the query plan to find out how many rows were actually affected, you can do it like this.

WITH XMLNAMESPACES (
    DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
SELECT
  x2.obj.value('@Database', 'sysname'),
  x2.obj.value('@Schema', 'sysname'),
  x2.obj.value('@Table', 'sysname'),
  x1.relop.value('(RunTimeInformation/RunTimeCountersPerThread/@ActualRows)[1]', 'int')
FROM @queryPlan.nodes('//RelOp [@LogicalOp = "Delete"]') x1(relop)
CROSS APPLY x1.relop.nodes('Update/Object') x2(obj);

db<>fiddle

If you instead want to know which tables could be affected from a delete on a certain table, you can use the following query on the system catalogs.

WITH fks AS (
    SELECT fk.referenced_object_id
    FROM sys.tables t
    JOIN sys.foreign_keys fk ON fk.parent_object_id = t.object_id
    WHERE t.object_id = OBJECT_ID(N'dbo.SomeTable')
      AND fk.delete_referential_action = 1  -- cascade

    UNION ALL

    SELECT fk.referenced_object_id
    FROM fks
    JOIN sys.foreign_keys fk ON fk.parent_object_id = fks.referenced_object_id
    WHERE fk.delete_referential_action = 1  -- cascade
      AND fk.parent_object_id <> fk.referenced_object_id
)
SELECT
  s.name AS schema_name,
  t.name AS table_name
FROM fks
JOIN sys.tables t ON t.object_id = fks.referenced_object_id
JOIN sys.schemas s ON s.schema_id = t.schema_id;
2

... I could delete one row from one table in a sufficiently evil database and unknowingly delete 99% of the database's data.

Absolutely correct.

One of the biggest threats to our databases is the "Oops!" moment.
For example, someone/something firing off the wrong bit of SQL that results in devastation (if you're lucky; at least that's easy to spot) or on-going / progressive "weird" system behaviour (which is far more likely). Accidentally deleting the "Company" record at the "top" of the Finance system (thereby effectively "erasing" your whole company financially) might be categorised as such an "Oops!" moment (although, in the UK, we'd probably call that a "P45" moment).

Foreign Keys are there to protect your data.
To guarantee that, say, every Order Line has a parent Order.
The fact that these SQL constructs have also been imbued with these abilities to change, or delete, related data is, IMHO, a mistake.

Primary Keys should never change.
They should be allocated / generated when a record is first created and should remain, unchanged, until that record is finally and permanently destroyed. If that's the case, why would ever need "ON UPDATE CASCADE"?

Destruction of data should be controlled.
As you've suggested, "nested" cascades could do hideous amounts of damage, causing system outages, your having to exercise your Database Disaster Recovery plan, you name it. It's high impact and expensive.
So is "ON CASCADE DELETE" really such a good idea?

Now, OK, there might be some "less important" tables that you might want to apply this to but that needs to be a conscious decision, reached after discussion and agreement that you this really is the best way to manage those tables.

Anybody putting "ON ... CASCADE" clauses on their foreign key clauses by default is just loading their database with dynamite.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.