Take the 2-minute tour ×

I have a table that contains a tree in oracle.


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)
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.
share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

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

share|improve this answer
Oh sure...post the answer right after I figure it out and right before I post it ;) –  testing123 Apr 7 '11 at 23:53
add comment

Figured it out.

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

share|improve this answer
add comment

Your Answer


