In SQL Server 2008, there is a Primary table which is linked to three other child tables by 1 to many relationship. So, we are thinking of using Cascading delete in the primary table, so that all the records on the child table will be removed when record from primary table is deleted.
I am generally wary of cascaded deletes (and other automatic actions that could drop/damage data), either via triggers or ON <something> CASCADE. Such facilities are very powerful, but also potentially dangerous.
- So, is cascading delete a correct choice here?
It would certainly do what you are looking for it to do: remove related records when a parent record is removed, without you needing to implement any other logic to ensure that children get removed first therefore making your code more concise. All the actions will be wrapped in an implicit transaction so if something blocks the child deletes the whole operation is blocked, maintaining referential integrity with little or no extra coding effort.
Make sure that your use of cascaded deletes and other "behind the scenes" actions are well documented so future maintainers of the system are fully aware of it.
- When cascading detele should not be used?
It should not be used if you are paranoid like me! One key point to consider is the other developers who currently, or may in future, work on your code/database (hence the comment above about documenting any "hidden" behaviours).
It is quite common in my experience for inexperienced people to use DELETE then re-INSERT in order to update rows, especially when what they really want is a MERGE/UPSERT operation (update existing rows and create new ones where a row with a given key does not exist) and the DBMS doesn't support merge/upsert (or they are unaware of its support). Without cascaded actions this is perfectly safe (or will error when it threatens data integrity) but if someone does this for rows in a parent table where referring FKs have ON DELETE CASCADE set then related data will be deleted as a result of the initial delete and not replaced - so data is lost (not that even if the delete and subsequent insert are wrapped in explicit transactions, the cascade happens with the delete operation - it won't wait to see if the transaction replaces rows in the parent table in subsequent statements) and the cascade could continue through other relation ships (for instance: delete a senior supervisor, his team get deleted by cascade, his teams' teams get deleted by cascade, all the tracked records for all those people get deleted by cascade, ...). Without cascading enabled, you would just get an error here instead of the data being silently lost.
I guess the answer boils down to whether or not makes sense for your situation, it depends. For your situation, does it make sense for the rows in the 'child' table to remain if their corresponding 'primary' rows go? Would the data in the child table be meaningless without the parent? If so, then cascading delete would enforce referential integrity. You might want to keep child rows as a record, an archive of past activity (though potentially you could write these rows to another table specially for this purpose).
An example I use to illustrate this point is the doctor/patient relation. One doc can have many patients. A patient can only be a patient if they have a doctor. If the doc goes (leaves the practice) then something has to happen to the remaining patients. One possibility is that they get purged. Another is that a default value replaces the doc reference or they could be removed from the main table and placed somewhere else. Alternatively no activity occurs and the patients remain as they were, untouched, as though the doc was still present. It depends what you want to do.
From personal experience think carefully about the design of the database. This week I had to run a purge of orphaned records in a table which pointed to nowhere and was literally taking up space.
Using cascading delete is a matter of personal preference, like whether to name tables with plural names or not (Customer vs Customers).
I would prefer to never use cascading delete. Some database designs avoid deleting at all. There are few justifications to delete data from a database when disk space is so cheap. Some database designs set an additional field "IsDeleted" rather than physically delete data.
If you have to delete data then using stored procedures to manage this gives you more transparency and control. Your application can execute a stored procedure which deletes from the child then parent. You don't know how the business requirements will change over time so sp's will give you more versatility. That's my 2c.
