Oracle 多表插入和 Blob 外键问题

Question

我们有一个表，我们希望根据特定的源列将其分解为表树。我想尝试使用多列插入，但似乎如果我将一个 blob 插入到子表中，我最终会违反外键约束。

我不认为这违反了有关多的规则-table inserts 但我可能是错的...

我希望有人能给我指出一些关于这里实际发生的事情的更深入的资源，这样我就可以确信任何解决方案都会起作用Oracle 数据库 9i 上 liquibase 变更集的一部分 -> 11克。

希望简化场景

CREATE TABLE source (
    pk NUMBER NOT NULL PRIMARY KEY,
    type VARCHAR2(20) NOT NULL,
    content VARCHAR2(20) NOT NULL
);

INSERT INTO source (pk,type,content) values (1,'two','n/a');
INSERT INTO source (pk,type,content) values (2,'one','Content');

CREATE TABLE dest (
    pk NUMBER NOT NULL PRIMARY KEY,
    type VARCHAR2(20) NOT NULL
);


CREATE TABLE dest_one  (
    pkfk NUMBER NOT NULL PRIMARY KEY,
    data BLOB NOT NULL,
    CONSTRAINT XFK1DEST_ONE FOREIGN KEY (pkfk) REFERENCES dest (pk)
);


CREATE TABLE dest_two  (
    pkfk NUMBER NOT NULL PRIMARY KEY,
    CONSTRAINT XFK1DEST_TWO FOREIGN KEY (pkfk) REFERENCES dest (pk)
 );

源包含我们的原始数据。 dest 将是我们的父表，其子表为 dest_one 和 dest_two（分别包含“one”或“two”类型的信息）。第一类事物有内容，第二类事物没有内容。

失败的尝试

INSERT ALL
WHEN 1=1 THEN INTO dest (pk,type) VALUES (pk,type)
WHEN type='one' THEN INTO dest_one (pkfk,data) VALUES (pk,content)
WHEN type='two' THEN INTO dest_two (pkfk) VALUES (pk)
SELECT pk,type,utl_raw.cast_to_raw(content) as content from source where type in ('one','two');

正如前面提到的，我在这里遇到了外键约束违规。为了进一步说明 blob 是问题所在，我尝试了两个单独的类似查询（如下），意识到没有 blob 插入的查询有效，但 blob 插入失败。

INSERT ALL
WHEN 1=1 THEN INTO dest (pk,type) VALUES (pk,type)
WHEN type='two' THEN INTO dest_two (pkfk) VALUES (pk)
SELECT pk,type,utl_raw.cast_to_raw(content) as content from source where type = 'two';
/* Successful */

INSERT ALL
WHEN 1=1 THEN INTO dest (pk,type) VALUES (pk,type)
WHEN type='one' THEN INTO dest_one (pkfk,data) VALUES (pk,content)
SELECT pk,type,utl_raw.cast_to_raw(content) as content from source where type = 'one';
/* ORA-02291: integrity constraint violated, no parent key */

解决方案 1 - 传统插入

INSERT INTO dest (pk,type) SELECT pk,type from source where type in ('one','two');
INSERT INTO dest_two (pkfk) SELECT pk from source where type = 'two';
INSERT INTO dest_one (pkfk,data) SELECT pk,utl_raw.cast_to_raw(content) from source where type = 'one';

我正在考虑的一个选项是返回到多个单独的插入语句，但与我在这里陈述的方式不同，我担心我必须确保我编写的子表插入仅尝试插入父目标表中存在的那些行...我需要对 Liquibase 如何处理同一变更集中的多个 sql 语句进行更多研究。

解决方案 2 - 暂时禁用外键约束

ALTER TABLE dest_one DISABLE CONSTRAINT XFK1DEST_ONE;


INSERT ALL
当 1=1 时，则进入 dest (pk,type) 值 (pk,type)
当 type='one' 时，则进入 dest_one (pkfk,data) VALUES (pk,content)
当 type='two' 时，则进入 dest_two (pkfk) 值 (pk)
选择 pk,type,utl_raw.cast_to_raw(content) 作为源中的内容，其中输入 ('one','two');

更改表 dest_one 启用约束 XFK1DEST_ONE；

这是我倾向于的解决方案。虽然禁用 Blob 表上的外键似乎使其在我的测试环境（10g - 10.2.0.1.0）中工作，但我不确定是否也应该禁用非 Blob 表上的外键（由于 9i、11g 或其他版本的 10g 的行为方式）。这里的任何资源也将不胜感激。

非常感谢！

Answer 1

另一种解决方案是将约束评估推迟到 COMMIT。我怀疑（但不确定）多表插入是以不同于您期望和想要的顺序插入行的。按如下方式重新创建约束：

ALTER TABLE DEST_ONE DROP CONSTRAINT XFK1DEST_ONE;

ALTER TABLE DEST_ONE
  ADD CONSTRAINT XFK1DEST_ONE
    FOREIGN KEY (pkfk) REFERENCES dest (pk) 
    INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE DEST_TWO DROP CONSTRAINT XFK1DEST_TWO;

ALTER TABLE DEST_TWO
  ADD CONSTRAINT XFK1DEST_TWO
    FOREIGN KEY (pkfk) REFERENCES dest (pk)
    INITIALLY DEFERRED DEFERRABLE;

这将重新创建约束，以便可以推迟它们，并从创建它们的时间开始推迟。然后再次尝试原来的 INSERT。

分享并享受。