使用 Oracle 将树从一个表插入到另一个表的最佳方法是什么

Question

我有一张存放树木的桌子。有一个node_id和parent_id。

当我尝试以下操作时：

insert into table1 select * from table2 start with node_id = 1 connect by prior node_id = parent_id order by parent_id nulls first

我收到此错误：

Error starting at line 6 in command:
insert into table1 select * from table2 start with node_id = 1 connect by prior node_id = parent_id order by parent_id nulls first
Error report:
SQL Error: ORA-02291: integrity constraint (XVTEST.REGIONAL_DEFAULT_DELETE) violated - parent key not found
02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
*Cause:    A foreign key value has no matching primary key value.
*Action:   Delete the foreign key or add a matching primary key.

我明白为什么会收到此错误。我只是想知道是否有一种方法可以在不进行递归 pl/sql 过程的情况下做到这一点。想法？如果不可能，是否有人有类似的程序可以用作示例？

Answer 1

一种选择是将 FOREIGN KEY 约束创建为 DEFERRABLE，然后为您的事务将它们设置为 DEFERRED，这样外键约束的执行就会推迟到 COMMIT。

请注意，当表的内容违反约束时，您不想执行任何 SELECT。在某些情况下，某些 SELECT 语句将返回意外或不一致的结果。 （请务必考虑触发器执行的 SELECT 语句。）

---

-- to create foreign key constraints as deferrable
ALTER TABLE my_table ADD CONSTRAINT my_table_fk1 
FOREIGN KEY (other_table_id) REFERENCES other_table (id)
DEFERRABLE INITIALLY IMMEDIATE;

-- defer all deferrable constraints until the next commit
ALTER SESSION SET CONSTRAINTS=DEFERRED;
-- or
SET CONSTRAINTS ALL DEFERRED;

-- dml operations may now temporarily violate constraints
INSERT ... ; 
UPDATE ... ;

-- you can check the constraints before the commit
SET CONSTRAINTS ALL IMMEDIATE;

-- all deferred constraints will be enforced at the next commit
-- (a fk violation exception will be raised here, rather than by the DML)
COMMIT;

---

一些有用的参考：

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:914629004506

http://download.oracle.com/docs/cd /B10500_01/server.920/a96524/c22integ.htm#4666

---

附录：

此方法专门适用于 Oracle 数据库，可能不适用于其他关系数据库引擎。

在延迟约束时针对表执行 SELECTS 的警告仅适用于延迟约束的会话。其他会话将看到一致的状态，因为它们不会看到任何未提交的更改。

使用 DEFERRED 约束优于禁用和重新启用约束，因为禁用约束会影响所有会话，并且重新验证约束会消耗大量资源（对于大型表）。