1、Oracle體系結(jié)構(gòu)與管理第十三章 維護數(shù)據(jù)完整性,本章要點,數(shù)據(jù)完整性概述 完整性約束 實現(xiàn)約束 維護約束 查詢約束信息,本章教學目標,理解數(shù)據(jù)完整性的基本概念及數(shù)據(jù)完整性規(guī)則； 理解Oracle數(shù)據(jù)庫的完整性約束的條件、狀態(tài)和檢查時間等概念； 掌握定義列級約束和表級約束的技術和方法； 掌握添加、修改和刪除約束的技術和方法。,13.1 數(shù)據(jù)完整性概述,數(shù)據(jù)完整性 數(shù)據(jù)完整性是指存儲在數(shù)據(jù)庫中數(shù)據(jù)的一致性和正確性。根據(jù)數(shù)據(jù)完整性作用的數(shù)據(jù)庫對象和范圍不同，可以將數(shù)據(jù)完整性分為: 實體完整性(Entity Integrity) 域完整性(Domain Integrity) 引用完整性(Refer

2、ence Integrity) 用戶定義完整性(User Defined Integrity),13.1 數(shù)據(jù)完整性概述,數(shù)據(jù)完整性 作用： 實體完整性可以保證表內(nèi)每條記錄的唯一性。 域完整性可以保證表內(nèi)數(shù)據(jù)項的合理性和有效性。 引用完整性又稱參照完整性，可以保證引用表和被引用表之間的數(shù)據(jù)一致性。 用戶定義完整性是指用戶可以定義不屬于其他任何完整性分類的特定業(yè)務規(guī)則。,13.1 數(shù)據(jù)完整性概述,數(shù)據(jù)完整性規(guī)則 Oracle應用于關系數(shù)據(jù)庫數(shù)據(jù)完整性有下列4種類型的規(guī)則： NULL規(guī)則 在插入或修改表的行時是否允許包含有NULL的值。 唯一性規(guī)則 保證插入或修改的記錄在字段值上的唯一性。 引用完

3、整性規(guī)則 保證多個相關表的一致性。 用戶自定義規(guī)則 可實現(xiàn)復雜的完整性檢查。,13.1 數(shù)據(jù)完整性概述,數(shù)據(jù)完整性規(guī)則 數(shù)據(jù)完整性的實現(xiàn)方法： 應用程序代碼 完整性約束 數(shù)據(jù)庫觸發(fā)器,13.1 數(shù)據(jù)完整性概述,數(shù)據(jù)完整性規(guī)則 利用完整性約束實施數(shù)據(jù)完整性規(guī)則有下列優(yōu)點： 創(chuàng)建或修改表時直接通過SQL語句定義完整性約束，無需額外的編程，可減少程序性錯誤。 完整性約束存儲在數(shù)據(jù)字典中，任何進入表中的數(shù)據(jù)都必須接受完整性約束，可以保證數(shù)據(jù)庫中所有數(shù)據(jù)的完整性。 使用完整性約束可以分離數(shù)據(jù)和程序。 DBA可以通過約束管理語句靈活地設置完整性約束的有效性。 由于完整性約束存儲在數(shù)據(jù)字典中，DBA、開發(fā)人

4、員和應用程序可以快速查詢事務規(guī)則，選擇正確的操作或數(shù)據(jù)。,13.2 完整性約束,約束條件 非空約束（NOT NULL） 唯一性約束（UNIQUE） 主鍵約束（PRIMARY KEY） 外鍵約束（FOREIGN KEY） 檢查約束（CHECK）,13.2 完整性約束,約束條件 通過EMPLOYEE表的創(chuàng)建說明各種約束條件的作用。 CREATE TABLE EMPLOYEE （EMPNO NUMBER（10） PRIMARY KEY， NAME VARCHAR2（40） NOT NULL， SEX CHAR（1）， DEPTNO NUMBER（2） DEFAULT 10， SALARY NUMBE

