SQL & PL/SQL

In a recent data deletion project, I ran into a problem where Oracle allowed for a record (the parent) to be deleted when there still existed a child record in the same table which referred to the parent.

An abbreviated version of the table is as follows:

create table test (
template_id number not null,
customer_id number null,
parent_id number null,
constraint pk_test primary key (template_id),
constraint r_parent foreign key (parent_id) references test (template_id));

The parent and child records are as follows:

Parent: template_id = 100, customer_id = 200, parent_id = null
Child: template_id = 101, customer_id = null, parent_id = 100

Records were deleted from this table using:

DELETE FROM test WHERE customer_id = 200;

When this statement is executed, is it being executed as part of 155 delete statements using PL/SQL, and tens of thousands of records from 155 tables are being deleted. These delete statements have been ordered taking into account foreign key constraints. All 155 statements are being deleted in a single transaction. The delete statement above was the 23rd statement of the set to be executed. I would have expected when this delete statement was executed, an error would have been thrown. However, all 155 delete statements successfully complete, and after committing the results, the child record above still exists in the DB as an orphan. I also tried executing the first 23 statements manually in SQL*Plus (still one transaction), and the problem still occurs: the parent is deleted and the child is orphaned.

If I execute the very simple example above, I do indeed get an referential constraint error.

Has anyone ever encountered this situation? And does anyone have any ideas how to go about troubleshooting this problem. We need to know the cause of this, as we have a small handful of table with a similar self-referential foreign key constraint set-up.

Thank you very much in advance,

Mark