維護(hù)數(shù)據(jù)完整性.復(fù)習(xí)進(jìn)程

1、維護(hù)數(shù)據(jù)完整性.本章教學(xué)目標(biāo)理解數(shù)據(jù)完整性的基本概念及數(shù)據(jù)完整 性規(guī)則；理解Oracle數(shù)據(jù)庫(kù)的完整性約束的條件、 狀態(tài)和檢查時(shí)間等概念；掌握定義列級(jí)約束和表級(jí)約束的技術(shù)和 方法；掌握添加、修改和刪除約束的技術(shù)和方 法。13.1數(shù)據(jù)完整性概述數(shù)據(jù)完整性數(shù)據(jù)完整性是指存儲(chǔ)在數(shù)據(jù)庫(kù)中數(shù)據(jù)的 一致性和正確性。根據(jù)數(shù)據(jù)完整性作用的數(shù) 據(jù)庫(kù)對(duì)象和范圍不同，可以將數(shù)據(jù)完整性分 為： 實(shí)體完整性(Entity Integrity) 域完整性(Domain Integrity) 引用完整性(Reference Integrity) 用戶定義完整性(User Defined Integrity)數(shù)據(jù)完整性作用：

2、實(shí)體完整性可以保證表內(nèi)每條記錄的唯一性。域完整性可以保證表內(nèi)數(shù)據(jù)項(xiàng)的合理性和有效 性。引用完整性又稱參照完整性，可以保證引用表 和被引用表之間的數(shù)據(jù)一致性。用戶定義完整性是指用戶可以定義不屬于其他 任何完整性分類的特定業(yè)務(wù)規(guī)則。數(shù)據(jù)完整性規(guī)則Oracle應(yīng)用于關(guān)系數(shù)據(jù)庫(kù)數(shù)據(jù)完整性有下列4 種類型的規(guī)則： NULL規(guī)則在插入或修改表的行時(shí)是否允許包 含有NULL的值。唯一性規(guī)則保證插入或修改的記錄在字段值 上的唯一性。引用完整性規(guī)則保證多個(gè)相關(guān)表的一致性。用戶自定義規(guī)則 可實(shí)現(xiàn)復(fù)雜的完整性檢查。數(shù)據(jù)完整性規(guī)則 數(shù)據(jù)完整性的實(shí)現(xiàn)方法: 應(yīng)用程序代碼 完整性約束 數(shù)據(jù)庫(kù)觸發(fā)器數(shù)據(jù)完整性規(guī)貝!I利用完

3、整性約束實(shí)施數(shù)據(jù)完整性規(guī)則有下列優(yōu)點(diǎn)：創(chuàng)建或修改表時(shí)直接通過SQL語(yǔ)句定義完整性約束，無(wú) 需額外的編程，可減少程序性錯(cuò)誤。完整性約束存儲(chǔ)在數(shù)據(jù)字典中，任何進(jìn)入表中的數(shù)據(jù)都 必須接受完整性約束，可以保證數(shù)據(jù)庫(kù)中所有數(shù)據(jù)的完 整性。使用完整性約束可以分離數(shù)據(jù)和程序。 DBA可以通過約束管理語(yǔ)句靈活地設(shè)置完整性約束的 看效性。由于完整性約束存儲(chǔ)在數(shù)據(jù)字典中，DBA、開發(fā)人員 軀用程序可以快速查詢事務(wù)規(guī)則，選擇正確的操作或13.2完整性約束約束條件非空約束（NOT NULL）唯一性約束（UNIQUE）A 主鍵約束（PRIMARY KEY） 外鍵約束（FOREIGN KEY）檢查約束（CHECK）13-

