关于键保留表的一些汇总

在通过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 plain copy

1. create table dept(deptid int primary key,deptname varchar2(20))  
2. create table employee(empid int primary key,empname varchar2(20),deptid int)  
3.   
4. insert into dept values(1,'dept1');  
5. insert into dept values(2,'dept2');  
6. insert into dept values(3,'dept3');  
7.   
8. insert into employee values(1,'emp1',1);  
9. insert into employee values(2,'emp2',1);  
10. insert into employee values(3,'emp3',2);  
11.   
12. create view testv   
13. as select d.deptid deptid,deptname,empid,empname,e.deptid edeptid  
14. from dept d join employee e  
15. 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 plain copy

1. create table test1(t1id int primary key,t1v varchar2(10))  
2. create table test2(t2id int primary key,t2v varchar2(10))  
3.   
4. insert into test1 values(1,'t1');  
5. insert into test1 values(2,'t2');  
6. insert into test1 values(3,'t3');  
7.   
8. insert into test2 values(1,'t21');  
9. insert into test2 values(2,'t22');  
10. insert into test2 values(3,'t23');  
11.   
12. create view test1v   
13. as select t1id ,t1v,t2id,t2v  
14. from test1 join test2  
15. on test1.t1id=test2.t2id  

在这个视图中,test1,test2都是key preserved table.但是对view的DML操作一次只能作用于一个表,不能同时对多个base 表操作.
select * from test1v

如下面的DML是可以的
update test1v set t1v='t11'
update test1v set t2v='t22'
insert into test1v(t1id,t1v) values(4,'t4')
insert into test1v(t2id,t2v) values(4,'t24')
delete from test1v where t1id=4
delete from test1v where t2id=4(这两条delete语句都是将test1表中的数据delete了??)
但是下面的DML操作是不可以的:
update test1v set  t1v='t11',t2v='t22'
insert into test1v values(5,'t5',5,'t25')
ORA-01776: cannot modify more than one base table through a join view
对于View是否可以进行DML操作还有一些其他限制:
不能有start with和connect by.
不能有group by和having.
不能有集合操作union,intersect和minus.
不能有聚合函数,如AVG,COUNT,MAX等等.
不能有rownum伪列.
当view有check option的时候:

[sql]  view plain copy

1. create view test1v   
2. as select t1id ,t1v,t2id,t2v  
3. from test1 join test2  
4. on test1.t1id=test2.t2id  
5. with check option  

insert into test1v(t1id,t1v) values(4,'t4')
update test1v set t1id=4 where t1id=1 
ORA-01733: virtual column not allowed here

如何查看那些字段可以插入更新

select * from USER_UPDATABLE_COLUMNS where table_name='TESTV'

--------------------------------------------------------------------------------------------------------------------

视图中的键保留表：

连接视图中所有更新的列必须映射到键保留表的列中，也就是视图DML操作的列必须映射到键保留表的列中

键保留表的理解是：一个复杂视图，若需要出现键保留表的话则必须保证基表中至少有一张表是有主键的！(如果两个没有主键表进行关联时是不会出现键保留表的，已经验证；另外不是如果视图中有一张基表具有主键，就一定会出现另一张基表成为键保留表的现象)

     其次，这两张表在进行关联时(可以是表连接也可以是多表查询，但一定要有关联条件，其关联条件其实相当于两表的主外键关系)，如果关联条件是使用了主键的话，则外键表为键保留表。

--------------------------------------------------------------------------------------------------------------------------

如果将第一个文章的SQL改成如下形式

create table dept(deptid int primary key,deptname varchar2(20))
create table employee(empid int ,empname varchar2(20),deptid int) ------此处原来将empid设置为主键，我已经去掉了。

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(3,'emp1',1);------该列数据原来是（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

那么最终结果集是

这种键保留表没有定义主键时。可以认为ROWID就是KEY。-----因为每一行的rowid都是不同的。
其目的在于保证一行结果只能映射到基表上的一行。

-------------------------------------------------------------------------------

请点击： 视图中的键保留表如何理解

--------------------------------------------------------------------------------

对于一个联合视图如果其中一个基表（B1）没有定义主键（解释为什么必须要有一张表定义主键），那么这个基表与其他基表进行关联的列的值就可能有重复值；这样会造成另外一张基表（B2）和它进行关联时，一条记录会匹配多条另外一张基表（B1）的记录，那么对这个联合视图的B2基表所贡献的列进行update操作（假如可以进行，实际上oracle是不会通过的）那么就会修改多次B2基表贡献的列，这样必然不是我们想要的update结果，大家进行Update时都是只需要update一次吧？

-------------------------------------------------------------------增加一点键保留表是干嘛的

  一般地，由主外键关系的2个表组成的连接视图，外键表就是键值保存表，而主键表不是。  
连接视图的更新准则
        之一：一般准则——(讲)
                        任何DML操作，只能对视图中的键值保存表进行更新,
                        即，“不能通过连接视图修改多个基表”;
                        在DML操作中，“只能使用连接视图定义过的列”;
                        “自连接视图”的所有列都是可更新(增删改)的
        之二：insert准则
                       在insert语句中不能使用“非键值保存表”中的列(包括“连接列”)；
                       执行insert操作的视图，至少应该“包含”键值保存表中所有设置了约束的列；
                       如果在定义连接视图时使用了WITH CHECK OPTION 选项，
                       则“不能”针对连接视图执行insert操作    

       之三：update准则
                        键值保存表中的列是可以更新的；
                        如果在定义连接视图时使用了WITH CHECK OPTION 选项，
                        则连接视图中的连接列(一般就是“共有列”)和基表中的“其他共有列”是“不可”更新的，
                        连接列和共有列之外的其他列是“可以”更新的

 

转载于:https://www.cnblogs.com/JSD1207ZX/p/9386314.html