Q
Problem solve Get help with specific problems with your technologies, process and projects.

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:

Departments
2 Marketing
4 Accounting
6 Sales

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

  
Works
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


This was last published in May 2003

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close