SQL Server – Disadvantages of Using ON DELETE CASCADE

cascadedeleteforeign keysql server

Our application currently has "soft delete" built-in to most tables with a BIT "deleted" column.

There is now a requirement for "hard delete" functionality.

Are there any disadvantages, gotchas, or things to bear in mind when adding ON UPDATE CASCADE ON DELETE CASCADE to every single foreign key in the database?

Best Answer

Aside from the aforementioned 'oops' where you delete a record you didn't intend to and now a lot of associated records are gone as well, you probably simply can't cascade delete everything.

A thing to keep in mind when using cascades is that it can cause conflicts if there's multiple cascade paths. SQL Server and other DBMS will block the creation of the foreign key with the cascade.

If you're going to delete cascade everything, you'll almost definitely hit this problem and either have to rework relations or be selective in which foreigns key actually do get cascades and which ones don't.

Explanation with illustrated example (not my source) Link

One way to get around it is to either use procedures/'front end' code (do the delete order management in the application calling the DB), or use triggers. Just try to be consistent with it. If your data model allows you to not hit multiple cascade paths, and you're certain you don't mind the 'oops'. Then by all means use it.

If you're going to hit multiple cascade paths, my advice would be to make the implementated solution consistent. Having some cascade, some triggers, some through procedures doing the management. Just makes the code disorganized.

Related Solutions

Mysql foreign key cascade delete

I did a little more research and tested on my local database, found out that the child tables just need to cascade delete and it works from the top down perfectly.

You do this by adding the ON CASCADE DELETE option to the foreign keys where you want this to happen.

Sql-server – Slow delete caused by many foreign keys

Make sure that there are indexes on the constrained columns, since the dbms will do a lookup using those columns in the referencing tables.

You can try using this script, it generates an index creation script for any non-indexed column being referenced by a cascading constraints.

SELECT 'CREATE NONCLUSTERED INDEX IX_'+OBJECT_NAME(fk.parent_object_id)+'_'+c.name+' ON '+OBJECT_NAME(fk.parent_object_id)+'('+c.name+') WITH (ONLINE=ON)' 
        --, OBJECT_NAME(fk.referenced_object_id) AS referenced_tale,  cc.name
FROM 
    sys.foreign_keys fk
    INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
    INNER JOIN sys.columns cc ON fkc.referenced_object_id = cc.object_id AND fkc.referenced_column_id = cc.column_id
WHERE delete_referential_action_desc IN ('CASCADE', 'SET_NULL')
    AND NOT EXISTS
        (
        SELECT  1
        FROM
            SYS.index_columns ic 
            INNER JOIN sys.indexes i ON i.object_id = ic.object_id AND ic.index_id = i.index_id
        WHERE 
            1 = 1
            AND type_DESC IN ('CLUSTERED','NONCLUSTERED')
            AND ic.OBJECT_ID = c.object_id
            AND ic.column_id = c.column_id
            AND ic.is_included_column = 0
        )

Best Answer

Related Solutions

Mysql foreign key cascade delete

Sql-server – Slow delete caused by many foreign keys

Related Question