|
|
“ORA-01779: 无法修改与非键值保存表对应的列”中涉及的概念和解决方法
什么是键值保存表（Key-Preserved Table）？
在理解什么是键值保存表之前，首先要知道 可更新的联接视图 这个概念，键值保存表只是保存了允许更新的字段信息的一张表。为什么会出现这么一张表呢？一步一步来看吧。
理解“视图”、“联接视图”、“可更新的联接视图”的概念
视图 可以分为视图对象和内嵌视图两种。
- 视图对象（View Object）
数据库里的
视图对象
跟一张表或索引等一样，是数据库对象中的一种，它其实是将基于表中原始数据的查询给虚拟了一层之后，提供给外部查询的一种对象。
其本质是并不保存查询的实际结果，而只是将查询语句保存在数据库中，当用户查询某张视图的时候，就会找到并执行这个视图的语句。
而
联接视图
其实就是在一个视图中将两个表建立关联就是了。
-- 基于SCOTT用户下的表创建测试表
CREATE TABLE EMP_T AS
SELECT *
FROM EMP;
CREATE TABLE DEPT_T AS
SELECT *
FROM DEPT;
-- 创建视图对象（联接视图）
CREATE OR REPLACE VIEW EMP_DEPT_V
SELECT E.EMPNO
, E.ENAME
, E.SAL
, E.DEPTNO
, D.DNAME
FROM EMP_T E, DEPT_T D
WHERE E.DEPTNO = D.DEPTNO;
- 内嵌视图（Inline View）
在SQL语句中，很多地方都可以写嵌套的语句，比如说，FROM后面可以套括号嵌套其他的语句，WHERE、SELECT、UPDATE、INSERT、DELETE后面都是可以写的。只是写的位置不同，执行的方式还有处理时的一些限制不同，这些SQL中嵌套的SQL被称为子查询（Subquery） 。子查询可以分为如下几类：
内嵌视图：位置位于FROM语句中，也相当于一个预处理结果集的概念
标量子查询（Scalar Subquery）：只返还一个结果值的子查询。位于SELECT语句的子查询只能返还一个值，所以必然是标量子查询，WHERE语句里也会出现这样的子查询
关联子查询：将查询的结果传递给子查询作为参数的使用的形式
一般子查询：除了那些特殊形式的子查询以外的一般用法
-- 内嵌视图的示例
-- 联接内嵌视图
SELECT *
(SELECT E.EMPNO
, E.ENAME
, E.SAL
, E.DEPTNO
, D.DNAME
FROM EMP_T E, DEPT_T D
WHERE E.DEPTNO = D.DEPTNO
那什么是可更新联接视图（Updatable Join View ）?
很简单，就是将一个联接视图（包括视图对象和内嵌视图）放到UPADTE语句中这样的语法形式。
暂时不考虑SQL语句的意义，只是测试用
-- 基于视图对象的
UPDATE EMP_DEPT_V
SET ENAME = ENAME || '-' || DEPTNO;
-- 基于内嵌视图的
UPDATE
(SELECT E.EMPNO
, E.ENAME
, E.SAL
, E.DEPTNO
, D.DNAME
FROM EMP_T E, DEPT_T D
WHERE E.DEPTNO = D.DEPTNO
SET ENAME = ENAME || '-' || DEPTNO;
跟到一步一步下来，到这一步的时候，别执行这个语句，先来猜猜这两条语句执行的结果是什么？
理解报错的原理
按照正常的联接逻辑，DEPT_T和EMP_T之间的关系是一对多关系，也就是说一个部门可以对应多个员工，而一个员工一次只能从属于一个部门。
-- 假如EMP_T中有如下数据
EMPNO | ENAME | DEPTNO
7839 , KING , 10
7935 , MILLER , 10
-- 假如DEPT_T中有如下数据
DEPTNO | DNAME
10 , ACCOUNTING
这个时候如果两表联接后，用DNAME替换EMP_T中DEPTNO的话，很明显DEPTNO里的值都会替换成ACCOUNTING
EMPNO | ENAME | DEPTNO | DNAME
7839 , KING , 10 , ACCOUNTING
7935 , MILLER , 10 , ACCOUNTING
但是，如果DEPT_中有两条DEPTNO为10的数据呢？
-- 假如DEPT_T中有如下数据
DEPTNO | DNAME
10 , ACCOUNTING
10 , MARCKEING
联接的结果就会变成：
EMPNO | ENAME | DEPTNO | DNAME
7839 , KING , 10 , ACCOUNTING
7839 , KING , 10 , MARCKEING
7935 , MILLER , 10 , ACCOUNTING
7935 , MILLER , 10 , MARCKEING
那10号部门里的值到底应该替换成哪个？所以，这样的时候数据库也不知道该怎么处理，只能报出一个ORA-01779: 无法修改与非键值保存表对应的列的错误。
那怎么才能看到这个键值保存表呢？ORACLE提供了一个视图。
SELECT *
FROM DBA_UPDATABLE_COLUMNS
WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'EMP_DEPT_V';
OWNER | TABLE_NAME | COLUMN_NAME | UPDATABLE | INSERTABLE | DELETABLE
SCOTT EMP_DEPT_V EMPNO NO NO NO
SCOTT EMP_DEPT_V ENAME NO NO NO
SCOTT EMP_DEPT_V SAL NO NO NO
SCOTT EMP_DEPT_V DEPTNO NO NO NO
SCOTT EMP_DEPT_V DNAME NO NO NO
从这里面可以看到视图EMP_DEPT_V里的字段都无法变更。
那怎么才能让它们可以变更呢？根据前面说的，如果能保证DEPT_T表里的数据是唯一的就能够更新。也就是说在DEPT_T表上加主键约束或唯一约束。
因为如果不加约束的话，数据库无法判断是否唯一，加上约束其实也就是向数据库告知一下，“放心改吧，不会出错！”
ALTER TABLE DEPT_T
ADD CONSTRAINT PK_DEPT_T PRIMARY KEY (DEPTNO);
再看一下之前的表（如果结果跟下面不一样，重建一下视图）：
SELECT *
FROM DBA_UPDATABLE_COLUMNS
WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'EMP_DEPT_V';
OWNER | TABLE_NAME | COLUMN_NAME | UPDATABLE | INSERTABLE | DELETABLE
SCOTT EMP_DEPT_V EMPNO YES YES YES
SCOTT EMP_DEPT_V ENAME YES YES YES
SCOTT EMP_DEPT_V SAL YES YES YES
SCOTT EMP_DEPT_V DEPTNO YES YES YES
SCOTT EMP_DEPT_V DNAME NO NO NO
这个时候就可以看到，视图中EMP_T表中原来的字段都是可以更新的了。
可以执行一下前面的语句，测试一下。
总结一下：
在对联接视图进行变更的时候，必须保证修改后的值是唯一的，并且这个事实要让数据库知道，而告知数据库的方法是建立主键约束或唯一约束。
那么问题来了，有什么方法能够不添加约束也能变更？并不是所有的表都可以随意建这些约束啊。
解决的方法：
添加/*+ BYPASS_UJVC */ 提示，让ORACLE跳过检查（11g R2之后无效，不建议使用）
改写UPDATE语句，通过其他方式判断后处理
将UPDATE改成MERGE语句
参考ORACLE官方文档的解释：
The concept of a key-preserved table is fundamental to understanding the restrictions on modifying join views. A table is key-preserved if every key of the table can also be a key of the result of the join. So, a key-preserved table has its keys preserved through a join.
An updatable join view (also referred to as a modifiable join view) is a view that contains multiple tables in the top-level FROM clause of the SELECT statement, and is not restricted by the WITH READ ONLY clause.