5、R（7，2） CHECK（SALARY1000000.00）， SOCNUM CHAR(14) UNIQUE， FOREIGN KEY （DEPTNO） REFERENCES DEPT（DEPTNO） TABLESPACE USERS；,13.2 完整性約束,約束狀態(tài) 1.激活和禁用 激活狀態(tài)（ENABLE） 在這種狀態(tài)下，完整性約束被激活，Oracle將對輸入到表中的數(shù)據(jù)進行完整性約束檢查，如果數(shù)據(jù)不符合約束，則不允許保存到表中，所執(zhí)行的操作被回退。一般情況下，完整性約束總是處于被激活狀態(tài)以保證數(shù)據(jù)完整性。 禁用狀態(tài)（DISABLE） 在這種狀態(tài)下，完整性約束被禁止，Oracle不對輸入到表

6、中的數(shù)據(jù)進行完整性約束檢查，任何數(shù)據(jù)都可以存入表中。,13.2 完整性約束,約束狀態(tài) 2.驗證和非驗證 驗證狀態(tài)（VALIDATE） 在這種狀態(tài)下，在定義或激活約束時，Oracle檢查表中該列中的數(shù)據(jù)是否滿足約束條件。 非驗證狀態(tài)（NOVALIDATE） 在這種狀態(tài)下，在定義或激活約束時，Oracle不檢查表中該列中的數(shù)據(jù)是否滿足約束條件。,13.2 完整性約束,約束狀態(tài) 根據(jù)以上情況可以將完整性約束組合成4種狀態(tài) ： ENABLE VALIDATE 表中的數(shù)據(jù)必須滿足約束條件，將要輸入的數(shù)據(jù)也要滿足約束條件。 ENABLE NOVALIDATE Oracle不對表中的數(shù)據(jù)進行約束驗證，只對將

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

8、完整性約束,約束檢查的時間 1.非延遲約束 非延遲約束也叫立即約束，是在一條DML語句執(zhí)行完后立即進行完整性約束檢查，如果發(fā)現(xiàn)數(shù)據(jù)不滿足約束條件，則回退該操作。非延遲約束一經(jīng)定義，不允許將檢查時間修改在提交事務的時候，除非刪除該約束，重新定義約束為延遲約束。 非延遲約束是約束的默認值，如果要顯式定義非延遲約束可以在定義約束時使用NO DEFERRABLE子句。,13.2 完整性約束,約束檢查的時間 2.可延遲約束 可延遲約束是指可以指定約束檢查的時間，可以在DML語句執(zhí)行完后不立即進行約束檢查，而是延遲到事務提交命令COMMIT執(zhí)行時檢查。 可延遲約束是在定義約束時指定了DEFERRABLE子

9、句。該子句有兩種檢驗模式： INITIALLY IMMEDIATE 立即檢驗模式。這種模式同非延遲約束一樣，是在DML語句執(zhí)行完后立即檢查約束。這是可延遲約束的缺省值。 INITIALLY DEFERRED 延遲檢驗模式。這種模式不在DML語句執(zhí)行完后立即檢查約束，而是推遲到事務提交命令COMMIT執(zhí)行完后。,13.2 完整性約束,約束檢查的時間 2.可延遲約束 可延遲約束一經(jīng)定義，不允許改變?yōu)榉茄舆t約束，除非刪除該約束，重新定義約束為非延遲約束。但是通過改變可延遲的檢驗模式可以達到非延遲約束的效果。設置驗證模式可以執(zhí)行SET CONSTRAINT命令實現(xiàn)，如： SQLSET CONSTRAI

10、NT ALL IMMEDIATE； 該命令將所有可延遲約束設置為立即檢驗模式。,13.2 完整性約束,約束檢查的時間 2.可延遲約束 設置當前會話級的檢驗模式，可以執(zhí)行下列命令： ALTER SESSION SET CONSTRAINT =IMMEDIATE|DEFERRED|DEFAULT； DEFAULT表示保持原來的檢驗模式。,13.3 實現(xiàn)約束,定義列級約束 【任務13.1】創(chuàng)建STUDENTS表時，對表中各字段設置完整性約束，如ID字段定義為該表的主鍵，并且設置為可延遲約束，約束為激活狀態(tài)，NAME、SEX、COURSE和SCORE字段定義NOT NULL約束。,13.3 實現(xiàn)約束,

11、定義列級約束 作用于單個列上的約束稱之為列級約束。列級約束是在定義列的子句中設置。列級約束定義子句的語法為： column datatype CONSTRAINT constraint NOT NULL |UNIQUE USING INDEX index_clause |PRIMARY KEY USING INDEX index_clause |REFERENCES schema.table (column) ON DELETE CASCADE |CHECK (condition) NOT DEFERRABLE|DEFERRABLE INITIALLY IMMEDIATE|DEFERRED D