4、2完整性約束約束條件通過EMPLOYEE表的創(chuàng)建說(shuō)明各種約束條件的作用。CREATE TABLE EMPLOYEE(EMPNO NAME SEX DEPTNONUMBER (10) VARCHAR2 (40) CHAR (1),NUMBER (2)PRIMARY KEY,NOT NULL,DEFAULT 10,CHECKSALARY NUMBER (7, 2)(SALARY ENABLE VALIDATE 表中的數(shù)據(jù)必須滿足約束 條件，將要輸入的數(shù)據(jù)也要滿足約束條件。 ENABLE NOVALIDATE Oracle不對(duì)表中的數(shù) 翅進(jìn)行約束驗(yàn)證，只對(duì)將要輸入的數(shù)據(jù)進(jìn)行約 策檢查。 DISABL

5、E VALIDATE約束被禁用，所有將要輸入的數(shù)據(jù)均不作約束檢查，并且不允許作任 何插入、刪除或修改操作。 DISABLE NOVALIDATE 約束禁用，并且不對(duì) 表中的數(shù)據(jù)進(jìn)行約束驗(yàn)證。約束檢查的時(shí)間通常情況下，Oracle在一條DML語(yǔ) 句執(zhí)行結(jié)束后立即進(jìn)行完整性約束檢查， 如果發(fā)現(xiàn)數(shù)據(jù)不滿足約束條件，則回退該 操作。但是，有時(shí)不希望執(zhí)行完一條DML 語(yǔ)句后立即檢查約束，而是多條DML語(yǔ)句執(zhí)行完后再進(jìn)行檢查，這時(shí)可以在事務(wù)提交（COMMIT命令）時(shí)統(tǒng)一檢查約束，女口果任何一個(gè)數(shù)據(jù)不滿足約束條件，Oracle將回退整個(gè)事務(wù)。約束檢查的時(shí)間1 .非延遲約束非延遲約束也叫立即約束，是在一條DM

6、L語(yǔ)句執(zhí)彳丁完后立即進(jìn)彳丁完整性約束檢查，如果發(fā)現(xiàn)數(shù)據(jù)不滿足約束條件，則回退該操作。非延遲約束一經(jīng)定義，不允許將檢查時(shí)間修改在 提交事務(wù)的時(shí)候，除非刪除該約束，重新定義 約束為延遲約束。非延遲約束是約束的默認(rèn)值，如果要顯式 定義非延遲約束可以在定義約束時(shí)使用NO DEFERRABLE 子句。約束檢查的時(shí)間2 可延遲約束可延遲約束是指可以指定約束檢查的時(shí)間，可以在 DML語(yǔ)句執(zhí)行完后不立即進(jìn)行約束檢查，而是延遲到事 務(wù)提交命令CO MMIT執(zhí)行時(shí)檢查?？裳舆t約束是在定義約束時(shí)指定了 DEFERRABLE 子句。該子句有兩種檢驗(yàn)?zāi)Ｊ剑?INITIALLY IMMEDIATE立即檢驗(yàn)?zāi)Ｊ健＿@種模式同

7、非 延遲約束一樣，是在DML語(yǔ)句執(zhí)行完后立即檢查約束。 這是可延遲約束的缺省值。 INITIALLY DEFERRED延遲檢驗(yàn)?zāi)Ｊ?。這種模式不在 DML語(yǔ)句執(zhí)行完后立即檢查約束，而是推遲到事務(wù)提交 命令CO MMIT執(zhí)行完后。約束檢查的時(shí)間2可延遲約束可延遲約束一經(jīng)定義，不允許改變?yōu)榉茄?遲約束，除非刪除該約束，重新定義約束為非 延遲約束。但是通過改變可延遲的檢驗(yàn)?zāi)Ｊ娇?以達(dá)到非延遲約束的效果。設(shè)置驗(yàn)證模式可以 執(zhí)行SET CONSTRAINT命令實(shí)現(xiàn)，如:SQLSET CONSTRAINT ALL IMMEDIATE； 該命令將所有可延遲約束設(shè)置為立即檢驗(yàn)?zāi)Ｊ?。約束檢查的時(shí)間2可延遲約束設(shè)置

8、當(dāng)前會(huì)話級(jí)的檢驗(yàn)?zāi)Ｊ剑梢詧?zhí)行下列命令:ALTER SESSIONSET CONSTRAINT =IMMEDIATE|DEFERRED|DEFAULT；DEFAULT表示保持原來(lái)的檢驗(yàn)?zāi)Ｊ健?3,3實(shí)現(xiàn)約束定義列級(jí)約束【任務(wù)13.1】創(chuàng)建STUDENTS表時(shí)，對(duì)表中各 字段設(shè)置完整性約束，如ID字段定義為該表的 主鍵，并且設(shè)置為可延遲約束，約束為激活狀 態(tài)，NAME、SEX、COURSE和SCORE字段定 義N OTNULL約束。定義列級(jí)約束作用于單個(gè)列上的約束稱之為列級(jí)約束。列級(jí)約束是在 定義列的子句中設(shè)置。列級(jí)約束定義子句的語(yǔ)法為：column datatypeCONSTRAINT con

