我创建两个结构一样的表，开始都没有加主键，然后再这两张表上用等值连接创建一个视图，分别对视图进行dml操作。现在的问题是，oracle怎么选定join的表中为key-preserved table？？？？create table t1(id int,name varchar2(20),age int);
create table t2(id int,name varchar2(20),age int);
insert into t1 values(1,'sms1',20);
insert into t1 values(2,'sms2',20);
insert into t2 values(1,'sms',20);create or replace view v_t1t2
as
select t1.id t1_id,t1.name t1_name,t1.age t1_age, t2.id t2_id,t2.name t2_name,t2.age t2_age from t1,t2 where t1.id=t2.id;select * from v_t1t2;insert into v_t1t2 values(11,'sms11',20,11,'sms11',20) --多表连接的视图要做dml操作时，必须要有key-preserved table
ORA-01779: cannot modify a column which maps to a non key-preserved table
delete from v_t1t2
ORA-01752: cannot delete from view without exactly one key-preserved tableupdate v_t1t2 set t1_name='dba' where t1_id=1
ORA-01779: cannot modify a column which maps to a non key-preserved table
--添加一个主键
我在t1上添加了主键，oracle怎么把t2表上的数据删除了，update和insert的时候为什么又提示没有关联到key-preserved table？？？？？alter table t1 add constraint pk_t1_id primary key (id);
insert into v_t1t2 values(11,'sms11',20,11,'sms11',20)
ORA-01779: cannot modify a column which maps to a non key-preserved tableupdate v_t1t2 set t1_name='dba' where t1_id=1
ORA-01779: cannot modify a column which maps to a non key-preserved table
SQL> delete from v_t1t2 ;
1 row deleted
SQL> select * from t1;
ID NAME AGE
--------------------------------------- -------------------- ---------------------------------------
1 sms1 20
2 sms2 20
SQL> select * from t2;
ID NAME AGE
--------------------------------------- -------------------- ---------------------------------------
SQL> rollback;--添加一个主键 两个表都有主键
创建了两个主键为什么就可以进行update和delete操作？？？alter table t2 add constraint pk_t2_id primary key (id);SQL> select * from t1;
ID NAME AGE
--------------------------------------- -------------------- ---------------------------------------
1 sms1 20
2 sms2 20
SQL> select * from t2;
ID NAME AGE
--------------------------------------- -------------------- ---------------------------------------
1 sms 20
SQL> select * from v_t1t2;
T1_ID T1_NAME T1_AGE T2_ID T2_NAME T2_AGE
--------------------------------------- -------------------- --------------------------------------- --------------------------------------- -------------------- ---------------------------------------
1 sms1 20 1 sms 20insert into v_t1t2 values(11,'sms11',20,11,'sms11',20)
ORA-01776: cannot modify more than one base table through a join view
SQL> update v_t1t2 set t1_name='dba' where t1_id=1;
1 row updated
SQL> select * from v_t1t2;
T1_ID T1_NAME T1_AGE T2_ID T2_NAME T2_AGE
--------------------------------------- -------------------- --------------------------------------- --------------------------------------- -------------------- ---------------------------------------
1 dba 20 1 sms 20
SQL> select * from t1;
ID NAME AGE
--------------------------------------- -------------------- ---------------------------------------
1 dba 20
2 sms2 20
SQL> select * from t2;
ID NAME AGE
--------------------------------------- -------------------- ---------------------------------------
1 sms 20
SQL> delete from v_t1t2 ;
1 row deleted
SQL> select * from v_t1t2;
T1_ID T1_NAME T1_AGE T2_ID T2_NAME T2_AGE
--------------------------------------- -------------------- --------------------------------------- --------------------------------------- -------------------- ---------------------------------------
SQL> select * from t2;
ID NAME AGE
--------------------------------------- -------------------- ---------------------------------------
1 sms 20
SQL> select * from t1;
ID NAME AGE
--------------------------------------- -------------------- ---------------------------------------
2 sms2 20[oracle@db ~]$ oerr ora 1779
01779, 00000, "cannot modify a column which maps to a non key-preserved table"
// *Cause: An attempt was made to insert or update columns of a join view which
// map to a non-key-preserved table.
// *Action: Modify the underlying base tables directly.
[oracle@db ~]$
[oracle@db ~]$ oerr ora 1752
01752, 00000, "cannot delete from view without exactly one key-preserved table"
// *Cause: The deleted table either had no key perserved tables,
// had more than one key-preserved table, or the key-preserved
// table was an unmerged view or a table from a read-only view.
// *Action: Redefine the view or delete it from the underlying base tables.
[oracle@db ~]$
[oracle@db ~]$ oerr ora 1776
01776, 00000, "cannot modify more than one base table through a join view"
// *Cause: Columns belonging to more than one underlying table were either
// inserted into or updated.
// *Action: Phrase the statement as two or more separate statements.
tableviewdmlkey-preserved table
create table t2(id int,name varchar2(20),age int);
insert into t1 values(1,'sms1',20);
insert into t1 values(2,'sms2',20);
insert into t2 values(1,'sms',20);create or replace view v_t1t2
as
select t1.id t1_id,t1.name t1_name,t1.age t1_age, t2.id t2_id,t2.name t2_name,t2.age t2_age from t1,t2 where t1.id=t2.id;select * from v_t1t2;insert into v_t1t2 values(11,'sms11',20,11,'sms11',20) --多表连接的视图要做dml操作时，必须要有key-preserved table
ORA-01779: cannot modify a column which maps to a non key-preserved table
delete from v_t1t2
ORA-01752: cannot delete from view without exactly one key-preserved tableupdate v_t1t2 set t1_name='dba' where t1_id=1
ORA-01779: cannot modify a column which maps to a non key-preserved table
--添加一个主键
我在t1上添加了主键，oracle怎么把t2表上的数据删除了，update和insert的时候为什么又提示没有关联到key-preserved table？？？？？alter table t1 add constraint pk_t1_id primary key (id);
insert into v_t1t2 values(11,'sms11',20,11,'sms11',20)
ORA-01779: cannot modify a column which maps to a non key-preserved tableupdate v_t1t2 set t1_name='dba' where t1_id=1
ORA-01779: cannot modify a column which maps to a non key-preserved table
SQL> delete from v_t1t2 ;
1 row deleted
SQL> select * from t1;
ID NAME AGE
--------------------------------------- -------------------- ---------------------------------------
1 sms1 20
2 sms2 20
SQL> select * from t2;
ID NAME AGE
--------------------------------------- -------------------- ---------------------------------------
SQL> rollback;--添加一个主键 两个表都有主键
创建了两个主键为什么就可以进行update和delete操作？？？alter table t2 add constraint pk_t2_id primary key (id);SQL> select * from t1;
ID NAME AGE
--------------------------------------- -------------------- ---------------------------------------
1 sms1 20
2 sms2 20
SQL> select * from t2;
ID NAME AGE
--------------------------------------- -------------------- ---------------------------------------
1 sms 20
SQL> select * from v_t1t2;
T1_ID T1_NAME T1_AGE T2_ID T2_NAME T2_AGE
--------------------------------------- -------------------- --------------------------------------- --------------------------------------- -------------------- ---------------------------------------
1 sms1 20 1 sms 20insert into v_t1t2 values(11,'sms11',20,11,'sms11',20)
ORA-01776: cannot modify more than one base table through a join view
SQL> update v_t1t2 set t1_name='dba' where t1_id=1;
1 row updated
SQL> select * from v_t1t2;
T1_ID T1_NAME T1_AGE T2_ID T2_NAME T2_AGE
--------------------------------------- -------------------- --------------------------------------- --------------------------------------- -------------------- ---------------------------------------
1 dba 20 1 sms 20
SQL> select * from t1;
ID NAME AGE
--------------------------------------- -------------------- ---------------------------------------
1 dba 20
2 sms2 20
SQL> select * from t2;
ID NAME AGE
--------------------------------------- -------------------- ---------------------------------------
1 sms 20
SQL> delete from v_t1t2 ;
1 row deleted
SQL> select * from v_t1t2;
T1_ID T1_NAME T1_AGE T2_ID T2_NAME T2_AGE
--------------------------------------- -------------------- --------------------------------------- --------------------------------------- -------------------- ---------------------------------------
SQL> select * from t2;
ID NAME AGE
--------------------------------------- -------------------- ---------------------------------------
1 sms 20
SQL> select * from t1;
ID NAME AGE
--------------------------------------- -------------------- ---------------------------------------
2 sms2 20[oracle@db ~]$ oerr ora 1779
01779, 00000, "cannot modify a column which maps to a non key-preserved table"
// *Cause: An attempt was made to insert or update columns of a join view which
// map to a non-key-preserved table.
// *Action: Modify the underlying base tables directly.
[oracle@db ~]$
[oracle@db ~]$ oerr ora 1752
01752, 00000, "cannot delete from view without exactly one key-preserved table"
// *Cause: The deleted table either had no key perserved tables,
// had more than one key-preserved table, or the key-preserved
// table was an unmerged view or a table from a read-only view.
// *Action: Redefine the view or delete it from the underlying base tables.
[oracle@db ~]$
[oracle@db ~]$ oerr ora 1776
01776, 00000, "cannot modify more than one base table through a join view"
// *Cause: Columns belonging to more than one underlying table were either
// inserted into or updated.
// *Action: Phrase the statement as two or more separate statements.
tableviewdmlkey-preserved table
a view is not updatable if it contains any of the following:
Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
DISTINCT
GROUP BY
HAVING
UNION or UNION ALL
Subquery in the select list
Certain joins (see additional join discussion later in this section)
Nonupdatable view in the FROM clause
A subquery in the WHERE clause that refers to a table in the FROM clause
Refers only to literal values (in this case, there is no underlying table to update)
Uses ALGORITHM = TEMPTABLE (use of a temporary table always makes a view nonupdatable)
Multiple references to any column of a base table. Tom 大师有比较好的阐述：
Prince -- Thanks for the question regarding "updatable view",
其次是使用instead of 触发器来实现对基表的更新。
Oracle 多表视图更新
表的主键列全部显示在视图中,并且它们的值在视图中都是唯一且非空的，则该表是key-preserved table
比如：
你创建了一个复合视图EMP_DEPTSELECT * FROM EMP_DEPT;
EMPNO ENAME SAL DEPTNO DNAME LOC
----- ------ ---- ------ ---------- --------
7369 SMITH 800 20 RESEARCH DALLAS
7499 ALLEN 1600 30 SALES CHICAGO
7521 WARD 1250 30 SALES CHICAGO
7566 JONES 2975 20 RESEARCH DALLAS
7654 MARTIN 1250 30 SALES CHICAGO
7698 BLAKE 2850 30 SALES CHICAGO
7782 CLARK 2695 10 ACCOUNTING NEW YORK
7788 SCOTT 3000 20 RESEARCH DALLAS
7839 KING 5500 10 ACCOUNTING NEW YORK
7844 TURNER 1500 30 SALES CHICAGO
7876 ADAMS 1100 20 RESEARCH DALLAS
7900 JAMES 950 30 SALES CHICAGO
7902 FORD 3000 20 RESEARCH DALLAS
7934 MILLER 1430 10 ACCOUNTING NEW YORK 14 rows selected.
这里EMP is a key-preserved table，因为empno是emp的键值，也是视图的键值
但DEPT不是键保留表，因为deptno虽然是dept的键值，但不是视图的键值