12、ISABLE|ENABLE VALIDATE|NOVALIDATE,13.3 實現(xiàn)約束,定義列級約束 各關鍵字和參數(shù)的含義如下： constraint 定義約束名。如果缺省，Oracle自動命名。 NOT NULL 定義NOT NULL約束。如果為NOT NULL，表示該列不允許有空值；如果為NULL，表示該列可以有空值。 UNIQUE 定義唯一性約束，并可以通過index_clause子句定義索引。 PRIMARY KEY 定義主鍵約束，并可以通過index_clause子句定義索引。 REFERENCES 定義外鍵約束，并指出被引用表的表名和列。 ON DELETE CASCADE 表示當

13、刪除父表的數(shù)據(jù)時，將子表中與父表被刪除數(shù)據(jù)相關的數(shù)據(jù)一起刪除。 CHECK 定義檢查約束。,13.3 實現(xiàn)約束,定義列級約束 各關鍵字和參數(shù)的含義如下： NOT DEFERRABLE 定義非延遲約束。該值是缺省值。 DEFERRABLE 定義可延遲約束。 INITIALLY IMMEDIATE 設置約束檢查為立即檢驗模式。該值是缺省值。 INITIALLY DEFERRED 設置約束檢查為延遲檢驗模式。 ENABLE 設置約束為激活狀態(tài)。該值是缺省值。 DISABLE 設置約束為禁用狀態(tài)。 VALIDATE 設置約束為驗證狀態(tài)。該值是缺省值。 NOVALIDATE 設置約束為非驗證狀態(tài)。,13

