sql - 更新连接 View 而不会出现非 key 保留错误

Question

我在 Oracle 11g 数据库中创建了一个 View ，它由两个连接表组成，如下所示:

CREATE FORCE VIEW my_dataview
(
   key1,
   key2,
   column_from_table1,
   column_from_table2
)
AS
   SELECT key1,
          key2,
          column_from_table1,
          column_from_table2
     FROM table1
          NATURAL LEFT OUTER JOIN table2;

其中两个源表都有两个主键，名为 key1 和 key2。数据正在正确聚合，但我无法直接从 View 更新 table2 中的任何列。

如果我要执行以下更新，

UPDATE my_dataview SET column_from_table2 = 'Hello, world' 
WHERE key1 = 1234
     AND key2 = 12;

它给出的错误是 ORA-01779 cannot modify a column which maps to a non key-preserved table。它还突出显示 column_from_table2。如果我要执行相同的命令，但改为设置 column_from_table1，它会起作用。我认为这可能是因为 table1 是连接中所需的表。

我首先想到，也许因为我加入了主键，并且 View 中只有一个结果列，这可以解释我的问题。但是，即使将单独的 table2.key1 和 table2.key2 列添加到 View 中也没有任何改变，除了不必要地复制数据。

我知道可以使用 INSTEAD OF 触发器来更新 View ，但我更愿意尽可能地保持它的整洁——无论如何，这看起来更像是一种 hack 而不是解决方案。

所以总结一下我的问题，在连接 View 上启用插入、更新和删除的最佳行动计划是什么，以至于根本无法判断它是由两个单独的表组成的 View ？

要求的表格定义:

CREATE TABLE table1
(
   key1                 NUMBER(5)    NOT NULL,
   key2                 NUMBER(2)    NOT NULL,
   column_from_table_1  DATE  
);

CREATE UNIQUE INDEX pk_table1_index ON table1
(key1, key2);

ALTER TABLE table1 ADD (
   CONSTRAINT table1_Pkey
   PRIMARY KEY
   (key1, key2)
   USING INDEX pk_table1_index
   ENABLE VALIDATE);

CREATE TABLE table2
(
   key1                 NUMBER(5)    NOT NULL,
   key2                 NUMBER(2)    NOT NULL,
   column_from_table_2  VARCHAR2(20)
);

CREATE UNIQUE INDEX pk_table2_index ON table2
(key1, key2);

ALTER TABLE table2 ADD (
   CONSTRAINT table2_Pkey
   PRIMARY KEY
   (key1, key2)
   USING INDEX pk_table2_index
   ENABLE VALIDATE);

Answer 1

您不能在此 View 中更新 column_from_table2，它不可更新。
要检查某些列是否可更新，请运行此查询:

SELECT * FROM USER_UPDATABLE_COLUMNS
WHERE table_name = 'MY_DATAVIEW';

OWNER                          TABLE_NAME                     COLUMN_NAME                    UPDATABLE INSERTABLE DELETABLE
------------------------------ ------------------------------ ------------------------------ --------- ---------- ---------
TEST                           MY_DATAVIEW                    KEY1                           YES       YES        YES       
TEST                           MY_DATAVIEW                    KEY2                           YES       YES        YES       
TEST                           MY_DATAVIEW                    COLUMN_FROM_TABLE1             YES       YES        YES       
TEST                           MY_DATAVIEW                    COLUMN_FROM_TABLE2             NO        NO         NO   

为什么它不可更新？这是个大问题。
关于这个主题的文档中有一整章:
http://docs.oracle.com/cd/B28359_01/server.111/b28310/views001.htm#i1006234
找到“Updating Views That Involve Outer Joins”，里面有一个带有外部连接的 View 的例子，并详细解释了这个 View 中哪些列可以更新，哪些列不能更新，以及为什么。