on delete cascade

The use of a referential integrity constraint is to enforce that each child record has a parent.

SQL> CREATE TABLE DEPT
2 (DEPTNO NUMBER PRIMARY KEY,
3 DNAME VARCHAR2(10)) ;

Table created.

SQL> CREATE TABLE EMP
2 (EMPNO NUMBER PRIMARY KEY,
3 ENAME VARCHAR2(10),
4 DEPTNO NUMBER
5 CONSTRAINT EMP_DEPT_FK
6 REFERENCES DEPT(deptno));

Table created.

SQL> INSERT INTO DEPT(deptno,dname) VALUES
2 (50,'CREDIT');

1 row created.

SQL> INSERT INTO EMP(EMPNO,ENAME,DEPTNO) VALUES
2 (9999,'JOEL',50);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> DELETE DEPT WHERE DEPTNO=50;
DELETE DEPT WHERE DEPTNO=50
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.EMP_DEPT_FK) violated
- child record found

I cannot delete this department, because the department is not empty. Fortunately ❗

Let’s redefine the constraint with a DELETE CASCADE clause


SQL> alter table emp drop constraint emp_dept_fk;

Table altered.

SQL> alter table emp add constraint emp_dept_fk
2 foreign key (deptno) references dept(deptno)
3 on delete cascade;

Table altered.

SQL> DELETE DEPT WHERE DEPTNO=50;

1 row deleted.

SQL> select * from emp where ename='JOEL';

no rows selected

Note the line 1 row deleted. This is evil 👿 I have deleted a department, and there were employees in it, but I got no error, no warning and no feedback about the DELETE EMP.

Instead of improving the data quality, the ON DELETE CASCADE foreign key constraint here silently deleted rows. Joel will once phone you and ask why he has been deleted…

There is one more clause of the foreign key which sets the refering column to null

SQL> INSERT INTO DEPT(deptno,dname) VALUES
2 (60,'RESTAURANT');

1 row created.

SQL> INSERT INTO EMP(EMPNO,ENAME,DEPTNO) VALUES
2 (9998,'MARC',60);

1 row created.

SQL> alter table emp drop constraint emp_dept_fk;

Table altered.

SQL> alter table emp add constraint emp_dept_fk
2 foreign key (deptno) references dept(deptno)
3 on delete set null;

Table altered.

SQL> DELETE DEPT WHERE DEPTNO=60;

1 row deleted.

SQL> select * from emp where ename='MARC';

EMPNO ENAME DEPTNO
---------- ---------- ----------
9998 MARC

Marc has no department, because his department has been deleted. Again, no feedback, no warning, no error.

Instead of improving the data quality, the ON DELETE SET NULL foreign key constraint here silently updated rows columns to NULL. Marc will wonder why he get no invitation to the department meetings.

What could be worse???

Triggers of course! Triggers not only removes rows in child tables, but triggers can also do very weird things, like updating another table, changing the values you are trying to insert, outputing a message, etc.

Also triggers are programmed by your colleagues, so they must be full of bugs 😈

You cannot imagine the number of problems that are caused by triggers and revealed only when tracing.

I once had something like

SQL> CREATE INDEX I ON T(X);

P07431B processed

Well, after enabling the trace, I discover one trigger fired on any ddl and the trigger was doing nothing else than this distracting dbms_output for “debugging” purpose. Guess google and metalink for the message did not help much…

9 thoughts on “on delete cascade”

  1. John Flack

    Yes, both referential integrity constraints and triggers can have unexpected, and unreported effects. Does that mean we shouldn’t use them? No! It means that we should use them with extreme care, and planning and good design. We should also document them thoroughly. For instance, do we really want a cascade delete on the FK between departments and employees? No, if we had done our analysis right, we would know that an employee is an independent entity from a department and does not disappear just because the department does. We might want to null the FK on EMP, but if we do, we need a cleanup process to prompt a human to assign the employees to other departments. However, let’s consider Orders and Order Line Items. Line Items are not necessarily independent of the Orders on which they appear. So we may very well want to cascade the delete from Orders to Order Line Items. The fact that Oracle only reports one Order deleted is fine – the line items are part of the one order.

  2. Laurent Schneider Post author

    Thanks John, I expected that comment! Triggers can be very useful, that is sure. I just wanted to show that they can be evil when they produce unexpected results. About orders, it is a good example.

    This features could be used, but should not be misused 😎

  8. Pingback: Oracle: Deleting table with child-record-references « Logbuffer-Blog

  9. Pingback: Oracle: Deleting child-record-referenced parent-rows « Logbuffer-Blog

Comments are closed.