CODEWITHSUNDEEP
core-data
Senseful
Senseful

Reputation: 91711

What are all the side effects of each cascade delete option for a one-to-many relationship?

Say I have two tables, Department and Employee. Each employee has exactly one department. Each department has one or more employees.

Is there any combination of cascade delete options I can use to enforce this rule without having to write special code?

In other words, here are the two rules:

  1. A department can only be deleted when it has no employees.
  2. When the last employee in a department gets deleted, the department should be deleted.

If not, then which set should I use in order to help me the most with enforcing this business logic?

Upvotes: 3

Views: 293

Answers (1)

Senseful
Senseful

Reputation: 91711

Say we have the following data in the table before each scenario is run:

Sales (Department)
  Amy (Employee)
  Bob (Employee)

Scenario 1: Cascade/Nullify

Employee --(Cascade)--> Department
Department --(Nullify)--> Employee

Delete Employee:

Deleting Bob will cause Sales to be deleted, and Amy to have a NULL department.

Amy

Delete Department:

If we start over and delete Sales this time, Amy and Bob are left in the database with NULL departments.

Amy
Bob

Scenario 2: Cascade/Deny

Employee --(Cascade)--> Department
Department --(Deny)--> Employee

Delete Employee:

Deleting Bob will raise an exception: The operation couldn’t be completed. (Cocoa error 1600.).

Delete Department:

If we start over and delete Sales this time, we get the exception: The operation couldn’t be completed. (Cocoa error 1600.).

Scenario 3: Cascade/Cascade

Employee --(Cascade)--> Department
Department --(Cascade)--> Employee

Delete Employee:

Deleting Bob will cause Sales to be deleted. As a result, Amy gets deleted as well.

(empty DB)

Delete Department:

If we start over and delete Sales this time, it will cause all employes to be deleted as well.

(empty DB)

Scenario 4: Nullify/Nullify

Employee --(Nullify)--> Department
Department --(Nullify)--> Employee

Delete Employee:

Deleting Bob will not change the rest of the database.

Sales
  Amy

Deleting Amy now will cause Sales to not have any Employees.

Sales

Delete Department:

If we start over and delete Sales this time, it will cause Amy and Bob to have NULL departments.

Amy
Bob

Scenario 5: Nullify/Deny

Employee --(Nullify)--> Department
Department --(Deny)--> Employee

Delete Employee:

Deleting Bob will not change the rest of the database.

Sales
  Amy

Deleting Amy now will cause Sales to not have any Employees.

Sales

Delete Department:

If we start over and delete Sales this time, it will cause an exception: The operation couldn’t be completed. (Cocoa error 1600.).

Scenario 6: Nullify/Cascade

Employee --(Nullify)--> Department
Department --(Cascade)--> Employee

Delete Employee:

Deleting Bob will not change the rest of the database.

Sales
  Amy

Deleting Amy now will cause Sales to not have any Employees.

Sales

Delete Department:

If we start over and delete Sales this time, it will delete all employees.

(empty DB)

Scenario 7: Deny/Nullify

Employee --(Deny)--> Department
Department --(Nullify)--> Employee

Delete Employee:

Deleting Bob will cause an exception: The operation couldn’t be completed. (Cocoa error 1600.).

Delete Department:

If we start over and delete Sales this time, it will cause Amy and Bob to have NULL departments.

Amy
Bob

Scenario 8: Deny/Deny

Employee --(Deny)--> Department
Department --(Deny)--> Employee

Delete Employee:

Deleting Bob will cause an exception: The operation couldn’t be completed. (Cocoa error 1600.).

Delete Department:

If we start over and delete Sales this time, it will cause an exception: The operation couldn’t be completed. (Cocoa error 1600.).

Scenario 9: Deny/Cascade

Employee --(Deny)--> Department
Department --(Cascade)--> Employee

Delete Employee:

Deleting Bob will cause an exception: The operation couldn’t be completed. (Cocoa error 1600.).

Delete Department:

If we start over and delete Sales this time, it will delete all the employees.

(empty DB)

Conclusion:

Therefore, it seems it is best to use scenario 5 (Nullify/Deny) and you will have to write custom code to delete a department after the last employee is deleted. This scenario will also ensure you don't accidentally delete a department too soon.

Upvotes: 11

Related Questions