14、.3 實現(xiàn)約束,定義列級約束 SQLCREATE TABLE TEST.STUDENTS 2 (ID VARCHAR2(10) 3 CONSTRAINT STUDENTS_ID_PK PRIMARY KEY 4 DEFERRABLE 5 USING INDEX 6 STORAGE(INITIAL 100K NEXT 100K) 7 TABLESPACE INDEXS 8 ENABLE, 9 NAME VARCHAR2(10) NOT NULL, 10 SEX VARCHAR2(2) NOT NULL, 11 COURSE VARCHAR2(20) NOT NULL, 12 SCORE NUMB

15、ER(3) 13 TABLESPACE USER01 14 PCTFREE 20 15 PCTUSED 40 16 INITRANS 1 17 MAXTRANS 100 18 STORAGE ( 19 INITIAL 400K 20 NEXT 400K 21 MINEXTENTS 2 22 MAXEXTENTS 200 23 PCTINCREASE 20 24 BUFFER_POOL RECYCLE)；,13.3 實現(xiàn)約束,定義表級約束 【任務13.2】創(chuàng)建EMPLOYEE表時，對表中FIRST_NAME和LAST_NAME兩個字段組合在一起設置成唯一性約束，該約束為表級約束。,13.3 實現(xiàn)

16、約束,定義表級約束 表級約束子句的語法為： CONSTRAINT constraint PRIMARY KEY (column , column . ) USING INDEX index_clause |UNIQUE (column , column . ) USING INDEX index_clause |FOREIGN KEY (column , column . ) REFERENCES schema.table (column , column . ) ON DELETE CASCADE |CHECK (condition) NOT DEFERRABLE|DEFERRABLE IN

17、ITIALLY IMMEDIATE|DEFERRED DISABLE|ENABLE VALIDATE|NOVALIDATE 除了不能在表級約束子句中不能定義NOT NULL約束外，該子句所有關鍵字和參數(shù)的含義同列級約束定義子句是一樣的。,13.3 實現(xiàn)約束,定義表級約束 SQLCREATE TABLE TEST.EMPLOYEE 2 （EMPNO NUMBER（10） PRIMARY KEY， 3 FIRST_NAME VARCHAR2（40） NOT NULL， 4 LAST_NAME VARCHAR2（40） NOT NULL， 5 SEX CHAR（1）， 6 DEPTNO NUMBER

18、（2） 7 SALARY NUMBER（7，2） CHECK（SALARY1000000.00）， 8 SOCNUM CHAR(14) UNIQUE， 9 CONSTRAINT NAME UNIQUE(FIRST_NAME，LAST_NAME) 10 DEFERRABLE 11 DISABLE VALIDATE） 12 TABLESPACE USERS；,13.4 維護約束,維護約束命令 維護約束語句的語法為： ALTER TABLE schema. table ADD out_of_line_constraint out_of_line_constraint . MODIFY CONSTRA

19、INT constraint | PRIMARY KEY | UNIQUE (column) , UNIQUE (column) . DEFERRABLE INITIALLY IMMEDIATE|DEFERRED DISABLE|ENABLE VALIDATE|NOVALIDATE RENAME CONSTRAINT old_name TO new_name drop_constraint_clause ；,13.4 維護約束,維護約束命令 維護約束命令包含了4個子句，其功能為： ADD 添加新約束子句。ADD后面跟有表級約束定義子句，添加新的約束只能是表級約束。 MODIFY 修改約束狀態(tài)子

20、句?？梢詫F(xiàn)有約束的狀態(tài)改為ENABLE VALIDATE、ENABLE NOVALIDATE、DISABLE VALIDATE和DISABLE NOVALIDATE。 RENAME 重新命名約束名子句。 drop_constraint_clause 刪除已有約束子句。,13.4 維護約束,添加約束 【任務13.3】 在STUDENTS表中添加一個檢查約束，使學生成績不能小于0分，也不能大于100分。 為表TEST.STUDENTS的SCORE定義一個檢查約束。 SQL ALTER TABLE TEST.STUDENTS 2 ADD (CONSTRAINT SCORE_CHECK 3 CHEC

21、K(score=0 and score=100)； 結(jié)果顯示為： 表已更改。,13.4 維護約束,修改約束狀態(tài) 【任務13.4】 對STUDENTS表中SCORE_CHECK約束的狀態(tài)進行設置，使之禁用、激活、驗證或非驗證。 在創(chuàng)建該約束時將其設置為禁用狀態(tài)： SQLALTER TABLE TEST.STUDENTS 2 ADD (CONSTRAINT SCORE_CHECK 3 CHECK(score=0 and score=100) DISABLE)； 結(jié)果顯示為： 表已更改。,13.4 維護約束,修改約束狀態(tài) 激活該約束： SQLALTER TABLE TEST.STUDENTS 2 M

22、ODIFY CONSTRAINT SCORE_CHECK 3 ENABLE； 結(jié)果顯示為： 表已更改。,13.4 維護約束,修改約束狀態(tài) 修改約束狀態(tài)為驗證狀態(tài)： SQLALTER TABLE TEST.STUDENTS 2 MODIFY CONSTRAINT SCORE_CHECK 3 VALIDATE； 結(jié)果顯示為： 表已更改。,13.4 維護約束,修改約束狀態(tài) 修改約束狀態(tài)為非驗證狀態(tài)： SQLALTER TABLE TEST.STUDENTS 2 MODIFY CONSTRAINT SCORE_CHECK 3 NOVALIDATE； 結(jié)果顯示為： 表已更改。,13.4 維護約束,修改約

23、束狀態(tài) 設置約束狀態(tài)為ENABLE VALIDATE： SQLALTER TABLE TEST.STUDENTS 2 MODIFY CONSTRAINT SCORE_CHECK 3 ENABLE VALIDATE； 結(jié)果顯示為： 表已更改。,13.4 維護約束,修改約束狀態(tài) 利用ALTER TABLE語句也可以修改約束的狀態(tài)，其語法為： ALTER TABLE schema. table | ENABLE | DISABLE | NOVALIDATE | NOVALIDATE CONSTRAINT constraint | PRIMARY KEY | UNIQUE ( column , col

24、umn . ) USING INDEX index_clause ；,13.4 維護約束,修改約束狀態(tài) 激活SCORE_CHECK約束，并設置該約束為驗證狀態(tài)： SQLALTER TABLE TEST.STUDENTS 2 ENABLE VALIDATE CONSTRAINT SCORE_CHECK； 要禁用SCORE_CHECK約束，并設置該約束為非驗證狀態(tài)： SQLALTER TABLE TEST.STUDENTS 2 DISABLE NOVALIDATE CONSTRAINT 3 SCORE_CHECK；,13.4 維護約束,修改約束延遲 【任務13.5】 為表STUDENTS的SEX字

25、段定義一個可延遲約束，使之只能為1或0，并設置其為立即檢驗模式或延遲檢驗模式。,13.4 維護約束,修改約束延遲 為表TEST.STUDENTS的SEX定義一個非延遲約束。 SQLALTER TABLE TEST.STUDENTS 2 ADD （CONSTRAINT SEX_CHECK 3 CHECK(SEX=0 or SEX=1) 4 NOT DEFERRABLE）； 結(jié)果顯示為： 表已更改。,13.4 維護約束,修改約束延遲 設置延遲檢驗模式。 SQLALTER TABLE TEST.STUDENTS 2 MODIFY CONSTRAINT SEX_CHECK 3 INITIALLY DE

26、FERRED； 設置為立即檢驗模式。 SQLALTER TABLE TEST.STUDENTS 2 MODIFY CONSTRAINT SEX_CHECK 3 INITIALLY IMMEDIATE；,13.4 維護約束,修改約束延遲 執(zhí)行下面的命令將當前事務的所有可延遲約束設置立即檢驗模式。 SQLSET CONSTRAINTS ALL IMMEDIATE； 執(zhí)行下面的命令將當前事務的所有可延遲約束設置延遲檢驗模式。 SQLSET CONSTRAINTS ALL DEFERRED；,13.4 維護約束,刪除約束 【任務13.6】 刪除一個無用的約束。 刪除約束子句drop_constrain

27、t_clause的語法為： DROP | CONSTRAINT constraint | PRIMARY KEY | UNIQUE ( column , column. ) | CASCADE KEEP | DROP INDEX；,13.4 維護約束,刪除約束 其中各關鍵字的含義為： CONSTRAINT 要刪除的約束名。 PRIMARY KEY 刪除的主鍵約束。當刪除主健約束時，相關的唯一性索引同時被刪除。 UNIQUE 刪除的唯一性約束。當刪除唯一性約束時，相關的唯一性索引同時被刪除。 CASCADE 使用該參數(shù)可以刪除引用該表主鍵的所有外鍵約束，然后刪除唯一性約束和主鍵約束。 DROP

28、INDEX 在刪除主鍵約束和唯一性約束時，Oracle將刪除約束所對應的索引。該選項為默認值。 KEEP INDEX 如果只刪除約束而保留索引，可以使用KEEP INDEX。,13.4 維護約束,刪除約束 刪除TEST.STUDENTS表中的STUDENTS_ID_PK主鍵約束。 SQLALTER TABLE TEST.STUDENTS 2 DROP CONSTRAINT STUDENTS_ID_PK 3 CASCADE； 執(zhí)行下列命令也可以刪除STUDENTS_ID_PK主鍵約束。 SQLALTER TABLE TEST.STUDENTS 2 DROP PRIMARY KEY 3 CASCA

29、DE；,13.4 維護約束,刪除約束 在具有主鍵約束和外鍵約束的主表和子表之間執(zhí)行DDL語句或DML語句時應注意以下幾點： 在刪除主表之前，必須首先刪除外鍵約束。 在截斷（TRUNCATE）主表時，首先應禁止子表的外鍵。 在刪除包含有主表的表空間時，應首先刪除外鍵約束。 在刪除主表中的記錄時，如果在DELETE語句中沒有使用ON DELETE CASCADE 或ON DELETE SET NULL子句時，應確保子表中沒有與主鍵對應的記錄。,13.5 查詢約束信息,13.5 查詢約束信息,查詢TEST.STUDENTS表中的約束信息。 SQLSELECT CONSTRAINT_NAME NAME

30、， 2 CONSTRAINT_TYPE， 3 STATUS， 4 DEFERRABLE， 5 DEFERRED， 6 VALIDATED 7 FROM ALL_CONSTRAINTS 8 WHERE TABLE_NAME=STUDENTS； 結(jié)果顯示為： NAME C STATUS DEFERRABLE DEFERRED VALIDATED - - - - - - STUDENTS_PK P DISABLED NOT DEFERRABLE IMMEDIATE VALIDATED SEX_CHECK C ENABLED DEFERRABLE IMMEDIATE VALIDATED,13.5 查詢約束信息,查詢TEST.STUDENTS表中各字段的約束信息。 SQLSELECT CONSTRAINT_NAME， 2 COLUMN_NAME 3 FROM ALL_CONS_COL