金仓数据库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、Start
- With、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、Start
- With、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、Start
- With、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 AS
SELECT emp.empno, emp.ename, emp.deptno, emp.sal, emp_bk.empno AS empno_bk, emp_bk.mgr, emp_bk.job
FROM 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;
![file](https://img-blog.csdnimg.cn/c1c1dc43ef5845b389ae1522c50a0f7a.png)
多表join视图支持update操作
update emp_dept set ename='king' where empno=7782;
验证:
select * from emp;
![file](https://img-blog.csdnimg.cn/542b4be6856c4c53813aa28c8886f9e1.png)
多表join视图支持delete操作
delete from emp_dept where empno=7790;
验证:
![file](https://img-blog.csdnimg.cn/acd679a14f274587be2ad88cea47e34a.png)
5.2 只读视图
CREATE OR REPLACE VIEW emp_dept AS
SELECT empno, ename, emp.deptno, sal, dname, loc
FROM 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);
![file](https://img-blog.csdnimg.cn/2f4e40e1d7784b4cb105722c8e382edb.png)
只读视图的update操作:
update emp_dept set ename='king' where empno=7782;
![file](https://img-blog.csdnimg.cn/fa7fb44a09e1484db5ca77211af38866.png)
只读视图的delete操作:
delete from emp_dept where empno=7789;
![file](https://img-blog.csdnimg.cn/d026c23e52754b43bda1770c090f3118.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');
![file](https://img-blog.csdnimg.cn/0da98a898dc742d4a6f8948b1e36ecbd.png)
Update操作:
update emp_dept set a = 3 where b = 'kes';
![file](https://img-blog.csdnimg.cn/5648b55b7a6f4c0c91e7a8888c19441b.png)
Delete操作:
delete from emp_dept where a =1;
![file](https://img-blog.csdnimg.cn/ae24b10bd3e64a84898b97d1f204a7c1.png)