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

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

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

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

…

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

…）或者：CREATETABLE表名（

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

…

身份證號(hào)CHAR(19)UNIQUE,

…

）或：CREATETABLE職工表（

…

身份證號(hào)CHAR(19),

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

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

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

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

--作為表級(jí)約束定義

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

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

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

男21計(jì)算機(jī)系0811102劉晨

男20計(jì)算機(jī)系0811103王敏

女20計(jì)算機(jī)系0811104張小紅

女19計(jì)算機(jī)系0821101張立

男20信息管理系0821102吳賓

女19信息管理系0821103張海

男20信息管理系SnoCnoGrade0811101C001960811101C002800811101C003840811101C005620811102C001920811102C002900811102C004840821102C001760821102C004850821102C005730821102C007NULL0821103C001500821103C004800811102劉晨

男20計(jì)算機(jī)系0811102C001920811102C002900811102C00484ONDELETECASCADE[ONDELETENOACTION]√x0811102劉晨

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

男21計(jì)算機(jī)系0811102劉晨

男20計(jì)算機(jī)系0811103王敏

女20計(jì)算機(jī)系0811104張小紅

女19計(jì)算機(jī)系0821101張立

男20信息管理系0821102吳賓

女19信息管理系0821103張海

男20信息管理系SnoCnoGrade0811101C001960811101C002800811101C003840811101C005620811102C001920811102C002900811102C004840821102C001760821102C004850821102C005730821102C007NULL0821103C001500821103C004800811102劉晨

男20計(jì)算機(jī)系0811102C001920811102C002900811102C00484ONUPDATECASCADE[ONUPDATENOACTION]√x0811110劉晨

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

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

…

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

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

電話CHAR(8)DEFAULT'11111111',

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

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

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

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

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