第13章數(shù)據(jù)完整性(新)

第13章數(shù)據(jù)完整性13.1數(shù)據(jù)完整性概念13.2數(shù)據(jù)約束13.3缺省與規(guī)則13.4約束與缺省、規(guī)則比較13.1數(shù)據(jù)完整性概念什么是數(shù)據(jù)庫的完整性數(shù)據(jù)庫的完整性是指數(shù)據(jù)的正確性和相容性正確性：數(shù)據(jù)是符合現(xiàn)實世界語義、反映當(dāng)前實際狀況的。相容性：數(shù)據(jù)庫同一對象在不同關(guān)系表中的數(shù)據(jù)是符合邏輯的。數(shù)據(jù)庫完整性是防止不合語義或不正確的數(shù)據(jù)進(jìn)入數(shù)據(jù)庫，體現(xiàn)了是否真實地反映現(xiàn)實世界

1.提供定義完整性約束條件的機(jī)制DBMS應(yīng)提供定義數(shù)據(jù)庫完整性約束條件，并把它們存入數(shù)據(jù)庫中。2.提供完整性檢查的方法檢查數(shù)據(jù)是否滿足完整性約束條件的機(jī)制稱為完整性檢查。一般在INSERT、UPDATE、DELETE語句執(zhí)行后開始檢查。3.違約處理DBMS若發(fā)現(xiàn)用戶的操作違背了完整性約束條件，就采取一定的動作以保證數(shù)據(jù)的完整性，如拒絕執(zhí)行該操作，或級聯(lián)執(zhí)行其他操作。DBMS維護(hù)數(shù)據(jù)庫完整性的機(jī)制：數(shù)據(jù)的完整性和安全性數(shù)據(jù)的完整性和安全性是兩個不同概念數(shù)據(jù)的完整性防止數(shù)據(jù)庫中存在不符合語義的數(shù)據(jù)，也就是防止數(shù)據(jù)庫中存在不正確的數(shù)據(jù)防范對象：不合語義的、不正確的數(shù)據(jù)數(shù)據(jù)的安全性保護(hù)數(shù)據(jù)庫防止惡意的破壞和非法的存取防范對象：非法用戶和非法操作完整性類型13.1.1實體完整性13.1.2參照完整性13.1.3用戶定義完整性SQL完整性組件域完整性：域完整性指列的值域的完整性。如數(shù)據(jù)類型、格式、值域范圍、是否允許空值等。13.1.1實體完整性規(guī)則檢查和違約處理SQLServer實現(xiàn)實體完整性的組件實體完整性規(guī)則規(guī)則1實體完整性規(guī)則（EntityIntegrity）若屬性A是基本關(guān)系R的主屬性，則屬性A不能取空值。例1：單列SAP(SUPERVISOR,SPECIALITY,POSTGRADUATE)POSTGRADUATE：主碼（假設(shè)研究生不會重名）不能取空值例2：多列SC（S#，C#，GRADE）屬性S#和C#都不能取空。實體完整性說明(1)實體完整性規(guī)則是針對基本關(guān)系而言的。一個基本表通常對應(yīng)現(xiàn)實世界的一個實體集。(2)現(xiàn)實世界中的實體是可區(qū)分的，即它們具有某種唯一性標(biāo)識。(3)關(guān)系模型中以主碼作為唯一性標(biāo)識。(4)主碼中的屬性即主屬性不能取空值。主屬性取空值，就說明存在某個不可標(biāo)識的實體，即存在不可區(qū)分的實體，這與第（2）點相矛盾，因此這個規(guī)則稱為實體完整性實體完整性檢查和違約處理插入或?qū)χ鞔a列進(jìn)行更新操作時，DBMS按照實體完整性規(guī)則自動進(jìn)行檢查。包括：1.檢查主碼值是否唯一，如果不唯一則拒絕插入或修改2.檢查主碼的各個屬性是否為空，只要有一個為空就拒絕插入或修改實體完整性檢查和違約處理(續(xù))檢查記錄中主碼值是否唯一的一種方法是進(jìn)行全表掃描實體完整性檢查和違約處理(續(xù))在索引中檢查是否唯一。

