什么是Oracle Key-Preserved Table和什么样的视图可以进行DML操作
在通过DML操作Oracle的视图的时候,有一个很重要的条件是操作的base表必须是Key-Preserved Table.
那么,什么是Key-Preserved Table呢.Oracle给出的定义是:
A table is key preserved if every key of  the table can also be a key of the result of the join.
It is not necessary that the key or keys of a table be selected for it to be key preserved.

It is sufficient that if the key or keys were selected, then they would also be key(s) of the result of the join.

如果某一个表的主键可以作为这个join结果(view通常是几个表的join结果)的主键,那么这个表就是key preserved table.

这个表的主键并非一定要出现在select出来的结果集中(select list里面),但是如果其出现在结果集中,那么它必须可以满足作为这个结果集的主键的要求.


来看一个例子,有这样两个表,dept和employee,以及基于这两个表的testv视图[sql] view plaincopyprint?create table dept(deptid int primary key,deptname varchar2(20)) 
create table employee(empid int primary key,empname varchar2(20),deptid int) 
 
insert into dept values(1,'dept1'); 
insert into dept values(2,'dept2'); 
insert into dept values(3,'dept3'); 
 
insert into employee values(1,'emp1',1); 
insert into employee values(2,'emp2',1); 
insert into employee values(3,'emp3',2); 
 
create view testv  
as select d.deptid deptid,deptname,empid,empname,e.deptid edeptid 
from dept d join employee e 
on d.deptid=e.deptid 

create table dept(deptid int primary key,deptname varchar2(20))
create table employee(empid int primary key,empname varchar2(20),deptid int)

insert into dept values(1,'dept1');
insert into dept values(2,'dept2');
insert into dept values(3,'dept3');

insert into employee values(1,'emp1',1);
insert into employee values(2,'emp2',1);
insert into employee values(3,'emp3',2);

create view testv
as select d.deptid deptid,deptname,empid,empname,e.deptid edeptid
from dept d join employee e
on d.deptid=e.deptid查询这个视图:select * from testv


在testv这个视图中,employee就是一个key preserved table,而dept不是.
那么这个视图可以进行的DML为
delete from testv where empid=1(操作的结果是将employee表中的empid=1的记录delete了,dept表不会有改变)
delete from testv where deptid=1(操作的结果是将employee表中的empid=1和2的记录都delete了,dept表不会有改变)
update testv set empname='empx' where edeptid=1
update testv set empname='empx' where empid=1
update testv set empname='empx' where deptid=1
insert into testv(empid,empname,edeptid) values(4,'emp4',2)
这个视图不可以进行的DML为
update testv set deptname='deptx' where deptid=1
update testv set deptname='deptx' where empid=1
insert into testv(deptid,deptname) values(4,'dept4')
ORA-01779: cannot modify a column which maps to a non key-preserved table
一个View中可以有多个key preserved tables

[sql] view plaincopyprint?create table test1(t1id int primary key,t1v varchar2(10)) 
create table test2(t2id int primary key,t2v varchar2(10)) 
 
insert into test1 values(1,'t1'); 
insert into test1 values(2,'t2'); 
insert into test1 values(3,'t3'); 
 
insert into test2 values(1,'t21'); 
insert into test2 values(2,'t22'); 
insert into test2 values(3,'t23'); 
 
create view test1v  
as select t1id ,t1v,t2id,t2v 
from test1 join test2 
on test1.t1id=test2.t2id 

create table test1(t1id int primary key,t1v varchar2(10))
create table test2(t2id int primary key,t2v varchar2(10))

insert into test1 values(1,'t1');
insert into test1 values(2,'t2');
insert into test1 values(3,'t3');

insert into test2 values(1,'t21');
insert into test2 values(2,'t22');
insert into test2 values(3,'t23');

create view test1v
as select t1id ,t1v,t2id,t2v
fr