什么是鍵值保存表(Key-Preserved Table)硫椰?
在理解什么是鍵值保存表之前笤昨,首先要知道 可更新的聯(lián)接視圖 這個概念被丧,鍵值保存表只是保存了允許更新的字段信息的一張表齿诞。為什么會出現(xiàn)這么一張表呢指蚜?一步一步來看吧麻裳。
理解“視圖”口蝠、“聯(lián)接視圖”、“可更新的聯(lián)接視圖”的概念
視圖可以分為視圖對象和內(nèi)嵌視圖兩種津坑。
- 視圖對象(View Object)
數(shù)據(jù)庫里的視圖對象跟一張表或索引等一樣亚皂,是數(shù)據(jù)庫對象中的一種,它其實是將基于表中原始數(shù)據(jù)的查詢給虛擬了一層之后国瓮,提供給外部查詢的一種對象灭必。
其本質(zhì)是并不保存查詢的實際結(jié)果狞谱,而只是將查詢語句保存在數(shù)據(jù)庫中,當用戶查詢某張視圖的時候禁漓,就會找到并執(zhí)行這個視圖的語句跟衅。
而聯(lián)接視圖其實就是在一個視圖中將兩個表建立關聯(lián)就是了。
-- 基于SCOTT用戶下的表創(chuàng)建測試表
CREATE TABLE EMP_T AS
SELECT *
FROM EMP;
CREATE TABLE DEPT_T AS
SELECT *
FROM DEPT;
-- 創(chuàng)建視圖對象(聯(lián)接視圖)
CREATE OR REPLACE VIEW EMP_DEPT_V
AS
SELECT E.EMPNO
, E.ENAME
, E.SAL
, E.DEPTNO
, D.DNAME
FROM EMP_T E, DEPT_T D
WHERE E.DEPTNO = D.DEPTNO;
- 內(nèi)嵌視圖(Inline View)
在SQL語句中播歼,很多地方都可以寫嵌套的語句伶跷,比如說,F(xiàn)ROM后面可以套括號嵌套其他的語句秘狞,WHERE叭莫、SELECT、UPDATE烁试、INSERT雇初、DELETE后面都是可以寫的。只是寫的位置不同减响,執(zhí)行的方式還有處理時的一些限制不同靖诗,這些SQL中嵌套的SQL被稱為子查詢(Subquery) 。子查詢可以分為如下幾類:
- 內(nèi)嵌視圖:位置位于FROM語句中支示,也相當于一個預處理結(jié)果集的概念
- 標量子查詢(Scalar Subquery):只返還一個結(jié)果值的子查詢刊橘。位于SELECT語句的子查詢只能返還一個值,所以必然是標量子查詢颂鸿,WHERE語句里也會出現(xiàn)這樣的子查詢
- 關聯(lián)子查詢:將查詢的結(jié)果傳遞給子查詢作為參數(shù)的使用的形式
- 一般子查詢:除了那些特殊形式的子查詢以外的一般用法
-- 內(nèi)嵌視圖的示例
-- 聯(lián)接內(nèi)嵌視圖
SELECT *
FROM
(SELECT E.EMPNO
, E.ENAME
, E.SAL
, E.DEPTNO
, D.DNAME
FROM EMP_T E, DEPT_T D
WHERE E.DEPTNO = D.DEPTNO
);
那什么是可更新聯(lián)接視圖(Updatable Join View )?
很簡單促绵,就是將一個聯(lián)接視圖(包括視圖對象和內(nèi)嵌視圖)放到UPADTE語句中這樣的語法形式。
暫時不考慮SQL語句的意義嘴纺,只是測試用
-- 基于視圖對象的
UPDATE EMP_DEPT_V
SET ENAME = ENAME || '-' || DEPTNO;
-- 基于內(nèi)嵌視圖的
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;
跟到一步一步下來绞愚,到這一步的時候,別執(zhí)行這個語句颖医,先來猜猜這兩條語句執(zhí)行的結(jié)果是什么位衩?
理解報錯的原理
按照正常的聯(lián)接邏輯,DEPT_T和EMP_T之間的關系是一對多關系熔萧,也就是說一個部門可以對應多個員工糖驴,而一個員工一次只能從屬于一個部門。
-- 假如EMP_T中有如下數(shù)據(jù)
EMPNO | ENAME | DEPTNO
7839 , KING , 10
7935 , MILLER , 10
-- 假如DEPT_T中有如下數(shù)據(jù)
DEPTNO | DNAME
10 , ACCOUNTING
這個時候如果兩表聯(lián)接后佛致,用DNAME替換EMP_T中DEPTNO的話贮缕,很明顯DEPTNO里的值都會替換成ACCOUNTING
EMPNO | ENAME | DEPTNO | DNAME
7839 , KING , 10 , ACCOUNTING
7935 , MILLER , 10 , ACCOUNTING
但是,如果DEPT_中有兩條DEPTNO為10的數(shù)據(jù)呢俺榆?
-- 假如DEPT_T中有如下數(shù)據(jù)
DEPTNO | DNAME
10 , ACCOUNTING
10 , MARCKEING
聯(lián)接的結(jié)果就會變成:
EMPNO | ENAME | DEPTNO | DNAME
7839 , KING , 10 , ACCOUNTING
7839 , KING , 10 , MARCKEING
7935 , MILLER , 10 , ACCOUNTING
7935 , MILLER , 10 , MARCKEING
那10號部門里的值到底應該替換成哪個感昼?所以,這樣的時候數(shù)據(jù)庫也不知道該怎么處理罐脊,只能報出一個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
里的字段都無法變更宵溅。
那怎么才能讓它們可以變更呢凌简?根據(jù)前面說的,如果能保證DEPT_T表里的數(shù)據(jù)是唯一的就能夠更新恃逻。也就是說在DEPT_T表上加主鍵約束
或唯一約束
雏搂。
因為如果不加約束的話,數(shù)據(jù)庫無法判斷是否唯一寇损,加上約束其實也就是向數(shù)據(jù)庫告知一下凸郑,“放心改吧,不會出錯矛市!”
ALTER TABLE DEPT_T
ADD CONSTRAINT PK_DEPT_T PRIMARY KEY (DEPTNO);
再看一下之前的表(如果結(jié)果跟下面不一樣芙沥,重建一下視圖):
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表中原來的字段都是可以更新的了尘盼。
可以執(zhí)行一下前面的語句,測試一下烦绳。
總結(jié)一下:
在對聯(lián)接視圖進行變更的時候卿捎,必須保證修改后的值是唯一的,并且這個事實要讓數(shù)據(jù)庫知道径密,而告知數(shù)據(jù)庫的方法是建立主鍵約束或唯一約束午阵。
那么問題來了,有什么方法能夠不添加約束也能變更享扔?并不是所有的表都可以隨意建這些約束啊底桂。
解決的方法:
- 添加
/*+ 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.