9、straintNOT NULLIUNIQUE USING INDEX index_clauseIPRIMARY KEY USING INDEX index_clauseREFERENCES schemajtable (column) ONDELETE CASCADE|CHECK (condition)NOT DEFERRABLE|DEFERRABLE INITIALLY IMMEDIATEQEFERREDDISABLE|ENABLE VALIDATE|NOVALIDATE定義列級(jí)約束各關(guān)鍵字和參數(shù)的含義如下： constraint定義約束名。如果缺省，Oracle自動(dòng)命名。 NOT NULL定

10、義NOT NULL約束。女口果為NOT NULL,表示 該列不允許有空值；如果為NULL，表示該列可以有空值。 UNIQUE定義唯一性約束，并可以通過index_clause子句定義索引。 PRIMARY KEY定義主鍵約束，并可以通過index_clause子句 定文蕖專i。 REFERENCES定義外鍵約束，并指出被引用表的表名和列。 ON DELETE CASCADE表示當(dāng)刪除父表的數(shù)據(jù)時(shí)，將子表 中與父表被刪除數(shù)據(jù)相關(guān)的數(shù)據(jù)一起刪除。 CHECK定義檢查約束。13-3實(shí)現(xiàn)約束定義列級(jí)約束各關(guān)鍵字和參數(shù)的含義如下： NOT DEFERRABLE定義非延遲約束。該值是缺省值。 DEFERR