SQL完整性組件SQLServer實現(xiàn)實體完整性的組件索引：聚集索引Unique約束：定義非空的唯一約束Primary約束：主鍵約束Identity屬性：自動增加Identity屬性語法IDENTITY[(seed,increment)]seed：加載到表中的第一個行所使用的值。increment：與前一個加載的行的標(biāo)識值相加的增量值。必須同時指定種子和增量，或者二者都不指定。如果二者都未指定，則取默認(rèn)值(1,1)舉例CREATETABLEnew_employees(id_numintIDENTITY(1,1),fnamevarchar(20),minitchar(1),lnamevarchar(30));13.1.2參照完整性關(guān)系間的引用外碼參照完整性規(guī)則1.關(guān)系間的引用在關(guān)系模型中實體及實體間的聯(lián)系都是用關(guān)系來描述的，因此可能存在著關(guān)系與關(guān)系間的引用。例1學(xué)生實體、專業(yè)實體

學(xué)生（學(xué)號，姓名，性別，專業(yè)號，年齡）專業(yè)（專業(yè)號，專業(yè)名）主碼主碼學(xué)生關(guān)系引用了專業(yè)關(guān)系的主碼“專業(yè)號”。學(xué)生關(guān)系中的“專業(yè)號”值必須是確實存在的專業(yè)的專業(yè)號，即專業(yè)關(guān)系中有該專業(yè)的記錄。關(guān)系間的引用(續(xù))例2學(xué)生、課程、學(xué)生與課程之間的多對多聯(lián)系學(xué)生（學(xué)號，姓名，性別，專業(yè)號，年齡）課程（課程號，課程名，學(xué)分）選修（學(xué)號，課程號，成績）關(guān)系間的引用(續(xù))例3學(xué)生實體及其內(nèi)部的一對多聯(lián)系學(xué)生（學(xué)號，姓名，性別，專業(yè)號，年齡，班長）“學(xué)號”是主碼，“班長”是外碼，它引用了本關(guān)系的“學(xué)號”“班長”必須是確實存在的學(xué)生的學(xué)號2．外碼（ForeignKey）設(shè)F是基本關(guān)系R的一個或一組屬性，但不是關(guān)系R的碼。如果F與基本關(guān)系S的主碼Ks相對應(yīng)，則稱F是基本關(guān)系R的外碼基本關(guān)系R稱為參照關(guān)系（ReferencingRelation）基本關(guān)系S稱為被參照關(guān)系（ReferencedRelation）或目標(biāo)關(guān)系（TargetRelation）外碼(續(xù))［例1］：學(xué)生關(guān)系的“專業(yè)號與專業(yè)關(guān)系的主碼“專業(yè)號”相對應(yīng)“專業(yè)號”屬性是學(xué)生關(guān)系的外碼專業(yè)關(guān)系是被參照關(guān)系，學(xué)生關(guān)系為參照關(guān)系

外碼(續(xù))［例2］：選修關(guān)系的“學(xué)號”與學(xué)生關(guān)系的主碼“學(xué)號”相對應(yīng)選修關(guān)系的“課程號”與課程關(guān)系的主碼“課程號”相對應(yīng)“學(xué)號”和“課程號”是選修關(guān)系的外碼學(xué)生關(guān)系和課程關(guān)系均為被參照關(guān)系選修關(guān)系為參照關(guān)系外碼(續(xù))［例3］：“班長”與本身的主碼“學(xué)號”相對應(yīng)“班長”是外碼學(xué)生關(guān)系既是參照關(guān)系也是被參照關(guān)系

外碼(續(xù))關(guān)系R和S不一定是不同的關(guān)系目標(biāo)關(guān)系S的主碼Ks和參照關(guān)系的外碼F必須定義在同一個（或一組）域上外碼并不一定要與相應(yīng)的主碼同名當(dāng)外碼與相應(yīng)的主碼屬于不同關(guān)系時，往往取相同的名字，以便于識別3.參照完整性規(guī)則規(guī)則2.2參照完整性規(guī)則若屬性（或?qū)傩越M）F是基本關(guān)系R的外碼它與基本關(guān)系S的主碼Ks相對應(yīng)（基本關(guān)系R和S不一定是不同的關(guān)系），則對于R中每個元組在F上的值必須為：或者取空值（F的每個屬性值均為空值）或者等于S中某個元組的主碼值

