數(shù)據(jù)庫Oracle第4章2.ppt

數(shù)據(jù)庫應(yīng)用技術(shù) 第四章 數(shù)據(jù)庫對象 與數(shù)據(jù)庫設(shè)計(jì),4.2 約束,4.2.1 約束的作用與分類,在實(shí)際業(yè)務(wù)中，通常會(huì)要求數(shù)據(jù)必須滿足一系列條件，這些條件能夠保證數(shù)據(jù)的一致性和完整性，稱為商業(yè)規(guī)則。實(shí)現(xiàn)商業(yè)規(guī)則有三種方式： 應(yīng)用程序代碼：在客戶端和程序內(nèi)部檢查，這種方式無法完全保證一致性。 觸發(fā)器：在DBMS內(nèi)部，通過程序代碼對所有數(shù)據(jù)的變化都進(jìn)行檢查，這種方式可以保證一致性，但執(zhí)行代價(jià)較大。 約束(Constraints)：對于一些常見、通用的要求，使用設(shè)置的方式建立規(guī)則。這種方式設(shè)置簡單、執(zhí)行代價(jià)較小、一致性強(qiáng)。 數(shù)據(jù)類型：最基本的約束。,ORACLE支持5種類型的完整性約束 NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK,4.2.2 定義與維護(hù)約束,NOT NULL 非空約束是字段級(jí)約束，要求表中所有行在該字段上都必須有數(shù)據(jù)值。 CONSTRAINT NULL|NOT NULL,UNIQUE 唯一性約束要求表中任意兩行在該字段上的值都不相同，但可以為空。 字段級(jí)：CONSTRAINT UNIQUE 可在多個(gè)字段上建立一個(gè)唯一性約束，表約束： CONSTRAINT UNIQUE () 系統(tǒng)自動(dòng)建立唯一索引,刪除有外鍵指向的唯一性約束前應(yīng)：禁用或刪除外鍵。 刪除或禁用唯一性約束時(shí)：通常同時(shí)刪除關(guān)聯(lián)的唯一索引，這會(huì)降低數(shù)據(jù)庫性能。經(jīng)常刪除或禁用唯一性約束可能導(dǎo)致因丟失索引而帶來的性能降低和錯(cuò)誤。如何避免？ 1、在唯一性約束保護(hù)的數(shù)據(jù)列上創(chuàng)建非唯一性索引。 2、添加唯一性約束。,PRIMARY KEY 主鍵約束在表上是唯一的，相當(dāng)于非空的唯一性約束?？梢酝瑫r(shí)在一個(gè)或多個(gè)字段上建立。 字段級(jí)：CONSTRAINT PRIMARY KEY 表級(jí)：CONSTRAINT PRIMARY KEY () 用于表聯(lián)接。如：學(xué)生證號(hào)。 唯一性約束和主鍵約束不會(huì)對完全為NULL的鍵進(jìn)行處理。,FOREIGN KEY 外鍵約束作用在父表和子表上 父表上的主鍵或唯一鍵 子表外鍵的值必須：父表字段或者空 在單一字段上建立外鍵，字段級(jí)約束 CONSTRAINT REFERENCES () 在多個(gè)字段上建立外鍵，表級(jí)約束 CONSTRAINT FOREIGN KEY () REFERENCES () ON DELETE SET NULL ON DELETE子句：聲明父表記錄被刪除后，對子表記錄的處理。缺省情況下禁止在子表記錄還存在的情況下刪除父表的記錄。,使用外鍵時(shí)應(yīng)該考慮的因素： 外鍵約束提供了字段參照完整性 外鍵從句中的字段數(shù)目和每個(gè)字段指定的數(shù)據(jù)類型必須和REFERENCES從句中的字段相匹配。 外鍵不能自動(dòng)創(chuàng)建，需要用戶手動(dòng)創(chuàng)建。 必須有外鍵約束參照表的SELECT權(quán)限或REFERENCES權(quán)限。 一個(gè)表中最多可以有31個(gè)外鍵約束。 在臨時(shí)表中不能使用外鍵約束。,CHECK 檢查約束檢查一行記錄是否滿足條件。 CONSTRAINT CHECK() 列級(jí)檢查約束只能與限制的字段有關(guān)，一個(gè) 表級(jí)檢查約束只能與限制的表中字段有關(guān)，多個(gè) 檢查約束中不能包含子查詢 如：工資獎(jiǎng)金5000，甲的工資乙的工資2000。,是可選的，如果沒提供Oracle將產(chǎn)生一個(gè)以SYS_開始的唯一的名字。,舉例 CREATE TABLE agents( aid CHAR(3) NOT NULL, lid CHAR(3) NULL, salary NUMBER(4,0) PRIMARY KEY(aid), FOREIGN KEY(lid) REFERNCE locations(lid), CHECK(salary0 AND UPPER(aid)=aid) );,約束存在于數(shù)據(jù)字典中，在執(zhí)行SQL或PL/SQL期間使用。 增加約束 NOT NULL使用ALTER的MODIFY子句，其它的使用ALTER的 ADD子句。 CREATE TABLE t(name VARCHAR2(20); ALTER TABLE t MODIFY name NOT NULL; ALTER TABLE t ADD CONSTRAINT t_uk_1 UNIQUE(name); ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY(name); ALTER TABLE t ADD id VARCHAR2(20) CONSTRAINT t_fk_1 REFERENCES tf(id);,修改約束名 ALTER TABLE RENAME CONSTRAINT TO ; 刪除約束 ALTER TABLE DROP CONSTRAINT ; 禁止約束 ALTER TABLE DISABLE CONSTRAINT CASCADE; 啟用約束 ALTER TABLE ENABLE CONSTRAINT ;,4.2.3 約束的建立原則,1、根據(jù)數(shù)據(jù)庫完整性約束的類型確定其實(shí)現(xiàn)的系統(tǒng)層次和方式，并提前考慮對系統(tǒng)性能的影響。一般情況下，靜態(tài)約束應(yīng)盡量包含在數(shù)據(jù)庫模式中，而動(dòng)態(tài)約束由應(yīng)用程序?qū)崿F(xiàn)。 2、實(shí)體完整性約束、參照完整性約束是關(guān)系數(shù)據(jù)庫最重要的完整性約束，在不影響系統(tǒng)關(guān)鍵性能的前提下需盡量應(yīng)用。用一定的時(shí)間和空間來換取系統(tǒng)的易用性是值得的。 3、要慎用目前主流DBMS都支持的觸發(fā)器功能，一方面由于觸發(fā)器的性能開銷較大，另一方面，觸發(fā)器的多級(jí)觸發(fā)不好控制，容易發(fā)生錯(cuò)誤，非用不可時(shí)，最好使用Before型語句級(jí)觸發(fā)器。,4、在需求分析階段就必須制定完整性約束的命名規(guī)范，盡量使用有意義的英文單詞、縮寫詞、表名、列名及下劃線等組合，使其易于識(shí)別和記憶。如果使用CASE工具，一般有缺省的規(guī)則，可在此基礎(chǔ)上修改使用。 5、要根據(jù)業(yè)務(wù)規(guī)則對數(shù)據(jù)庫完整性進(jìn)行細(xì)致的測試，以盡早排除隱含的完整性約束間的沖突和對性能的影響。 6、要有專職的數(shù)據(jù)庫設(shè)計(jì)小組，自始至終負(fù)責(zé)數(shù)據(jù)庫的分析、設(shè)計(jì)、測試、實(shí)施及早期維護(hù)。數(shù)據(jù)庫設(shè)計(jì)人員不僅負(fù)責(zé)基于DBMS的數(shù)據(jù)庫完整性約束的設(shè)計(jì)實(shí)現(xiàn)，還要負(fù)責(zé)對應(yīng)用軟件實(shí)現(xiàn)的數(shù)據(jù)庫完整性約束進(jìn)行審核。 7、應(yīng)采用合適的CASE工具來降低數(shù)據(jù)庫設(shè)計(jì)各階段的工作量。好的CASE工具能夠支持整個(gè)數(shù)據(jù)庫的生命周期，這將使數(shù)據(jù)庫設(shè)計(jì)人員的工作效率得到很大提高，同時(shí)也容易與用戶溝通。,練習(xí)題,寫出圖書館管理系統(tǒng)中建表的DDL語句及相關(guān)的約束。,4.3 視圖,視圖不存儲(chǔ)數(shù)據(jù)， Oracle中將視圖以USER_VIEWS中查詢語句的形式存儲(chǔ)，在使用時(shí)組織基表中的數(shù)據(jù)。 視圖的數(shù)據(jù)可以來自： 1至多個(gè)基表、視圖。,4.3.1 視圖的作用,使用視圖的原因 權(quán)限控制 簡化查詢和應(yīng)用程序 兼容數(shù)據(jù)庫設(shè)計(jì)的修改,4.3.2 創(chuàng)建、刪除與使用視圖,視圖的創(chuàng)建 CREATE OR REPLACE FORCE|NOFORCE VIEW () AS WITH CHECK OPTIONS CONSTRAINT WITH READ ONLY; OR REPLACE：對已有同名視圖進(jìn)行替換和修改。 FORCE：不管基表是否存在都創(chuàng)建視圖。 NOFORCE：只有基表存在時(shí)，才創(chuàng)建視圖。 ：為的列定義的新名字，沒有則用返回的字段名定義。,CREATE OR REPLACE FORCE|NOFORCE VIEW () AS WITH CHECK OPTIONS CONSTRAINT WITH READ ONLY; ：可以包含連接、分組及子查詢。 WITH CHECK OPTIONS：對視圖插入或修改時(shí)，必須滿足約束條件。 WITH READ ONLY：視圖只讀。,舉例 CREATE VIEW v1(aid, uname, salary) AS SELECT aid, UPPER(aname), salary FROM agents WHERE salary3000; SELECT * FROM v1; 執(zhí)行時(shí)，系統(tǒng)會(huì)找出v1的定義，相當(dāng)于在基表上執(zhí)行。,刪除視圖 DROP VIEW CASCADE|RESTRICT; CASCADE：自動(dòng)刪除依賴該視圖的對象，如其它視圖。 RESTRICT：如果有依賴對象存在，拒絕刪除該視圖。 權(quán)限：視圖的建立者或DROP ANY VIEW權(quán)限。 視圖的刪除不影響基表，不會(huì)丟失數(shù)據(jù)。 舉例 DROP VIEW v1;,視圖的優(yōu)點(diǎn) 簡單性 簡化用戶對數(shù)據(jù)的理解和操作 安全性 通過視圖，可以令用戶只能查詢和修改他們能見到的數(shù)據(jù)，即限制在數(shù)據(jù)的不同子集上。 邏輯數(shù)據(jù)獨(dú)立性 使應(yīng)用程序和數(shù)據(jù)庫表在一定程度上獨(dú)立，即程序-視圖-數(shù)據(jù)庫表。,視圖的缺點(diǎn) 性能 由多表復(fù)雜查詢定義的視圖 修改限制 由多表復(fù)雜查詢定義的視圖，可能是不可修改的。 定義數(shù)據(jù)庫對象時(shí)，應(yīng)該權(quán)衡視圖的優(yōu)點(diǎn)和缺點(diǎn)，合理地定義視圖。,4.3.3 視圖上的DML操作,視圖的分類,復(fù)雜視圖上的DML操作的準(zhǔn)則 不允許違反約束的DML操作。 不能將一個(gè)值添加到包含算術(shù)表達(dá)式的列中。 在非key-preserved表上不允許DML操作。 在包括聚組函數(shù)、GROUP BY子句、ROWNUM偽列或DISTINCT關(guān)鍵字的視圖上不允許DML操作。,舉例 UPDATE v1 SET salary = 1000 WHERE aid = A01; SELECT * FROM v1; 此時(shí)，A01不存在了，salary 3000, 對表的操作將及時(shí)反映在視圖中。,CREATE VIEW v1(aid, uname,salary) AS SELECT aid, UPPER(aname), salary FROM agents WHERE salary3000;,舉例 CREATE VIEW v2(cname, ordno, dollars) AS SELECT ame, o.ordno, c.dollars FROM customers c, orders o WHERE c.cid = o.cid; UPDATE v2 SET dollars = 100 WHERE cname =TOM;WHERE ordno = 1012; SET cname = AA WHERE ordno = 1012;,在此，orders是鍵保留表，可以修改。,在此，customers表不是鍵保留表，不可以修改。,舉例 CREATE VIEW v3 AS SELECT cid, UPPER(aname) uname FROM customers; UPDATE v3 SET uname = A WHERE cid = C01;,不能對uname對應(yīng)