I have a table that contains a tree in oracle.
我有一个包含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:
现在，根的父id被设置为-1。当我尝试这个时，我得到了以下错误:
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.
2 个解决方案
#1
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.
我不记得这是否可行，但我的第一个想法是为根输入set parent_id=NULL。
#2
1
Figured it out.
算出来。
The key (no pun intended) is to make your parent_id nullable and then set your root to be null.
键(不是双关语)是使parent_id为nullable，然后将根设置为null。
#1
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.
我不记得这是否可行，但我的第一个想法是为根输入set parent_id=NULL。
#2
1
Figured it out.
算出来。
The key (no pun intended) is to make your parent_id nullable and then set your root to be null.
键(不是双关语)是使parent_id为nullable，然后将根设置为null。