這條規(guī)則的實質(zhì)是什么？“不允許引用不存在的實體”。參照完整性規(guī)則(續(xù))[例1]：學(xué)生關(guān)系中每個元組的“專業(yè)號”屬性只取兩類值：（1）空值，表示尚未給該學(xué)生分配專業(yè)（2）非空值，這時該值必須是專業(yè)關(guān)系中某個元組的“專業(yè)號”值，表示該學(xué)生不可能分配一個不存在的專業(yè)參照完整性規(guī)則(續(xù))〔例2〕：選修（學(xué)號，課程號，成績）“學(xué)號”和“課程號”可能的取值：（1）選修關(guān)系中的主屬性，不能取空值（2）只能取相應(yīng)被參照關(guān)系中已經(jīng)存在的主碼值參照完整性規(guī)則(續(xù))例3〕：學(xué)生（學(xué)號，姓名，性別，專業(yè)號，年齡，班長）“班長”屬性值可以取兩類值：（1）空值，表示該學(xué)生所在班級尚未選出班長（2）非空值，該值必須是本關(guān)系中某個元組的學(xué)號值13.1.3用戶定義的完整性針對某一具體關(guān)系數(shù)據(jù)庫的約束條件，反映某一具體應(yīng)用所涉及的數(shù)據(jù)必須滿足的語義要求關(guān)系模型應(yīng)提供定義和檢驗這類完整性的機(jī)制，以便用統(tǒng)一的系統(tǒng)的方法處理它們，而不要由應(yīng)用程序承擔(dān)這一功能13.2數(shù)據(jù)約束13.2.1列級約束與表級約束13.2.2主鍵約束13.2.3唯一約束13.2.4外鍵約束13.2.5缺省約束13.2.6CHECK約束13.2.7啟用和禁用約束使用sys.key_constraints目錄視圖

查看數(shù)據(jù)庫約束Friday,February3,2023第30頁13.2.1列級約束與表級約束列約束被指定為列定義的一部分，并且僅適用于指定的單個列。表約束的聲明與列的定義無關(guān)，可以適用于表中一個以上的列。當(dāng)一個約束中必須包含一個以上的列時，必須使用表約束。例如，如果一個表的主鍵內(nèi)有兩個或兩個以上的列，則必須使用表約束將這兩列加入主鍵內(nèi)。列級約束與表級約束舉例CREATETABLEtable7(c1int, c2int, c3char(5), c4char(10), CONSTRAINTc1PRIMARYKEY(c1,c2))CREATETABLEdepartment/*部門表*/( dnointPRIMARYKEY,/*部門號,為主鍵*/ dnamechar(20), /*部門名*/)表級約束列級約束13.2.2主鍵約束作為表定義的一部分在創(chuàng)建表時創(chuàng)建。添加到還沒有PRIMARYKEY約束的表中（一個表只能有一個PRIMARYKEY約束）。如果已有PRIMARYKEY約束，則可對其進(jìn)行修改或刪除。例如，可以使表的PRIMARYKEY約束引用其他列，更改列的順序、索引名、聚集選項或PRIMARYKEY約束的填充因子。定義了PRIMARYKEY約束的列的列寬不能更改。13.2.3唯一約束UNIQUEUNIQUE約束在列集內(nèi)強(qiáng)制執(zhí)行值的唯一性。對于UNIQUE約束中的列，表中不允許有兩行包含相同的非空值。主鍵也強(qiáng)制執(zhí)行唯一性，但主鍵不允許空值，而且每個表中主鍵只能有一個，但是UNIQUE列卻可以有多個。UNIQUE約束優(yōu)先于唯一索引。使用ALTERTABLE語句定義