11、ABLE定義可延遲約束。 INITIALLY IMMEDIATE設(shè)置約束檢查為立即檢驗(yàn)?zāi)Ｊ?。該值是缺省值?INITIALLY DEFERRED設(shè)置約束檢查為延遲檢驗(yàn)?zāi)Ｊ健?ENABLE設(shè)置約束為激活狀態(tài)。該值是缺省值。 DISABLE設(shè)置約束為禁用狀態(tài)。 VALIDATE設(shè)置約束為驗(yàn)證狀態(tài)。該值是缺省值。 NOVALIDATE設(shè)置約束為非驗(yàn)證狀態(tài)。定義列級(jí)約束SQLCREATE TABLE TEST.STUDENTS2 (ID VARCHAR2(10)CONSTRAINT STUDENTS_ID_PK PRIMARY KEY DEFERRABLE USING INDEXSTORAGE(INI

12、TIAL 100K NEXT 100K) TABLESPACE INDEXSENABLE,NAME VARCHAR2(10) NOT NULL,SEX VARCHAR2(2) NOT NULL,11 COURSE VARCHAR2(20) NOT NULL,12 SCORE NUMBER(3)13 TABLESPACE USER0114 PCTFREE 2015 PCTUSED 4016 INITRANS 117 MAXTRANS10018 STORAGE (19 INITIAL 400K20 NEXT 400K21 MINEXTENTS 222 MAXEXTENTS 20023 PCTINC

13、REASE 2024 BUFFER_POOL RECYCLE)；13,3實(shí)現(xiàn)約束定義表級(jí)約束【任務(wù)13.2倉(cāng)U建EMPLOYEE表時(shí)，對(duì)表中 FIRST_NAME和LAST_NAME兩個(gè)字段組合在 一起設(shè)置成唯一性約束；該約束為表級(jí)約束。定義表級(jí)約束表級(jí)約束子句的語(yǔ)法為：CONSTRAINT con straintPRIMARY KEY (column , column .)USING INDEX index_clause|UNIQUE (column , column .)USING INDEX index_clauseIFOREIGN KEY (column , column .)REFE

14、RENCES schema.table (column , column .)ON DELETE CASCADEICHECK (condition)NOT DEFERRABLEQEFERRABLE INITIALLY IMMEDIATEQEFERRED DISABLE|ENABLE VALIDATE|NOVALIDATE除了不能在表級(jí)約束子句中不能定義NOT NULL約束外，該子句所有關(guān)鍵字和參數(shù) 的含義同列級(jí)約束定義子句是一樣的。定義表級(jí)約束SQLCREATE TABLE TEST.EMPLOYEE2 (EMPNO NUMBER (10) PRIMARY KEY,3 FIRST_NAME V

15、ARCHAR2 (40) NOT NULL,4 LAST_NAME VARCHAR2 (40) NOT NULL,5 SEX CHAR (1),6 DEPTNO NUMBER (2)7 SALARY NUMBER (7, 2) CHECK(SALARY ADD添加新約束子句。ADD后面跟有表級(jí)約束 定義子句，添加新的約束只能是表級(jí)約束。 MODIFY修改約束狀態(tài)子句?？梢詫F(xiàn)有約束的狀態(tài)改為ENABLE VALIDATE、ENABLENOVALIDATE、DISABLE VALIDATE和DISABLE NOVALIDATEo RENAME重新命名約束名子句。 drop_constraint_

16、clause 刪除已有約束子句。添加約束【任務(wù)13.3】在STUDENTS表中添加一個(gè)檢查約 束，使學(xué)生成績(jī)不能小于0分，也不能大于100分。 為表TEST. STUDENTS的SCORE定義一個(gè)檢查約束。SQL ALTER TABLE TEST.STUDENTS2 ADD (CONSTRAINT SCORE_CHECK3 CHECK(score=0 and scoreALTER TABLE TEST.STUDENTS2 ADD (CONSTRAINT SCORE_CHECK3 CHECK(score=0 and scoreALTER TABLE TEST.STUDENTS2 MODIFY C

17、ONSTRAINT SCORE_CHECK3 ENABLE；結(jié)果顯示為：表已更改。修改約束狀態(tài)修改約束狀態(tài)為驗(yàn)證狀態(tài)：SQLALTER TABLE TEST.STUDENTS2 MODIFY CONSTRAINT SCORE_CHECK3 VALIDATE；結(jié)果顯示為：表已更改。修改約束狀態(tài)設(shè)置約束狀態(tài)為ENABLE VALIDATE：SQLALTER TABLE TEST.STUDENTS2 MODIFY CONSTRAINT SCORE_CHECK3 ENABLE VALIDATE；結(jié)果顯示為：表已更改。修改約束狀態(tài)利用ALTER TABLE語(yǔ)句也可以修改約束的狀態(tài)， 其語(yǔ)法為：ALTER

18、 TABLE schema. table| ENABLE | DISABLE| NOVALIDATE | NOVALIDATE CONSTRAINT constraint| PRIMARY KEY | UNIQUE ( column , column .)USING INDEX index_clause ；修改約束狀態(tài)激活SCORE_CHECK約束，并設(shè)置該約束為驗(yàn)證狀態(tài)：SQLALTER TABLE TEST.STUDENTS2 ENABLE VALIDATE CONSTRAINT SCORE_CHECK；要禁用SCORE_CHECK約束，并設(shè)置該約束為非驗(yàn)證狀態(tài):SQLALTER TABL

19、E TEST.STUDENTS2 DISABLE NOVALIDATE CONSTRAINT3 SCORE_CHECK；修改約束延遲【任務(wù)13.5】為表STUDENTS的SEX字段定 義一個(gè)可延遲約束，使之只能為1或0,異設(shè) 置其為立即檢驗(yàn)?zāi)Ｊ交蜓舆t檢驗(yàn)?zāi)Ｊ?。修改約束延遲為表TEST.STUDENTS的SEX定義一個(gè)非延遲約束。 SQLALTER TABLE TEST.STUDENTS2 ADD (CONSTRAINT SEX_CHECK3 CHECK(SEX=0orSEX=1)4 NOT DEFERRABLE)；結(jié)果顯示為：表已更改。修改約束延遲設(shè)置延遲檢驗(yàn)?zāi)Ｊ健QLALTER TABLE

20、 TEST.STUDENTS2 MODIFY CONSTRAINT SEX_CHECK3 INITIALLY DEFERRED； 設(shè)置為立即檢驗(yàn)?zāi)Ｊ健QLALTER TABLE TEST.STUDENTS2 MODIFY CONSTRAINT SEX_CHECK3 INITIALLY IMMEDIATE；修改約束延遲執(zhí)行下面的命令將當(dāng)前事務(wù)的所有可延遲約束設(shè) 置立即檢驗(yàn)?zāi)Ｊ健QLSET CONSTRAINTS ALL IMMEDIATE；執(zhí)行下面的命令將當(dāng)前事務(wù)的所有可延遲約束設(shè) 置延遲檢驗(yàn)?zāi)Ｊ?。SQLSET CONSTRAINTS ALL DEFERRED；刪除約束【任釦3.6】刪除一個(gè)

21、無(wú)用的約束。刪除約束子句drop_constraint_clause的語(yǔ)法為:DROP| CONSTRAINT constraint| PRIMARY KEY| UNIQUE ( column , column.)|CASCADE KEEP | DROP INDEX；刪除約束其中各關(guān)鍵字的含義為： CONSTRAINT要?jiǎng)h除的約束名。 PRIMARY KEY刪除的主鍵約束。當(dāng)刪除主健約束時(shí)，相關(guān)的 唯一性索引同時(shí)被刪除。 UNIQUE刪除的唯一性約束。當(dāng)刪除唯一性約束時(shí)，相關(guān)的唯 一性索引同時(shí)被刪除。 CASCADE使用該參數(shù)可以刪除引用該表主鍵的所有外鍵約束, 然后刪除唯一性約束和主鍵約束。

