oracle 尝试插入视图时无法修改映射到非键保留表错误的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8824015/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Cannot modify a column which maps to a non key-preserved table error while trying to insert into a view
提问by Android
I have t1table having no number(3)and name varchar2(20)columns
我有t1表no number(3)和name varchar2(20)列
And emptable having empno,ename,jobetc columns.
而emp其表empno，ename，job等列。
Now I am creatng a view
现在我正在创建一个视图
create view v_t as select * from t1,emp;
It creates view. Then I am trying to insert values inside v_tthat is view but it gives me error that
它创建视图。然后我试图在v_t视图中插入值，但它给了我错误
cannot modify a column which maps to a non key-preserved table
无法修改映射到非键保留表的列
What am I doing wrong?
我究竟做错了什么？
采纳答案by Aaron
Assuming that both the t1 table and emp table have primary keys that the view can identify as unique, I'm going to guess that the issue is with how you are specifying your JOIN. What you've got right now looks like it will create a Cartesian product (every row from one table indiscriminately joined to every row from the other), and that's probably not going to satisfy the key-preserved table requirement (referenced in the question that you linked above).
假设 t1 表和 emp 表都具有视图可以识别为唯一的主键，我猜测问题在于您如何指定 JOIN。您现在所拥有的看起来会创建一个笛卡尔积（一个表中的每一行不加选择地连接到另一个表中的每一行），这可能无法满足保留键的表要求（在问题中引用你在上面链接）。
You are specifying an implicit JOIN in your FROM clause, but I don't see any JOIN conditions (read: WHERE clause). By looking at your schema, I'm going to assume that you can JOIN on t1.no = emp.empno like this:
您在 FROM 子句中指定了隐式 JOIN，但我没有看到任何 JOIN 条件（阅读：WHERE 子句）。通过查看您的架构，我将假设您可以像这样在 t1.no = emp.empno 上加入：
create view v_t as
select *
from t1,emp
where t1.no = emp.empno;
Or with an explicit JOIN:
或者使用显式 JOIN：
create view v_t as
select *
from t1
inner join emp on emp.empno = t1.no;
Oracle will allow a NATURAL JOIN (without specifying JOIN conditions) on tables that have columns of the same type and name. Of course, since no and empno have different names, that's not going to work.
Oracle 将允许对具有相同类型和名称的列的表进行 NATURAL JOIN（不指定 JOIN 条件）。当然，由于 no 和 empno 具有不同的名称，所以这是行不通的。
Now that your CREATE VIEW is squared-away, let's get to the INSERT. You can INSERT into a VIEW based on a JOIN as long as the table is key-preserved and you don't try to INSERT to more than one base table at once. Which in your case means writing two separate INSERT statements or writing an INSTEAD OF trigger:
既然您的 CREATE VIEW 已经平方，让我们进入 INSERT。只要表是键保留的并且您不尝试一次插入多个基表，您就可以基于 JOIN 插入到视图中。在您的情况下，这意味着编写两个单独的 INSERT 语句或编写一个 INSTEAD OF 触发器：
CREATE TRIGGER v_t_insteadof
INSTEAD OF INSERT ON v_t
FOR EACH ROW
BEGIN
INSERT INTO t1 (no, name)
VALUES(:new.no, :new.name);
INSERT INTO emp (empno, ename, job)
VALUES(:new.no, :new.ename, :new.job);
END v_t_insteadof;
Note, that you'll need to adjust the INSERT INTO emp based-on the additional fields that might exist in that table. Also, your INSERT command will need to specifically name the fields:
请注意，您需要根据该表中可能存在的其他字段来调整 INSERT INTO emp。此外，您的 INSERT 命令将需要专门命名字段：
INSERT INTO v_t (no, name, ename, job) VALUES (59, 'Bob', 'Bobs Ename', 'Bobs Job');