UNIQUE約束在students表中，主鍵約束創(chuàng)建在studentID列上，如果這時還需要保證該表中的存儲身份證號的SSL列的數(shù)據(jù)是唯一的，可以使用UNIQUE約束。一般地，把UNIQUE約束稱為候選的主鍵約束。Friday,February3,2023第35頁13.2.4外鍵約束FOREIGNKEY約束稱為外鍵約束，用于標(biāo)識表之間的關(guān)系，以強(qiáng)制參照完整性，即為表中一列或者多列數(shù)據(jù)提供參照完整性。一個表中最多可以有253個可以參照的表，因此每個表最多可以有253個FOREIGNKEY約束。在FOREIGNKEY約束中，只能參照同一個數(shù)據(jù)庫中的表，而不能參照其他數(shù)據(jù)庫中的表。FOREIGNKEY子句中的列數(shù)目和每個列指定的數(shù)據(jù)類型必須和REFERENCE子句中的列相同。FOREIGNKEY約束不能自動創(chuàng)建索引。參照同一個表中的列時，必須只使用REFERENCE子句，而不能使用FOREIGNKEY子句。在臨時表中，不能使用FOREIGNKEY約束。外鍵約束的基本語法形式在CREATETABLE語句中定義列的外鍵約束FOREIGNKEYREFERENCES外表名（參照列）ONDELETE{NOACTION|CASCADE|SETNULL|SETDEFAULT}ONUPDATE{NOACTION|CASCADE|SETNULL|SETDEFAULT}在ALTERTABLE語句中定義列的外鍵約束FOREIGNKEY(COLUMN)REFERENCES外表名（參照列）ONDELETE{NOACTION|CASCADE|SETNULL|SETDEFAULT}ONUPDATE{NOACTION|CASCADE|SETNULL|SETDEFAULT}在CREATETABLE語句中定義多列的外鍵約束FOREIGNKEY(COLUMN_list)REFERENCES外表名（參照列名表）ONDELETE{NOACTION|CASCADE|SETNULL|SETDEFAULT}ONUPDATE{NOACTION|CASCADE|SETNULL|SETDEFAULT}

USEtestGOCREATETABLEworker /*職工表*/( nointPRIMARYKEY, /*編號,為主鍵*/ namechar(8), /*姓名*/ sexchar(2), /*性別*/ dnoint /*部門號*/ FOREIGNKEYREFERENCESdepartment(dno) ONDELETENOACTION,/*刪除主鍵數(shù)據(jù)時外鍵表的行為*/ addresschar(30) /*地址*/)GO

該程序使用FOREIGNKEY子句在worker表中建立了一個刪除約束，即worker表的dno列（是一個外鍵）與department表的dno列關(guān)聯(lián)。如果外鍵表有數(shù)據(jù)，則刪除失敗，NOACTION是默認(rèn)選項【例】在表定義時創(chuàng)建外鍵在ALTERTABLE語句中定義外鍵約束Friday,February3,2023第39頁13.2.5缺省約束DEFAULT缺省約束是加在列上的缺省值。缺省值是指當(dāng)對列數(shù)據(jù)進(jìn)行INSERT操作時系統(tǒng)自動提供的值。列的缺省約束值可以是常數(shù)或系統(tǒng)函數(shù)列的缺省約束值也可以是事先定義的缺省，在創(chuàng)建數(shù)據(jù)表的時候定義缺省約束時有兩種方法：用界面操作方便用命令操作需要兩步：第一步定義表結(jié)構(gòu)，第二步綁定缺省列的缺省約束值是常數(shù)的情況CREATETABLEjsy_temp4(駕照號 int IDENTITY(1,1)NOTNULL PRIMARYKEYCLUSTERED, 姓名varchar(8) NOTNULL, 是否見習(xí)varchar(20)DEFAULT(‘是’), 身份證號char(18) NOTNULL UNIQUENONCLUSTERED)使用ALTERTABLE語句定義DEFAULT約束Friday,February3,2023第42頁13.2.6CHECK約束CHECK約束通過限制用戶輸入的值來加強(qiáng)域完整性。它指定應(yīng)用于列中輸入的所有值的布爾（取值為TRUE或FALSE）搜索條件，拒絕所有不取值為TRUE的值?？梢詾槊苛兄付ǘ鄠€CHECK約束。以下程序在test數(shù)據(jù)庫中創(chuàng)建一個table6表，其中使用CHECK約束來限定f2列只能為0~100分：

USEtestGOCREATETABLEtable6( f1int, f2intNOTNULLCHECK(f2>=0ANDf2<=100))GO當(dāng)執(zhí)行如下語句：

