How to add cascade delete in oracle when the foreign key is on the same table

Question

I have a table that contains a tree in oracle.

MY_TABLE
node_id
parent_id

How do I add a cascade delete when the root of the tree is not going to have a parent?

Right now the parent id is set to -1 for the root. When I try this I get the following error:

Error starting at line 1 in command:
ALTER TABLE regional_defaults_working
add CONSTRAINT regional_defaults_wk_delete
  FOREIGN KEY (parent_id)
  REFERENCES  regional_defaults_working(node_id)
  ON DELETE CASCADE
Error report:
SQL Error: ORA-02298: cannot validate (XVTEST.REGIONAL_DEFAULTS_WK_DELETE) - parent keys not found
02298. 00000 - "cannot validate (%s.%s) - parent keys not found"
*Cause:    an alter table validating constraint failed because the table has
           child records.

score 4 · Accepted Answer · answered Apr 07 '11 at 23:51

4

I can't recall if this will work or not, but my first impulse is to say set parent_id=NULL for the root.

answered Apr 07 '11 at 23:51

Dave Costa

47,262
8
56
72

Oh sure...post the answer right after I figure it out and right before I post it ;) – testing123 Apr 07 '11 at 23:53

score 1 · Answer 2 · answered Apr 07 '11 at 23:51

1

Figured it out.

The key (no pun intended) is to make your parent_id nullable and then set your root to be null.

answered Apr 07 '11 at 23:51

testing123

11,367
10
47
61

How to add cascade delete in oracle when the foreign key is on the same table

2 Answers2