“ORA-01779: 無法修改與非鍵值保存表對應的列”中涉及的概念和解決方法

什么是鍵值保存表(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.

最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個濱河市氛魁,隨后出現(xiàn)的幾起案子暮顺,更是在濱河造成了極大的恐慌,老刑警劉巖秀存,帶你破解...
    沈念sama閱讀 216,470評論 6 501
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件捶码,死亡現(xiàn)場離奇詭異,居然都是意外死亡或链,警方通過查閱死者的電腦和手機惫恼,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 92,393評論 3 392
  • 文/潘曉璐 我一進店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來澳盐,“玉大人祈纯,你說我怎么就攤上這事令宿。” “怎么了盆繁?”我有些...
    開封第一講書人閱讀 162,577評論 0 353
  • 文/不壞的土叔 我叫張陵掀淘,是天一觀的道長。 經(jīng)常有香客問我油昂,道長革娄,這世上最難降的妖魔是什么? 我笑而不...
    開封第一講書人閱讀 58,176評論 1 292
  • 正文 為了忘掉前任冕碟,我火速辦了婚禮拦惋,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘安寺。我一直安慰自己厕妖,他們只是感情好,可當我...
    茶點故事閱讀 67,189評論 6 388
  • 文/花漫 我一把揭開白布挑庶。 她就那樣靜靜地躺著言秸,像睡著了一般。 火紅的嫁衣襯著肌膚如雪迎捺。 梳的紋絲不亂的頭發(fā)上举畸,一...
    開封第一講書人閱讀 51,155評論 1 299
  • 那天,我揣著相機與錄音凳枝,去河邊找鬼抄沮。 笑死,一個胖子當著我的面吹牛岖瑰,可吹牛的內(nèi)容都是我干的叛买。 我是一名探鬼主播,決...
    沈念sama閱讀 40,041評論 3 418
  • 文/蒼蘭香墨 我猛地睜開眼蹋订,長吁一口氣:“原來是場噩夢啊……” “哼率挣!你這毒婦竟也來了?” 一聲冷哼從身側(cè)響起露戒,我...
    開封第一講書人閱讀 38,903評論 0 274
  • 序言:老撾萬榮一對情侶失蹤难礼,失蹤者是張志新(化名)和其女友劉穎,沒想到半個月后玫锋,有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體蛾茉,經(jīng)...
    沈念sama閱讀 45,319評論 1 310
  • 正文 獨居荒郊野嶺守林人離奇死亡,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 37,539評論 2 332
  • 正文 我和宋清朗相戀三年撩鹿,在試婚紗的時候發(fā)現(xiàn)自己被綠了谦炬。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點故事閱讀 39,703評論 1 348
  • 序言:一個原本活蹦亂跳的男人離奇死亡,死狀恐怖键思,靈堂內(nèi)的尸體忽然破棺而出础爬,到底是詐尸還是另有隱情,我是刑警寧澤吼鳞,帶...
    沈念sama閱讀 35,417評論 5 343
  • 正文 年R本政府宣布看蚜,位于F島的核電站,受9級特大地震影響赔桌,放射性物質(zhì)發(fā)生泄漏供炎。R本人自食惡果不足惜,卻給世界環(huán)境...
    茶點故事閱讀 41,013評論 3 325
  • 文/蒙蒙 一疾党、第九天 我趴在偏房一處隱蔽的房頂上張望音诫。 院中可真熱鬧,春花似錦雪位、人聲如沸竭钝。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,664評論 0 22
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽香罐。三九已至,卻和暖如春时肿,著一層夾襖步出監(jiān)牢的瞬間庇茫,已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 32,818評論 1 269
  • 我被黑心中介騙來泰國打工嗜侮, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留港令,地道東北人啥容。 一個月前我還...
    沈念sama閱讀 47,711評論 2 368
  • 正文 我出身青樓锈颗,卻偏偏與公主長得像,于是被迫代替她去往敵國和親咪惠。 傳聞我的和親對象是個殘疾皇子击吱,可洞房花燭夜當晚...
    茶點故事閱讀 44,601評論 2 353

推薦閱讀更多精彩內(nèi)容