oracle中外键同表时如何添加级联删除
我有一个表，其中包含 Oracle 中的一棵树。
MY_TABLE
node_id
parent_id
当树的根没有父级时，如何添加级联删除？
现在，根的父 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.
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我不记得这是否有效，但我的第一反应是为根设置parent_id=NULL。
I can't recall if this will work or not, but my first impulse is to say set parent_id=NULL for the root.
想通了。
关键（没有双关语）是让你的parent_id可以为空，然后将你的root设置为空。
Figured it out.
The key (no pun intended) is to make your parent_id nullable and then set your root to be null.