CODEWITHSUNDEEP
sqloracle-databasecascadesql-deletecascading-deletes
testing123
testing123

Reputation: 11447

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

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.

Upvotes: 3

Views: 2946

Answers (2)

testing123
testing123

Reputation: 11447

Figured it out.

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

Upvotes: 1

Dave Costa
Dave Costa

Reputation: 48111

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

Upvotes: 4

Related Questions