Oracle 更新主键列或更新层次结构中的其他列

我有一个类似以下结构的表格:

create table table_a
(
"ID_TABLE_A" NUMBER, --primary key column
"PARENT_ID" NUMBER,
"CONTENT_1" VARCHAR2(32),
"CONTENT_2" NUMBER,
"CONTENT_3" NUMBER,
"CONTENT_4" VARCHAR2(256),
"CONTENT_5" varchar2(128)
)

表 _ a中有大约200条记录，它是复合的one logical collection,并且是层次结构。可能有很多这样的收藏s。

为了从这样一个有大约200条记录的集合中构建一棵树，我选择
manually generating primary keys, and they are continuous integers。 So they can easily be ordered by sql, and built into a tree by java code(not related with oracle db)。

Problems occurs when it comes that sometimes we need insert some extra records at some position into such collection, or delete some records at some position from such collection。
To keep the primary key column continuous integers。 I may choose update primary key or update other content columns。

为了清楚，假设我们有记录:

"ID_TABLE_A", "PARENT_ID", "CONTENT_1", "CONTENT_2", "CONTENT_3", "CONTENT_4", "CONTENT_5"
 1, null, null, null, null, null, null
 2,  1, null, null, null, null, null
 3,  1, null, null, null, null, null
 4,  1, null, null, null, null, null
 5,  2, null, null, null, null, null
 6,  2, null, null, null, null, null
 7,  3, null, null, null, null, null
 8,  3, null, null, null, null, null
 9,  4, null, null, null, null, null
10,  4, null, null, null, null, null
11,  5, null, null, null, null, null
12,  6, null, null, null, null, null
13,  7, null, null, null, null, null
14,  8, null, null, null, null, null
15,  9, null, null, null, null, null
16, 10, null, null, null, null, null

the above 16 records is a collection。

now we need add another child for 1, so its "ID_TABLE_A" should be 5, which already existed。
所以我可以选择:
答:

update ID_TABLE_A set ID_TABLE_A = ID_TABLE_A + 1 where ID_TABLE_A >= 5;
   insert into ID_TABLE_A (ID_TABLE_A) values(5);

B:

update ID_TABLE_A set PARENT_ID = #{}, CONTENT_1 = #{}, CONTENT_2 = #{}, CONTENT_3 = #{}, 
   CONTENT_4 = #{}, CONTENT_5 = #{}; -- for every record "ID_TABLE_A" >= 5
   insert into ID_TABLE_A (ID_TABLE_A, PARENT_ID, CONTENT_1, CONTENT_2, CONTENT_3, CONTENT_4, 
   CONTENT_5) values(17, 10, null, null, null, null, null);

way A need update primary key column, and I don't know the performance behavior。
way B need copy parent_id column and other content columns。

方式A只需要2个SQL，
way B need many SQLs。

I really don't know which way is better。
Could you guys shine me some light? Sorry for my poor English。

更新表的主键是个坏主意!

您必须将此更改传播到所有子行。哪个Oracle数据库不自动支持。但是，即使这样做了，在极端情况下，您最终可能会将更新级联到大量表中。

我的建议:

放弃尝试具有完全顺序的主键的想法，该主键以某种方式匹配树的顺序。

使用序列/标识生成值。插入行后，切勿更改主行。

并使用SQL生成树!

如果您不确定如何执行此操作，请在此视频中讨论各种技术:



如果您想研究其他存储树的方式，Bill Karwin有一个很好的幻灯片分享，讨论了各种存储层次结构的方法及其优缺点:

http://www.slideshare.net/billkarwin/models-for-hierarchical-data