ON DELETE in a many-to-many relationship

The question I have deals with data integrity. Suppose the Accounting department is wiped out due to restructuring of the company, making the database state inconsistent since some employees were working for the Accounting department before. How would you prevent such a situation by declaring database integrity statements when the database is created? I want to set up my database so that when the Accounting department is wiped out, all the records of the employees in a corresponding Works table who were working for the Accounting department are removed as well, without removing employee records the Employee table. I am trying find the solution without using triggers.

Also, dealing with this same type of question, I am aware of the function and use of triggers but cannot figure out how to use a trigger to reassign all employees under Accounting to the Sales department automatically when the records of the employees are removed from the Works table.

Let's look at some sample data to help visualize the relationships:

2 Marketing
4 Accounting
6 Sales

101 Joe
103 Adam
105 Bill
107 Ted
109 Mary
111 Fred

2 103
4 101
4 107
6 105
6 111

The integrity constraint you're looking for is ON DELETE. The Works table has two foreign keys, one to the Departments table, the other to the Employees table. When you declare a foreign key, you can declare an ON DELETE constraint, and you have several options.

If you declare ON DELETE RESTRICT (which is the default) for the foreign key to Departments, then you will not be allowed to delete Accounting as long as there are rows in the Works table with a foreign key to Accounting (the Works rows for Joe and Ted). You would have to delete those Works rows first.

ON DELETE CASCADE automates this process for you. Delete Accounting, and the related rows in Works are deleted automatically. Sweet.

However, with both of the above approaches, you now have no way of knowing whom to reassign to Sales. You could run a query on Employees, to find any employees who are not assigned to a department, but this isn't necessarily the right solution, since, for example, it would also reassign Mary, who's supposed to go back to Marketing once she returns from maternity leave.

Another option is ON DELETE SET NULL, but this is not supported by all databases. This option would let you delete Accounting, after which the related rows in Works would have nulls in the department foreign key. You might then update the Works table and set the department foreign key to 7 for any rows where it's null. (Just don't delete another department until after Joe and Ted have been reassigned!) Some would argue that this solution is unacceptable, because it doesn't make sense to have rows in Works without a department, even if only temporarily. I would agree with this assessment.

The best option is use ON DELETE RESTRICT, and simply reassign the employees before deleting Accounting. In other words, update Works and set the department foreign key to 7 for any rows where it's 4. Then you can safely delete Accounting afterwards. I like this solution because it's simple, effective, requires no additional queries, and allows the database to stay consistent at all times.

Oh, and triggers are not required for any of the above. Reassigning and deleting is done with UPDATE and DELETE queries.

For More Information

Dig Deeper on Oracle and SQL