Wednesday, December 8, 2010

Dilemma of delete actions- Restricted/Casecade+Restricted

To explain the difference b/w Restricted and Cascade+Restricted delete action, consider the following example:
Let's say you have tables CarTable and RentalTable, there is a one-to-many relationship between CarTable and RentalTable. Then if a record in CarTable is deleted and the delete action is:

None: No action on related records in RentalTable.

Cascade: All records in RentalTable related to the record being deleted in CarTable will also be deleted.

Restricted: The user will get a warning saying that the record in the CarTable cannot be deleted because transactions exist in table RentalTable. The user will be unable to delete the record in CarTable if one or more related records exist in the RentalTable.

Cascade + Restricted: This option is used when deleting records through more than two levels. Let's say another table existed that had a cascade delete action against CarTable and the CarTable had a Cascade + Restricted delete action against RentalTable. If the record in the top level table was about to be deleted it would also delete the related records in CarTable. In turn, all the records in RentalTable related to the records being deleted from CarTable would also be deleted (Cascade behaviour). If only a record in CarTable was about to be deleted, the user would get the same message when using the Restricted method (Restricted behaviour).

No comments: