第章數(shù)據(jù)庫數(shù)據(jù)完整性約束課件

第8章數(shù)據(jù)完整性約束8.1數(shù)據(jù)完整性的概念8.2實現(xiàn)數(shù)據(jù)完整性8.3系統(tǒng)對完整性約束的檢查8.4刪除約束8.5觸發(fā)器*1第8章數(shù)據(jù)完整性約束8.1數(shù)據(jù)完整性的概念*18.1數(shù)據(jù)完整性的概念數(shù)據(jù)完整性是指數(shù)據(jù)的正確性和相容性。如：每個人的身份證號必須是唯一的，人的性別只能是‘男’或‘女’。為了維護(hù)數(shù)據(jù)的完整性，DBMS必須要提供一種機制來檢查數(shù)據(jù)庫中的數(shù)據(jù)。這些加在數(shù)據(jù)庫數(shù)據(jù)之上的語義約束條件就稱為數(shù)據(jù)完整性約束條件。DBMS中檢查數(shù)據(jù)是否滿足完整性條件的機制就稱為完整性檢查。*28.1數(shù)據(jù)完整性的概念數(shù)據(jù)完整性是指數(shù)據(jù)的正確性和相容性。完整性約束條件的作用對象列級約束元組約束關(guān)系約束*3完整性約束條件的作用對象列級約束*3列級約束主要是對列的類型、取值范圍、精度等的約束，包括：對數(shù)據(jù)類型的約束：數(shù)據(jù)類型、長度、精度等例：snochar(8)對數(shù)據(jù)格式的約束：Sno:201001101

年專業(yè)班號序列號對取值范圍的約束：如學(xué)生的成績?nèi)≈捣秶鸀?～100。對空值的約束：列是否允許有空值。*4列級約束主要是對列的類型、取值范圍、精度等的約束，包括：*4元組約束元組約束是元組中各個字段之間的聯(lián)系的約束，如：開始日期小于結(jié)束日期訂貨數(shù)量小于等于庫存數(shù)量最低工資不能低于規(guī)定的最低值*5元組約束元組約束是元組中各個字段之間的聯(lián)系的約束，如：*5關(guān)系約束是指若干元組之間、關(guān)系之間的聯(lián)系的約束。比如：學(xué)號的取值不能重復(fù)也不能取空值學(xué)生修課表中學(xué)號的取值受學(xué)生表中學(xué)號取值的限制等。*6關(guān)系約束是指若干元組之間、關(guān)系之間的聯(lián)系的約束。比如：*6實現(xiàn)數(shù)據(jù)完整性的方法可以在服務(wù)器端完成，也可以在客戶端編程實現(xiàn)。在服務(wù)器端實現(xiàn)數(shù)據(jù)完整性的方法主要有兩種：聲明數(shù)據(jù)完整性觸發(fā)器在客戶端實現(xiàn)數(shù)據(jù)完整性主要是用前端開發(fā)工具，在應(yīng)用程序中編寫代碼保證。*7實現(xiàn)數(shù)據(jù)完整性的方法可以在服務(wù)器端完成，也可以在客戶端編程實8.2實現(xiàn)數(shù)據(jù)完整性設(shè)有如下兩張表：CREATETABLE職工表(職工編號CHAR(7)NOTNULL,職工名CHAR(10)NOTNULL,工作編號CHAR(8),工資SMALLINT,電話CHAR(8),身份證號CHAR(18))

*8CREATETABLE工作表(工作編號CHAR(8)NOTNULL,最低工資SMALLINT,最高工資SMALLINT)8.2實現(xiàn)數(shù)據(jù)完整性設(shè)有如下兩張表：*8CREATETA1.實體完整性約束實體完整性是用PRIMARYKEY來保證。注意：每個表只能有一個PRIMARYKEY約束；用PRIMARYKEY約束的列的取值必須是不重復(fù)的（對由多列構(gòu)成的主鍵，是這些主鍵列組合起來取值不重），并且不允許有空值。*91.實體完整性約束實體完整性是用PRIMARYKEY來保證添加主鍵約束ALTERTABLE表名ADD[CONSTRAINT約束名]PRIMARYKEY（<列名>[,…n]）*10添加主鍵約束ALTERTABLE表名*10示例ALTERTABLE職工表ADDCONSTRAINTPK_EMPPRIMARYKEY（職工編號）ALTERTABLE工作表ADDCONSTRAINTPK_JOBPRIMARYKEY（工作編號）*11示例ALTERTABLE職工表*112.唯一值約束用UNIQUE約束實現(xiàn)，用于限制一個列或者是多個列的組合取值不重復(fù)。用在事實上具有唯一性的屬性列上，比如身份證號碼、駕駛證號碼等。注意：有UNIQUE約束的列允許有一個空值；在一個表中可以定義多個UNIQUE約束；可以在一個列或多個列上定義UNIQUE約束；*122.唯一值約束用UNIQUE約束實現(xiàn)，用于限制一個列或者是在創(chuàng)建表時定義UNIQUE約束CREATETABLE表名（

…

列名類型[CONSTRAINT約束名]UNIQUE（<列名>[,…n]）,

…）或者：CREATETABLE表名（

列名類型,[CONSTRAINT約束名]UNIQUE（<列名>[,…n]））*13在創(chuàng)建表時定義UNIQUE約束CREATETABLE表名添加UNIQUE約束ALTERTABLE表名ADD[CONSTRAINT約束名]UNIQUE（<列名>[,…n]）*14添加UNIQUE約束ALTERTABLE表名*14示例——創(chuàng)建表時定義例2.為“身份證號”列添加唯一值約束。CREATETABLE職工表（

…

身份證號CHAR(19)UNIQUE,

…

）或：CREATETABLE職工表（

…

身份證號CHAR(19),

…UNIQUE(身份證號),…）*15示例——創(chuàng)建表時定義例2.為“身份證號”列添加唯一值約束。示例——添加約束ALTERTABLE職工表ADDCONSTRAINTUN_EMPUNIQUE（身份證號）*16示例——添加約束ALTERTABLE職工表*16復(fù)合唯一約束示例例3.設(shè)authors表，其中有au_fname和au_lname兩個列，現(xiàn)要限制這兩個列組合起來不重復(fù)。CREATETABLEauthors(

…au_fnameVARCHAR(20),au_lnameVARCHAR(20)UNIQUE(au_fname,au_lname),--作為列級約束定義

…)*17復(fù)合唯一約束示例例3.設(shè)authors表，其中有au_fna復(fù)合唯一約束示例CREATETABLEauthors(

…au_fnameVARCHAR(20),au_lnameVARCHAR(20),UNIQUE(au_fname,au_lname),

--作為表級約束定義

…)18*復(fù)合唯一約束示例CREATETABLEauthors(復(fù)合唯一約束示例在已創(chuàng)建好的表上添加唯一值約束：ALTERTABLEauthorsADDCONSTRAINTUN_Name

UNIQUE(au_fname,au_lname)*19復(fù)合唯一約束示例在已創(chuàng)建好的表上添加唯一值約束：*193.參照完整性參照完整性（引用完整性）用FOREIGNKEY約束保證.定義FOREIGNKEY約束時要注意：外鍵列引用的列必須是有PRIMARYKEY約束或UNIQUE約束的列，通常是有PRIMARYKEY約束的列。*203.參照完整性參照完整性（引用完整性）用FOREIGNK添加外鍵約束ALTERTABLE表名ADD[CONSTRAINT約束名]

[FOREIGNKEY]（<列名>）REFERENCES引用表名（<列名>）[ONDELETE{CASCADE|NOACTION}][ONUPDATE{CASCADE|NOACTION}]ONDELETE：級聯(lián)或限制刪除；ONUPDATE：級聯(lián)或限制更新。*21添加外鍵約束ALTERTABLE表名*21刪除示例*22SnoSnameSsexSageSdept0811101李勇

男21計算機系0811102劉晨

男20計算機系0811103王敏

女20計算機系0811104張小紅

女19計算機系0821101張立

男20信息管理系0821102吳賓

女19信息管理系0821103張海

男20信息管理系SnoCnoGrade0811101C001960811101C002800811101C003840811101C005620811102C001920811102C002900811102C004840821102C001760821102C004850821102C005730821102C007NULL0821103C001500821103C004800811102劉晨

男20計算機系0811102C001920811102C002900811102C00484ONDELETECASCADE[ONDELETENOACTION]√x0811102劉晨

男20計算機系0811102C001920811102C002900811102C00484刪除示例*22SnoSnameSsexSageSdept08更新示例*23SnoSnameSsexSageSdept0811101李勇

男21計算機系0811102劉晨

男20計算機系0811103王敏

女20計算機系0811104張小紅

女19計算機系0821101張立

男20信息管理系0821102吳賓

女19信息管理系0821103張海

男20信息管理系SnoCnoGrade0811101C001960811101C002800811101C003840811101C005620811102C001920811102C002900811102C004840821102C001760821102C004850821102C005730821102C007NULL0821103C001500821103C004800811102劉晨

男20計算機系0811102C001920811102C002900811102C00484ONUPDATECASCADE[ONUPDATENOACTION]√x0811110劉晨

男20計算機系0811110C001920811110C002900811110C00484更新示例*23SnoSnameSsexSageSdept08示例例4為職工表的“工作編號”列添加外鍵約束，此列引用工作表中的“工作編號”列。ALTERTABLE職工表ADDCONSTRAINTFK_job_id

FOREIGNKEY(工作編號)REFERENCES工作表(工作編號)*24示例例4為職工表的“工作編號”列添加外鍵約束，此列引用工4.默認(rèn)值約束用DEFAULT約束實現(xiàn)，用于提供列的默認(rèn)值。注意：只在向表中插入數(shù)據(jù)時才檢查DEFAULT約束；每個列只能有一個DEFAULT約束。*254.默認(rèn)值約束用DEFAULT約束實現(xiàn)，用于提供列的默認(rèn)值定義或添加默認(rèn)值約束CREATETABLE表名（

…

列名類型[CONSTRAINT約束名]DEFAULT常量表達(dá)式,

…）ALTERTABLE表名ADD[CONSTRAINT約束名]DEFAULT常量表達(dá)式FOR列名*26定義或添加默認(rèn)值約束CREATETABLE表名（*26示例例5在職工表中，如果某個職工沒有電話，則寫入默認(rèn)值：‘11111111’。CREATETABLE職工表( …

電話CHAR(8)DEFAULT'11111111',

…)或：ALTERTABLE職工表ADDCONSTRAINTDF_PHONEDEFAULT'11111111'FOR電話*27示例例5在職工表中，如果某個職工沒有電話，則寫入默認(rèn)值：5.列取值范圍約束用CHECK約束實現(xiàn)，用于限制列的取值在指定范圍內(nèi)，即約束列的取值符合應(yīng)用語義，如：人的性別只能是“男”或“女”，工資必須大于1000。使用CHECK約束時注意：在執(zhí)行INSERT語句和UPDATE語句時系統(tǒng)自動檢查CHECK約束；CHECK約束可以限制一個列的取值范圍，也可以限制同表多列之間的取值約束關(guān)系。*285.列取值范圍約束用CHECK約束實現(xiàn)，用于限制列的取值在定義或添加CHECK約束CREATETABLE表名(

…

列名類型[CONSTRAINT約束名

]CHECK(邏輯表達(dá)式),

…）或：ALTERTABLE表名ADD[CONSTRAINT約束名]

CHECK（邏輯表達(dá)式）*29定義或添加CHECK約束CREATETABLE表名(*2示例——定義CHECK約束例6為職工表定義工資必須大于等于1000的約束。CREATETABLE職工表（…

工資SMALLINTCHECK(工資>=1000),

…

）或者：CREATETABLE職工表（…

工資SMALLINT,

…

CHECK(工資>=1000),

…

）*30示例——定義CHECK約束例6為職工表定義工資必須大于等示例——添加CHECK約束ALTERTABLE職工表

ADDCONSTRAINTCHK_SalaryCHECK(工資>=1000)*31示例——添加CHECK約束ALTERTABLE職工表*3示例：定義多列取值約束例7限制“最低工資”<=“最高工資”。CREATETABLE工作表（…最低工資int,最高工資int,[…]CHECK（最低工資<=最高工資）,…

）注意：多列之間的CHECK約束只能定義在表級約束處。*32示例：定義多列取值約束例7限制“最低工資”<=“最高工資示例：添加多列取值約束ALTERTABLE工作表ADDCONSTRAINTCHK_Job_Salary

CHECK(最低工資<=最高工資)*33示例：添加多列取值約束ALTERTABLE工作表*33示例例8限制電話號碼列的每一位的取值必須是0~9之間的數(shù)字。

…CHECK（電話LIKE‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]’）

…*34示例例8限制電話號碼列的每一位的取值必須是0~9之間的數(shù)8.3系統(tǒng)對完整性約束的檢查主鍵約束：插入和更新數(shù)據(jù)時，檢查新數(shù)據(jù)的主鍵值是否與已存在的主鍵值重復(fù)，或者新主鍵值是否為空。唯一值約束：同主鍵約束。默認(rèn)值約束：插入數(shù)據(jù)且沒有為某個列提供值時檢查。列取值范圍約束：插入和修改有列取值約束的數(shù)據(jù)檢查。*358.3系統(tǒng)對完整性約束的檢查主鍵約束：插入和更新數(shù)據(jù)時，檢外鍵約束的檢查——對子表插入數(shù)據(jù)時，檢查新數(shù)據(jù)的外鍵值是否在主表的主鍵值范圍內(nèi)。修改外鍵列值時，檢查修改后的外鍵值是否在主表的主鍵值范圍內(nèi)。*36外鍵約束的檢查——對子表插入數(shù)據(jù)時，檢查新數(shù)據(jù)的外鍵值是否在外鍵約束的檢查——對主表刪除數(shù)據(jù)時，檢查被刪除數(shù)據(jù)的主鍵值是否在子表中有對它的引用，若無，則刪除之；若有，則看是否允許級聯(lián)刪除：若允許：則將子表中相應(yīng)數(shù)據(jù)一起刪掉；若不允許：則刪除失敗。更改主鍵列值時，檢查被更改的主鍵值是否在子表中有對它的引用，若無，則更改之；若有，則看是否允許級聯(lián)更改：若允許：則將子表中相應(yīng)數(shù)據(jù)一起進(jìn)行更改；若不允許：則更改失敗。*37外鍵約束的檢查——對主表刪除數(shù)據(jù)時，檢查被刪除數(shù)據(jù)的主鍵值是8.4刪除約束ALTERTABLE表名DROP[CONSTRAINT]約束名例9刪除在職工表上定義的限制電話號碼的CHK_PHONE約束。ALTERTABLE職工表DROPCHK_PHONE*388.4刪除約束ALTERTABLE表名*388.5觸發(fā)器是一段由對數(shù)據(jù)的更改操作引發(fā)的自動執(zhí)行的代碼。更改操作包括：UPDATE、INSERT、DELETE通常用于保證業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性主要優(yōu)點是用戶可以用編程的方法實現(xiàn)復(fù)雜的處理邏輯和商業(yè)規(guī)則，增強了數(shù)據(jù)完整性約束的功能。*398.5觸發(fā)器是一段由對數(shù)據(jù)的更改操作引發(fā)的自動執(zhí)行的代碼。創(chuàng)建觸發(fā)器CREATETRIGGER觸發(fā)器名稱ON{表名|視圖名}{FOR|AFTER|INSTEADOF}{[INSERT][,][DELETE][,][UPDATE]}AS

SQL語句*40創(chuàng)建觸發(fā)器CREATETRIGGER觸發(fā)器名稱*40注意在一個表上可以建立多個名稱不同、類型各異的觸發(fā)器，每個觸發(fā)器可由所有三個操作引發(fā)對AFTER型觸發(fā)器，可以在同一種操作上建立多個觸發(fā)器；對INSTEADOF型觸發(fā)器，在同一種操作上只能建立一個觸發(fā)器。大部分SQL語句都可用在觸發(fā)器中，但所有的創(chuàng)建和更改數(shù)據(jù)庫以及數(shù)據(jù)庫對象的語句、所有的DROP語句都不允許在觸發(fā)器中使用。*41注意在一個表上可以建立多個名稱不同、類型各異的觸發(fā)器，每個觸兩個特殊的臨時表在觸發(fā)器中可以使用兩個特殊的臨時表：INSERTEDDELETED由系統(tǒng)自動創(chuàng)建，結(jié)構(gòu)同建立觸發(fā)器的表結(jié)構(gòu)只能用在觸發(fā)器代碼中。INSERTED：保存INSERT操作中新插入的數(shù)據(jù)和UPDATE操作中更新后的數(shù)據(jù)；DELETED：保存DELETE操作刪除的數(shù)據(jù)和UPDATE操作中更新前的數(shù)據(jù)。*42兩個特殊的臨時表在觸發(fā)器中可以使用兩個特殊的臨時表：*42后觸發(fā)型觸發(fā)器當(dāng)后觸發(fā)型觸發(fā)器執(zhí)行時，引發(fā)觸發(fā)器執(zhí)行的數(shù)據(jù)操作語句已經(jīng)執(zhí)行完成*43執(zhí)行到引發(fā)觸發(fā)器執(zhí)行的操作語句執(zhí)行觸發(fā)器執(zhí)行該語句后觸發(fā)型觸發(fā)器當(dāng)后觸發(fā)型觸發(fā)器執(zhí)行時，引發(fā)觸發(fā)器執(zhí)行的數(shù)據(jù)操示例：維護(hù)不同表數(shù)據(jù)之間的取值約束例1.針對職工表和工作表，限制職工工資必須在相應(yīng)工作的最低工資到最高工資之間。CREATETri