《Oracle大型數(shù)據(jù)庫基礎(chǔ)開發(fā)教程》（微課視頻版）課件 第5章 數(shù)據(jù)庫完整性

第5章數(shù)據(jù)庫完整性5.1完整性概述一、實體完整性實體完整性指的是如果一個字段是主鍵字段，則此字段不能取空值（NULL）。例如：EMP表中雇員編號EMPNO為主鍵，則該字段在輸入時不能取空（NULL），如表5-1所示。5.1完整性概述二、域完整性域完整性指的是數(shù)據(jù)類型、范圍、長度等約束。例如：表5-1EMP表的定義如下所示：名稱是否為空?類型-----------------------------------------------------------------EMPNONOTNULLNUMBER(4)ENAMEVARCHAR2(10JOBVARCHAR2(9)MGRNUMBER(4)HIREDATEDATESALNUMBER(7,2)COMMNUMBER(7,2)DEPTNONUMBER(2)5.1完整性概述三、參照完整性1.外鍵一個字段或一組字段不是表R的主鍵，但它和另外一個表S的主鍵相對應(yīng)，則該字段或字段組合為R的外鍵。例如：表5-1EMP表中的DEPTNO不是EMP的主鍵但是它和另一表6-2DEPT表中的DEPTNO相對應(yīng)（EMP表中的DEPTNO的值都取自DEPT表中的DEPNO字段）。DEPT表如表5-2所示：5.1完整性概述2.參照完整性若一個表的外鍵和另一個表的主鍵相對應(yīng)，則該表在外鍵上的取值為：或者取空值（NULL）（外鍵的每個屬性值均為空值）?；蛘叩扔诹硗庖粋€表的某個元組的主鍵值。例：表6-1EMP表中的外鍵DEPTNO的取值，或者取NULL表示沒有所屬的部門，或者取自表6.2DEPT表中的DEPTNO中的某一個字段值（10，20,30）。四、用戶定義的完整性用戶定義的完整性指的是用戶根據(jù)某一具體應(yīng)用設(shè)置的約束條件。Oracle中必須提供這樣的定義機制，來實現(xiàn)用戶定義的約束條件。例：表6.1EMP表中如果定義COMM在0-5000的范圍就屬于用戶定義的完整性約束。5.2約束類型Oracle數(shù)據(jù)完整性約束可以分為主鍵（PRIMARYKEY）、非空（NOTNULL）、唯一（UNIQUE）和檢查（CHECK）約束和外鍵（FOREIGNKEY）約束五種類型。一、主鍵約束候選鍵是唯一標(biāo)識表中每一條記錄的字段或字段組合。若一個表有多個候選鍵，則選定其中一個為主鍵，定義主鍵的子句為PRIMARYKEY，指定了主鍵后，包含在主鍵里的字段為主鍵字段，主鍵字段不為空，主鍵值唯一。1.創(chuàng)建表時定義創(chuàng)建表時定義主鍵約束的語法格式如下：CREATETABLE[schema.]table_name(columndatatype[DEFAULTexpr][[CONSTRAINTconstraint_name]PRIMARYKEY],--列級約束

...[[CONSTRAINTconstraint_name]PRIMARYKEY（colum1,colum2,…）]);--表級約束5.2約束類型例：創(chuàng)建學(xué)生表student，包括學(xué)號(Sno)、姓名(Sname)、Sage（年齡）、性別(Ssex)、出生日期(Birthday)、系院(Sdept)等學(xué)生信息。其中學(xué)號(Sno)為主鍵。CREATETABLESTUDENT(Snochar(6)PRIMARYKEY,Snamevarchar2(8),Sagenumber(2,0),Ssexchar(2),Birthdaydate,Sdeptchar(20));5.2約束類型指定約束名CREATETABLESTUDENT(Snochar(6)CONSTRAINTPK_SnoPRIMARYKEY,Snamevarchar2(8),Sagenumber(2,0),Ssexchar(2),Birthdaydate,Sdeptchar(20));5.2約束類型表級約束例：創(chuàng)建一選課表SC,包括學(xué)號（Sno），課程號（Cno），成績（Grade），主鍵為（Sno,Cno）。

CREATETABLESC(Snovarchar(12),Cnovarchar(3),Gradenumber(3,0),CONSTRAINTPK_SCPRIMARY(Sno,Cno));5.2約束類型2.使用ALTERTABLE創(chuàng)建表之后可以使用ALTERTABLE添加或刪除主鍵約束，向已有表中添加主鍵約束時，表中的主鍵值不能有重復(fù)的值，表中的主鍵字段不能有NULL，刪除約束時必須要指定要刪除的約束名稱，語法格式如下：ALTERTABLEtable_nameADD[CONSTRAINTconstraint_name]PRIMARYKEY（colum1,colum2,…）[DROPCONSTRAINTconstraint_name]例：為SC表添加主鍵約束（Sno,Cno）。ALTERTABLESCADDPRIMARYKEY(Sno,Cno);5.2約束類型指定約束名稱PK_SnoCno。ALTERTABLESCADDCONSTRAINTPK_SnoCnoPRIMARYKEY(Sno,Cno);例：刪除SC表的主鍵約束PK_SnoCnoALTERTABLESCDROPCONSTRAINTPK_SnoCno;二、非空約束非空約束指定某字段不能取空值（NULL），它只能在列級定義。在默認(rèn)情況下，Oracle允許字段的內(nèi)容為空值，定義非空約束的子句為NOTNULL。1.創(chuàng)建表時定義創(chuàng)建表時定義非空約束的語法格式如下：CREATETABLE[schema.]table_name(columndatatype[DEFAULTexpr][[CONSTRAINTconstraint_name]NOTNULL],...);5.2約束類型例：創(chuàng)建課程表(course)，包括課程號(Cno)、課程名(Cname)、學(xué)分(Ccredit)，要求課程名不能為空。CREATETABLECOURSE(Cnochar(3)PRIMARYKEY,Cnamevarchar2(20)NOTNULL,Ccreditnumber(3));2.使用ALTERTABLE創(chuàng)建表之后可以使用ALTERTABLE添加或刪除非空約束，向已有表中添加非空約束時,相應(yīng)的字段不能有NULL。5.2約束類型語法格式如下：ALTERTABLEtable_nameMODIFYcloumn[CONSTRAINTconstraint_name]NOTNULL;[DROPCONSTRAINTconstraint_name]例：給STUDENT表的Sname字段添加非空約束，約束名為NOTNULL_SnameALTERTABLESTUDENTMODIFYSnameCONSTRAINTNOTNULL_SnameNOTNULL;例：刪除STUDENT表Sname的非空約束。ALTERTABLESTUDENTDROPCONSTRAINTNOTNULL_Sname;5.2約束類型三、唯一約束惟一約束條件要求表的一個字段或多字段的組合內(nèi)容必須是惟一的，即不能有重復(fù)的值。指定了UNIQUE的字段如果沒有指定NOTNULL約束時允許輸入NULL值，并且可多次輸入NULL值，Oracle認(rèn)為（NULL）不等于任何值。UNIQUE約束可以定義在列級，也可定義在表級。但如果惟一約束包含表的多個字段，則必須在表級定義。1.創(chuàng)建表時定義語法格式如下：CREATETABLE[schema.]table_name(columndatatype[DEFAULTexpr][[CONSTRAINTconstraint_name]UNIQUE],--列級約束

...[[CONSTRAINTconstraint_name]UNIQUE(column,…)]);--表級約束5.2約束類型例：創(chuàng)建課程表(course)，包括課程號(Cno)、課程名(Cname)、學(xué)分(Ccredit)，要求課程名取值唯一。CREATETABLECOURSE(Cnochar(3)PRIMARYKEY,Cnamevarchar2(20)UNIQUE,Ccreditnumber(3));2.使用ALTERTABLE創(chuàng)建表之后可以使用ALTERTABLE添加或刪除唯一約束,向已有表中添加唯一約束時相應(yīng)的字段不能有重復(fù)的值。5.2約束類型語法格式如下。ALTERTABLEtable_nameADD[CONSTRAINTconstraint_name]UNIQUE(column,…);[DROPCONSTRAINTconstraint_name]例：給STUDENT表的Sname字段添加唯一約束，約束名為UNIQUE_SnameALTERTABLESTUDENTADDCONSTRAINTUNIQUE_SnameUNIQUE(Sname);例：刪除STUDENT表Sname的唯一約束。ALTERTABLESTUDENTDROPCONSTRAINTUNIQUE_Sname;5.2約束類型四、檢查約束檢查約束是用來定義表上的每一行必須滿足約束條件，一個字段上的CHECK約束可以定義多個。CHECK約束不能定義在偽列上，可以調(diào)用SYSDATE、USER等系統(tǒng)函數(shù)。一個CHECK約束可以包含一個字段或多字段。CHECK約束可以定義在列級，也可以定義在表級，如果CHECK約束包含表的多個字段，則必須在表級定義。1.創(chuàng)建表時定義語法格式如下：CREATETABLE[schema.]table_name(columndatatype[DEFAULTexpr][[CONSTRAINTconstraint_name]CHECK(condition)],--列級約束

...[[CONSTRAINTconstraint_name]CHECK(condition)]);--表級約束5.2約束類型例：創(chuàng)建學(xué)生表student，包括學(xué)號(Sno)、姓名(Sname)、Sage（年齡）、性別(Ssex)、出生日期(Birthday)、系院(Sdept)等學(xué)生信息。其中學(xué)號(Sno)為主鍵，性別只能輸入‘男’或‘女’。CREATETABLESTUDENT(Snochar(6)PRIMARYKEY,Snamevarchar2(8),Sagenumber(2,0),Ssexchar(2)CONSTRAINTCH_SsexCHECK(Ssex='男'orSsex='女'),Birthdaydate,Sdeptchar(20));5.2約束類型2.使用ALTERTABLE創(chuàng)建表之后可以使用ALTERTABLE添加或刪除檢查約束，向已有表中添加檢查約束時現(xiàn)有表中的數(shù)據(jù)必須滿足定義的條件，否則禁止添加，語法格式如下：ALTERTABLEtable_nameADD[CONSTRAINTconstraint_name]CHECK(condition);[DROPCONSTRAINTconstraint_name]例：為SC表中的Grade添加一個檢查約束CH_Grade，限制Grade的取值范圍為0-100。ALTERTABLESCADDCONSTRAINTCH_GradeCHECK(GradeBETWEEN0AND100);例:刪除SC表的檢查約束CH_Grade。ALTERTABLESCDROPCONSTRAINTCH_Grade;5.2約束類型五、外鍵約束定義外鍵約束（FOREIGNKEY）必須用REFERENCES指定所參照的表及字段，REFERENCES所參照的表為父表或主表，外鍵所在的表為子表或從表。外鍵約束可以定義在列級，也可以定義在表級，定義外鍵約束的語法格式如下：語法格式如下：CREATETABLE[schema.]table_name(columndatatype[DEFAULTexpr][[CONSTRAINTconstraint_name]REFERENCEStable_name(column)[ONDELETECASCADE|ONDELETESETNULL]],--列級約束

...[[CONSTRAINTconstraint_name]FOREIGNKEY(column1，column2，…)REFERENCEStable_name(column1，column2，…)[ONDELETECASCADE|ONDELETESETNULL]]);--表級約束5.2約束類型ALTERTABLE的語法格式如下：ALTERTABLEtable_nameADD[CONSTRAINTconstraint_name]FOREIGNKEY(column1，column2，…)REFERENCEStable_name(column1，column2，…)[ONDELETECASCADE|ONDELETESETNULL];[DROPCONSTRAINTconstraint_name]其中ONDELETECASCADE、ONDELETESETNULL為外鍵規(guī)則子句，可以缺省，缺省時,也就是Oracle的基本的參照規(guī)則：禁止改變從表中的外鍵值(此值在主表中主鍵中不存在)；禁止修改在從表中有對應(yīng)記錄的主表記錄的主鍵值；禁止刪除在從表中有對應(yīng)記錄的主表記錄。ONDELETECASCADE：如果字表中子記錄存在，則刪除主表中的主記錄時，級聯(lián)刪除子記錄。ONDELETESETNULL：如果字表中子記錄存在，則刪除主表中的主記錄時，將子記錄（外鍵值）置成空。5.2約束類型1.創(chuàng)建表時定義例：創(chuàng)建一選課表SC,包括學(xué)號（Sno），課程號（Cno），成績（Grade），主鍵為（Sno,Cno），同時指定其外鍵為Sno，參照Student表中的Sno字段。

CREATETABLESC(Snovarchar(12)REFERENCESStudent(Sno),Cnovarchar(3),Gradenumber(3,0),CONSTRAINTPK_SCPRIMARY(Sno,Cno));5.2約束類型2.使用ALTERTABLE例：為EMP表中的EMPNO字段添加外鍵約束，參照DEPT表中的DEPTNO，約束名FK_DEP_DEPTNO。ALTERTABLEEMPADDCONSTRAINTFK_DEP_DEPTNOREFERENCESDEPT(DEPTNO);例：為EMP表中的EMPNO字段添加外鍵約束，參照DEPT表中的DEPTNO，約束名FK_DEP_DEPTNO_DELETE，參照規(guī)則為ONDELETECASCADE。ALTERTABLEEMPADDCONSTRAINTFK_DEP_DEPTNO_DELETEREFERENCESDEPT(DEPTNO)ONDELETECASCADE;5.2約束類型例：為EMP表中的EMPNO字段添加外鍵約束，參照DEPT表中的DEPTNO，約束名FK_DEP_DEPTNO_SET_NULL，參照規(guī)則為ONSETNULL。ALTERTABLEEMPADDCONSTRAINTFK_DEP_DEPTNO__SET_NULLREFERENCESDEPT(DEPTNO)ONSETNULL;例：刪除EMP表的外鍵約束FK_DEP_DEPTNO。ALTERTABLEEMPDROPCONSTRAINTFK_DEP_DEPTNO;5.2約束類型六、查看約束1.查看某表的所有約束其語法格式如下：SELECTCONSTRAINT_NAME,CONSTRAINT_TYPE,STATUSFROMUSER_CONSTRAINTSWHERETABLE_NAME=‘用戶表’;例：查看EMP表的所所有約束SELECTCONSTRAINT_NAME,CONSTRAINT_TYPE,STATUSFROMUSER_CONSTRAINTSWHERETABLE_NAME='EMP';注意此處的EMP一定要用打?qū)懽帜浮?.2約束類型2.查看某表的所有字段對應(yīng)的約束其語法格式如下：SELECTCONSTRAINT_NAME,COLUMN_NAMEFROMUSER_CONS_COLUMNS