CODEWITHSUNDEEP
databaseoracle-databaseparent-childcascading-deletes
ncgzeal
ncgzeal

Reputation: 11

Oracle: delete parent table when child is deleted(Bidirectional Cascade Delete)

Got a quick question. So I have a table called PARENT and a table called CHILD, assuming they are in parent-child relationship. Is it possible to delete one of them when the other table's data is deleted?

My initial attempt is to use cascade delete to delete CHILD record When PARENT record is deleted. At same time I use trigger to delete PARENT record when CHILD's record is deleted.(condition is after delete on CHILD, fire trigger)

The result is ORA-04091: Table is Mutating. It creates sort of deadlock.

Parent table deleted-->cascade delete child --> trigger fires to delete parent, At this point, the deleted record in PARENT is not committed, so we are in a limbo state.

Is it even possible to make Parent and Child table's record associated in this way, where if one is deleted the other one is killed as well? We are assuming that this CHILD table is the only child, and the column in CHILD with foreign key has a 1 to 1 relationship with referenced column in PARENT.

Edit: I came to across oracle document for compound trigger, it's a interesting read. I will update it if it works out.

Upvotes: 1

Views: 3069

Answers (1)

Nagh
Nagh

Reputation: 1804

This kind of cascade usually doesn't make sense. Parent have to exist when child is created. So its common sense that parent shouldn't be delete only because there is no childs.

If you use only 1 to 1 relationship, you just need to change your program logic to delete only parent records, when you need to delete child.

However, if you really need this thing. I'd call it LONELY_PARENT_CLEANUP procedure. Now when we have name and meaning, we only need way to execute it. Since we can't run it from trigger (mutating table!), we should make cleanup job. This job we can launch from the trigger or on some schedule. There might be slight delay before parent will be cleaned up.

Assuming we have TEST_PARENT table with columns (id) AND TEST_CHILD table with columns (id, parent_id). We have created FK with DELETE CASCADE.
Create procedure to delete parent. I suggest we want delete parent as soon as we can.

CREATE OR REPLACE PROCEDURE DELETE_LONELY_PARENT (p_id IN NUMBER) AS 
BEGIN
  DELETE FROM test_parent WHERE id = p_id;
  COMMIT;
END DELETE_LONELY_PARENT;

Now we need triger on TEST_CHILD table:

CREATE OR REPLACE TRIGGER DELETE#PARENT#TRG 
BEFORE DELETE ON TEST_CHILD FOR EACH ROW
DECLARE
  jobId NUMBER;
BEGIN
  dbms_job.submit(
    job => jobId, 
    what => 'BEGIN DELETE_LONELY_PARENT('|| :old.parent_id ||'); END;'
  );
END;

Now when you delete child record, special job will start to delete parent record. There is no mutating table problem anymore since its different transactions.

Upvotes: 2

Related Questions