22、 DROP INDEX在刪除主鍵約束和唯一性約束時(shí)，Oracle將刪除 約束所對(duì)應(yīng)的索引。該選項(xiàng)為默認(rèn)值。 KEEP INDEX如果只刪除約束而保留索引，可以使用KEEPINDEXo刪除約束刪除 TEST.STUDENTS 表中的 STUDENTS_ID_PK 主鍵約束。 SQLALTER TABLE TEST.STUDENTS2 DROP CONSTRAINT STUDENTS_ID_PK3 CASCADE；執(zhí)行下列命令也可以刪除STUDENTS_ID_PK主鍵約束。SQLALTER TABLE TEST.STUDENTS2 DROP PRIMARY KEY3 CASCADE；刪除約束在具有

23、主鍵約束和外鍵約束的主表和子表之間執(zhí)行DDL 語(yǔ)句或DML語(yǔ)句時(shí)應(yīng)注意以下幾點(diǎn)：在刪除主表之前，必須首先刪除外鍵約束。在截?cái)啵═RUNCATE）主表時(shí)，首先應(yīng)禁止子表的外 鍵。在刪除包含有主表的表空間時(shí)，應(yīng)首先刪除外鍵約束。在刪除主表中的記錄時(shí)，如果在DELETE語(yǔ)句中沒有使 用ON DELETE CASCADE 或ON DELETE SET NULL 子句時(shí)，應(yīng)確保子表中沒有與主鍵對(duì)應(yīng)的記錄。視圖名稱說(shuō)明DBA CONSTRAINTS ALL CONSTRAINTS USER_CONSTRAINTS描述了所有約束的基本 信息，包括約束的名稱、 類型、狀態(tài)、延遲性等。DBA CONS COLU

24、MNS ALL CONS COLUMNS USER_CONS_COLUMN S描述了與約束相關(guān)字段 的信息。13.5查詢約束信息查詢TEST. STUDENTS表中的約束信息。SQLSELECT CONSTRAINT_NAME NAME,2 CONSTRAINTYPE,3 STATUS,4 DEFERRABLE,5 DEFERRED,6 VALIDATED7 FROM ALL_CONSTRAINTS8 WHERE tAbLENAME-STUDENTS、VALIDATED結(jié)果顯示為：NAMEC STATUS DEFERRABLE DEFERREDSTUDENTS_PK P DISABLED NO

25、T DEFERRABLE IMMEDIATE VALIDATED SEX CHECK C ENABLED DEFERRABLEIMMEDIATE VALIDATED13-5查詢約束信息查詢TESTSTUDENTS表中各字段的約束信息。SQLSELECT CONSTRAINT_NAME,2 COLUMN_NAME3 FROM ALL_CONS_COLUMNS4 WHERE TABLE_NAME = STUDENTS 結(jié)果顯示為：CONSTRAINT_NAME COLUMN_NAMESTUDENTS_PKSEX_CHECKIDSEX13.6本章小結(jié)1) 數(shù)據(jù)完整性是指存儲(chǔ)在數(shù)據(jù)庫(kù)中數(shù)據(jù)的一致性和正確 性。數(shù)據(jù)完整性分為實(shí)體完整性、域完整性、引用完 整性和用戶定義完整性。實(shí)體完整性可以保證表內(nèi)每 條記錄的唯一性。域完整性可以保證表內(nèi)數(shù)據(jù)項(xiàng)的合 理性和有