金仓数据库KingbaseES多表join视图支持可更新介绍
关键字:
KingbaseES、键保留表、目标基表、可更新视图
1.什么是键保留表、目标基表、可更新视图?
键保留表(Key-Preserved Table):如果一个表的每个键,同样是多表join后结果的键,我们称该表是键值保护的。(基表A的Join列在另一侧基表B中是主键、唯一键或被唯一索引,则认为基表A是键保留表)
键保留表的行在视图结果中最多出现一次,它的本质是当通过JOIN视图执行DML操作时,可以找到目标行在基表中的对应行,进而通过视图对基表执行DML操作。键保留表是理解多表JOIN视图是否支持DML的基础。主要用于判断join视图是否支持DML,当JOIN视图的基表中存在一个或多个键保留表时,我们称该JOIN视图支持DML。
目标基表:对于多表JOIN视图进行DML操作时,只能操作其中一个基表,所有当前Query层中的真正操作的目标表或视图仅有一个,我们称其为目标基表。
可更新视图,是指通过视图,来更新(Update)、插入(Insert)、删除(Delete)基础表中的数据。视图是一个虚拟表,对视图的更新,实质上是更新基础表。
用户、前台查询视图时,仅需要确定关联关系即可。但是在数据更新方面,多表信息同步编辑功能,一般用instead of触发器实现。所以实现多表join视图可更新,可以大大简化前台编程的工作量。
2.多表join可更新视图的分类
2.1 可更新视图
2.1.1 多表join视图支持Insert操作
1.创建多表Join视图,满足如下条件,支持通过视图进行Insert操作数据到基表。
1)至少存在一个基表是键保留表(为方面后面文档描述,标记该基表为表A);
2)不存在如下字段
SET操作符(Union、Intersect、Except、Minus)Distinct操作符Aggregate(如count、avg、sum、max、min、first) 携带Group By、Connect By、StartWith、Having、LIMIT或OFFSET Window函数 set-returning函数
3)Where子句中不含有rownum
4)FROM子句后不能是SELECT CURRENT_USER这样的语句或者函数
5)视图定义无with check option
2.Insert操作的限制:
1)Insert操作只可一次修改一个基表;
2)Insert操作的列需是表A的列;
3)当创建join视图带了with check option, insert操作不被允许;
4)如果基表的列非空且没有默认值,insert时忽略该列,insert操作不被允许;
2.1.2 多表join视图支持Update操作
1.创建多表Join视图,满足如下条件,支持通过视图进行Update操作数据到基表。
1)至少存在一个基表是键保留表(为方面后面文档描述,标记该基表为表A);
2)不存在如下字段
SET操作符(Union、Intersect、Except、Minus)Distinct操作符Aggregate(如count、avg、sum、max、min、first) 携带Group By、Connect By、StartWith、Having、LIMIT或OFFSET Window函数 set-returning函数
3)Where子句中不含有rownum
4)FROM子句后不能是SELECT CURRENT_USER这样的语句或者函数
2.Update操作的限制:
1)Update操作只可一次修改一个基表;
2)Update操作的列需是表A的列;
3)当创建join视图带了with check option, 不允许更新join的列和重复表的所有列;
2.1.3 多表join视图支持Delete操作
1. 创建多表Join视图,满足如下条件,支持通过视图进行Delete操作数据到基表。
1)至少存在一个基表是键保留表(为方面后面文档描述,标记该基表为表A)。
2)不存在如下字段
SET操作符(Union、Intersect、Except、Minus)Distinct操作符Aggregate(如count、avg、sum、max、min、first) 携带Group By、Connect By、StartWith、Having、LIMIT或OFFSET Window函数set-returning函数
3)Where子句中不含有rownum
4)FROM子句后不能是SELECT CURRENT_USER这样的语句或者函数
2. Delete操作的限制:
1)Delete操作只可一次修改一个基表;
2)Delete操作只能从基表A中删除数据;
3)存在多个基表支持DML时,Delete只从第一个支持DML的基表中删除数据。
2.2 只读视图(with read only)
当创建视图with read only, 不允许通过视图进行DML操作。
2.3 不可更新视图
1) 创建视图为只读视图
2) 创建视图的基表不是键保留表
3) 创建视图含有违规字段(具体参考可更新视图规则)
3.多表join可更新视图的用途
目前Kingbase只支持单表视图的DML操作,支持多表Join视图的DML后,可通过多表join视图的进行操作,来更新对应基表的数据,可以大大降低前台编程的工作量。
4.KES和oracle的可更新视图区别
基本完全兼容oracle已知多表join支持可更新视图所有功能。
Oracle中两张表使用natural join建立视图,表的顺序不一致会导致自然连接的列不可更新。
Kes的自然连接不存在此类问题。
5.多表join支持可更新视图示例
5.1 可更新视图
create table dep ( deptno INT not null, dname VARCHAR(100) not null, loc VARCHAR(100) not null,Primary key (deptno) ); insert into dep values(10, 'Accounting', 'New York');insert into dep values(20, 'Research', 'Dallas');insert into dep values(30, 'Sailing', 'Boston'); CREATE TABLE emp (empno number(4) PRIMARY KEY,ename varchar2(10) not null,job varchar2(10),mgr number(4),sal number(7,2),comm number(7,2),deptno int,FOREIGN KEY (deptno) REFERENCES dep(deptno));INSERT INTO emp (empno, ename, deptno) VALUES (7782, 'clark', 10);INSERT INTO emp (empno, ename, deptno) VALUES (7783, 'king', 10);INSERT INTO emp (empno, ename, deptno) VALUES (7784, 'miller', 20);INSERT INTO emp (empno, ename, deptno) VALUES (7785, 'smith', 20);INSERT INTO emp (empno, ename, deptno) VALUES (7786, 'adams', 20);INSERT INTO emp (empno, ename, deptno) VALUES (7787, 'ford', 30);INSERT INTO emp (empno, ename, deptno) VALUES (7788, 'scott', 30);INSERT INTO emp (empno, ename, deptno) VALUES (7789, 'jones', 30);INSERT INTO emp (empno, ename, deptno) VALUES (7790, 'hello', 30);CREATE TABLE emp_bk (empno number(4) PRIMARY KEY,ename varchar2(10),job varchar2(10),mgr number(4),sal number(7,2),comm number(7,2),deptno int,FOREIGN KEY (deptno) REFERENCES dep(deptno));INSERT INTO emp_bk (empno, ename, deptno) VALUES (7782, 'clark', 10);INSERT INTO emp_bk (empno, ename, deptno) VALUES (7783, 'king', 10);INSERT INTO emp_bk (empno, ename, deptno) VALUES (7784, 'miller', 20);INSERT INTO emp_bk (empno, ename, deptno) VALUES (7785, 'smith', 20);INSERT INTO emp_bk (empno, ename, deptno) VALUES (7786, 'adams', 20);INSERT INTO emp_bk (empno, ename, deptno) VALUES (7787, 'ford', 30);INSERT INTO emp_bk (empno, ename, deptno) VALUES (7788, 'scott', 30);INSERT INTO emp_bk (empno, ename, deptno) VALUES (7789, 'jones', 30);INSERT INTO emp_bk (empno, ename, deptno) VALUES (7790, 'hello', 30);CREATE OR REPLACE VIEW emp_dept ASSELECT emp.empno, emp.ename, emp.deptno, emp.sal, emp_bk.empno AS empno_bk, emp_bk.mgr, emp_bk.jobFROM emp JOIN emp_bk ON (emp.empno = emp_bk.empno);多表join视图支持insert操作:INSERT INTO emp_dept (empno, ename, deptno, sal) VALUES (7791, 'wade',20,20000);验证:select * from emp;![在这里插入图片描述](https://img-/7bb72f5d83f04890abbe738a3aad6f30.png)多表join视图支持update操作update emp_dept set ename='king' where empno=7782;验证:select * from emp;![在这里插入图片描述](https://img-/98516c62ab21415fba92b9f54d7a928a.png)多表join视图支持delete操作delete from emp_dept where empno=7790;验证:![在这里插入图片描述](https://img-/48989f57efff473cae8eb770983ae605.png)
5.2 只读视图
CREATE OR REPLACE VIEW emp_dept ASSELECT empno, ename, emp.deptno, sal, dname, locFROM emp JOIN dep ON (emp.deptno = dep.deptno AND dep.deptno IN (10, 20, 30))WITH READ ONLY ;只读视图的insert操作:INSERT INTO emp_dept (empno, ename, deptno, sal) VALUES (7791, 'wade',20,20000);![在这里插入图片描述](https://img-/d03560fcc5a04139a629f1717113563c.png)只读视图的update操作:update emp_dept set ename='king' where empno=7782;![在这里插入图片描述](https://img-/1521b233e2134fc0aa75a26779cb1761.png)只读视图的delete操作:delete from emp_dept where empno=7789;![在这里插入图片描述](https://img-/cea107c389504905998d2a9dea2b38ff.png)
5.3 不可更新视图
create table kes_1(a int,b varchar(10));create table kes_2(a int,b varchar(10));insert into kes_1 values(1,'kes');insert into kes_1 values(2,'kes');insert into kes_2 values(1,'kes');insert into kes_2 values(2,'kes');create or replace view emp_dept as select kes_1.a,kes_1.b,kes_2.a as aa,kes_2.b as bb from kes_1 join kes_2 on (kes_1.a=kes_2.a);
insert操作:
insert into emp_dept(a,b) values(3,'king');![在这里插入图片描述](https://img-/df2c59e708ba4791b8cdd38ca1abf6b3.png)
Update操作:
update emp_dept set a = 3 where b = 'kes';![在这里插入图片描述](https://img-/1ec0151b97ae4ef39ae4c1e14248671c.png)
Delete操作:
delete from emp_dept where a =1;![在这里插入图片描述](https://img-/4d814e364bc146769b7bbbf049ea4705.png)