An option that has been around for a long time in the Oracle Database is ‘ON DELETE CASCADE’ on a foreign key. I almost never use this option, because the front-end takes care of this. But, since there are going to be more types of front-ends to the same database it’s a good idea to put this constraint on the database. In fact, it’s always best to put all possible code, constraints and model information on the database. First of all you can ‘enforce’ the business rules, regardless of the front-end used. But you can also use this information to generate code, based on the information in the database, for example using Qnxo.

Now, how does this cascading delete work. You might already know, but I want to write this down, to get my thoughts organized on this subject. And I invite you to respond if you have additions, found errors etc.

Suppose you have two tables, emp and dept. Emp has a foreign key to dept, that means that every Employee belongs to a department. If you make this key ON DELETE CASCADE then the employees connected to a department will also be removed if the department is deleted. In normal words: if you remove a department, then all the employees working for that department will also be removed. Seems a bit like the real life issues. When a company kills a department, all of its employees will be laid off. There is another option, and that is to set the foreign key to null, which means the connection with the parent (dept) is removed, but the child (emp) is not being removed. You can then write code to find all the employees that are not currently connected to a department and assign them to a different department.

Some links to this subject:

• Oracle/PLSQL: Foreign Keys with cascade delete
• Mutating Table Problems with DELETE CASCADE