INSERTtable6VALUES(1,120)則會出現(xiàn)如圖13.2所示的錯誤消息。例:CHECK約束的使用方法13.2.7啟用和禁用約束（1）當(dāng)建立主鍵約束或惟一約束時，SQLServer會在后臺自動建立惟一索引，并對表中的數(shù)據(jù)進(jìn)行檢查，以確保數(shù)據(jù)符合約束要求，這種自查過程是無法關(guān)閉。（2）當(dāng)創(chuàng)建檢查約束和外鍵約束時，在缺省狀態(tài)下，系統(tǒng)也會自動對數(shù)據(jù)進(jìn)行檢查，與主鍵約束和惟一約束不同的是，它可以取消。當(dāng)確認(rèn)表中的數(shù)據(jù)已經(jīng)符合約束條件時，就不必再浪費時間重復(fù)檢查。如果已確認(rèn)表中的數(shù)據(jù)不符合約束條件，又不想馬上清理當(dāng)前的數(shù)據(jù)，也不必對數(shù)據(jù)進(jìn)行檢查，此時可以使用WITHNOCHECK選項關(guān)閉檢查當(dāng)前數(shù)據(jù)。（3）當(dāng)為某列加入一個新的缺省約束，SQLServer并不對該列已有數(shù)據(jù)進(jìn)行檢查，表中已有記錄保留原有的取值或NULL。當(dāng)新建一列同時指定其缺省值時，SQLServer才為已有記錄填充數(shù)值。如果使用了WITHVALUES選項，SQLServer會把缺省值賦給新列，否則新列為NULL。如果新列不允許為NULL，則無論是否指定了WITHVALUES選項，都使用缺省值。（4）當(dāng)向數(shù)據(jù)庫添加大量的數(shù)據(jù)時，可以臨時禁止檢查約束和外鍵約束。使用WITHNOCHECK子句該約束對表中已有數(shù)據(jù)不強(qiáng)制檢查。但是，如果以后更新表中數(shù)據(jù)要檢查更新后的數(shù)據(jù)是否滿足約束。第46頁13.3缺省與規(guī)則SQLServer中缺省（默認(rèn)值）和規(guī)則的概念缺?。J(rèn)值）是為列提供數(shù)據(jù)的一種方式，如果用戶進(jìn)行INSERT操作時不為列輸入數(shù)據(jù)，則使用缺省值。規(guī)則是當(dāng)用戶進(jìn)行INSERT或UPDATE操作時，對輸入列中的數(shù)據(jù)設(shè)定的取值范圍。缺省與規(guī)則不是ANSI標(biāo)準(zhǔn)，一般不提倡使用，應(yīng)盡可能使用約束，任何可以使用缺省與規(guī)則的地方都有可以使用約束。13.3缺省與規(guī)則特點(1)缺省與規(guī)則是數(shù)據(jù)庫對象，它們是獨立于表和列而建立的。(2)缺省與規(guī)則建立后與列或數(shù)據(jù)類型產(chǎn)生關(guān)聯(lián)，列和數(shù)據(jù)類型就具有了缺省與規(guī)則的屬性。(3)缺省與規(guī)則定義后，可以重復(fù)使用，可以綁定到多個列或數(shù)據(jù)類型上。(4)缺省與規(guī)則不隨表同時調(diào)入內(nèi)存，當(dāng)用到時才被調(diào)入內(nèi)存，這可能會使程序執(zhí)行出現(xiàn)延時。13.3缺省與規(guī)則13.3.1創(chuàng)建缺省和規(guī)則13.3.2綁定13.3.3取消綁定13.3.4刪除缺省和規(guī)則13.3.1創(chuàng)建缺省和規(guī)則CREATEDEFAULTdefault_nameASconstant_expression其中default_name為缺省值對象名，constant_expression為一個常量、數(shù)學(xué)表達(dá)式或內(nèi)置函數(shù)，不能引用其列或別的數(shù)據(jù)庫對象。創(chuàng)建缺省舉例/*使用下面語句創(chuàng)建con3默認(rèn)對象,其默認(rèn)值為10*/createdefaultcon3as10go13.3.1創(chuàng)建缺省和規(guī)則CREATERULErule_nameASrule_conditions其中rule_name為規(guī)則對象名，rule_conditions為規(guī)則表達(dá)式，表示數(shù)據(jù)需滿足的條件。例：創(chuàng)建一個名為rule1的規(guī)則，限定輸入的值必須在0~10之間。

USEtestGOCREATERULErule1AS@c1BETWEEN0and10GO13.3.2綁定綁定到某列或用戶自定義的數(shù)據(jù)類型上，以便它們在數(shù)據(jù)輸入或修改操作中起作用。使用時要注意缺省和規(guī)則所用的數(shù)據(jù)類型必須與將要綁定的列數(shù)據(jù)類型一致，還要遵循已應(yīng)用到該列中的約束。缺省和默認(rèn)值綁定格式SP_BINDEFAULT‘default_name’,’object_name’[,FUTUREONLY]SP_BINDRULE‘rule_name’,‘object_name’,FUTUREONLY]1）default_name