07-第7章-數(shù)據(jù)庫(kù)完整性

第七章數(shù)據(jù)庫(kù)完整性學(xué)習(xí)要點(diǎn)數(shù)據(jù)庫(kù)完整性概述SQL2005完整性實(shí)現(xiàn)觸發(fā)器概述創(chuàng)建、修改、查看、刪除運(yùn)用說(shuō)明AFTER觸發(fā)器INSTEADOF觸發(fā)器數(shù)據(jù)庫(kù)完整性概述數(shù)據(jù)完整性（DataIntegrity）定義指數(shù)據(jù)的精確性和一樣性。目的防止數(shù)據(jù)庫(kù)中存在不符合語(yǔ)義規(guī)定的數(shù)據(jù)和防止因錯(cuò)誤信息的輸入輸出造成無(wú)效操作或錯(cuò)誤信息分類(lèi)實(shí)體完整性：用于保證數(shù)據(jù)庫(kù)中數(shù)據(jù)表的每一個(gè)特定實(shí)體的記錄都是唯一的。域完整性：用于保證指定列的數(shù)據(jù)具有正確的數(shù)據(jù)類(lèi)型、格式和有效的數(shù)據(jù)范圍。參照完整性：確保同一鍵值在全部相關(guān)聯(lián)表之間都一樣。用戶(hù)定義完整性：由用戶(hù)自定義的不屬于其他任何完整性分類(lèi)的特定業(yè)務(wù)規(guī)則。數(shù)據(jù)庫(kù)完整性概述數(shù)據(jù)完整性（DataIntegrity）為維護(hù)數(shù)據(jù)庫(kù)的完整性，DBMS必需：供應(yīng)定義完整性約束條件的機(jī)制供應(yīng)完整性檢查的方法違約處理學(xué)習(xí)要點(diǎn)完整性類(lèi)型實(shí)現(xiàn)技術(shù)實(shí)體完整性UNIQUE約束、PRIMARYKEY約束、IDENTITY屬性域完整性CHECK約束、FOREIGNKEY約束、DEFAULT定義、NOTNULL定義、默認(rèn)對(duì)象、規(guī)則對(duì)象參照完整性FOREIGNKEY約束、CHECK約束、觸發(fā)器用戶(hù)定義完整性CREATETABLE中的所有列級(jí)和表級(jí)約束、存儲(chǔ)過(guò)程、觸發(fā)器完整性實(shí)現(xiàn)SQLServer2005完整性實(shí)現(xiàn)DEFAULT約束CHECK約束PRIMARYKEY約束UNIQUE約束FOREIGNKEY約束NOTNULL約束標(biāo)識(shí)列默認(rèn)值規(guī)則學(xué)習(xí)要點(diǎn)約束DEFAULT約束定義在用戶(hù)未輸入某些列的數(shù)據(jù)時(shí)，數(shù)據(jù)庫(kù)系統(tǒng)為用戶(hù)供應(yīng)的默認(rèn)值。（默認(rèn)值必需與所約束列的數(shù)據(jù)類(lèi)型相一樣）是可以避開(kāi)不允許為空值的數(shù)據(jù)錯(cuò)誤可以加快用戶(hù)的輸入速度創(chuàng)建SSMS方式T-SQL方式約束列表運(yùn)用SSMS工具DEFAULT約束DEFAULT約束CREATETABELBookInfo(b_IDVARCHAR(16),……b_DetailVARCHAR(100)DEFAULT‘暫無(wú)描述’)運(yùn)用T-SQL定義（一）DEFAULT約束CREATETABEL數(shù)據(jù)表名(列名數(shù)據(jù)類(lèi)型[CONSTRAIN約束名]DEFAULT默認(rèn)值

[，...])列級(jí)約束示例NEXTCREATETABELBookInfo(……b_DetailVARCHAR(100)，

……CONSTRAINTde_DetailDEFAULT‘暫無(wú)描述’FORb_Detail

)運(yùn)用T-SQL定義（二）DEFAULT約束NEXTCREATETABEL數(shù)據(jù)表名(

列名1數(shù)據(jù)類(lèi)型，

……

[CONSTRAIN約束名]DEFAULT

默認(rèn)值FOR

(列名[,…n])

[,...])表級(jí)約束示例運(yùn)用T-SQL定義(三）DEFAULT約束ALTERTABEL數(shù)據(jù)表名(ADD[CONSTRAIN約束名]DEFAULT

默認(rèn)值FOR

(列名[,…n])[，...])修改表：添加約束ALTERTABELBookInfo(……ADDCONSTRAINTde_DetailDEFAULT‘暫無(wú)描述’FORb_Detail

……)示例返回約束CHECK約束定義限制用戶(hù)輸入數(shù)據(jù)的取值范圍、格式等。一個(gè)列可以定義多個(gè)CHECK約束。當(dāng)往表中插入記錄或修改相應(yīng)列的值時(shí)，系統(tǒng)檢查約束條件是否滿(mǎn)足，若不滿(mǎn)足則拒絕執(zhí)行數(shù)據(jù)操作。創(chuàng)建SSMS方式T-SQL方式約束列表運(yùn)用SSMS工具CHEECK約束CHECK約束CREATETABEL數(shù)據(jù)表名(列名數(shù)據(jù)類(lèi)型[CONSTRAIN約束名]CHECK(邏輯表達(dá)式)[，...])CREATETABELBookInfo(……b_QuantitySMALLINTCHECK(b_Quantity>=1ANDb_Quantity<=100),……)運(yùn)用T-SQL定義（一）CHECK約束列級(jí)約束示例NEXT運(yùn)用T-SQL定義（二、三）CHECK約束返回CREATETABEL數(shù)據(jù)表名(

列名1數(shù)據(jù)類(lèi)型，

……

[CONSTRAIN約束名]CHECK(邏輯表達(dá)式)

[,...])表級(jí)約束ALTERTABEL數(shù)據(jù)表名(ADD[CONSTRAIN約束名]CHECK(邏輯表達(dá)式)[，...])修改表：添加約束約束PRIMARYKEY約束定義若是單列作為主鍵，可以在列級(jí)定義或表級(jí)定義若是列集合作為主鍵，必需在表級(jí)定義一個(gè)表只能有一個(gè)PRIMARYKEY約束，受約束的列或列集合的值不能為空和重復(fù)，必需能唯一標(biāo)識(shí)表中的行。定義了PRIMARYKEY約束的列的列寬不能更改。創(chuàng)建SSMS方式T-SQL方式約束列表運(yùn)用SSMS工具返回PRIMARY約束運(yùn)用T-SQL定義（一）NEXTPRIMARY約束CREATETABEL數(shù)據(jù)表名(列名數(shù)據(jù)類(lèi)型[CONSTRAIN約束名]PRIMARYKEY[，...])CREATETABELBookInfo(b_IDVARCHAR(16)PRIMARYKEY，

……)列級(jí)約束示例默認(rèn)的約束名為：PK_BookInfo運(yùn)用T-SQL定義（二）PRIMARY約束CREATETABEL數(shù)據(jù)表名(列名1數(shù)據(jù)類(lèi)型，……[CONSTRAIN約束名]PRIMARYKEY(列名1[,…n])[,...])CREATETABELBorrowReturn(s_IDCHAR(8)

NOTNULL,r_IDCHAR(8)NOTNULL,……

PRIMARYKEY(s_ID,r_ID))表級(jí)約束示例組合鍵必需在表級(jí)定義默認(rèn)的約束名為：PK_BorrowReturnNEXT運(yùn)用T-SQL定義（三）返回PRIMARY約束ALTERTABEL數(shù)據(jù)表名(ADD[CONSTRAIN約束名]PRIMARYKEY(列名[,…])[，...])ALTERTABLEBorrowReturnADDCONSTRAINTPK_BorrowReturnPRIMARYKEY(s_ID,r_ID)示例修改表：添加約束約束UNIQUE約束定義也稱(chēng)唯一值約束，確保在非主鍵列中不輸入重復(fù)值。一個(gè)表可以定義多個(gè)UNIQUE約束。在允許空值的列上保證唯一性時(shí)，應(yīng)運(yùn)用UNIQUE約束。當(dāng)往表中插入記錄或修改相應(yīng)列的值時(shí)，系統(tǒng)檢查是否有重復(fù)值，若有則拒絕執(zhí)行數(shù)據(jù)操作。創(chuàng)建SSMS方式T-SQL方式約束列表運(yùn)用SSMS工具返回UNIQUE約束運(yùn)用T-SQL定義（一）NEXTUNIQUE約束CREATETABEL數(shù)據(jù)表名(列名數(shù)據(jù)類(lèi)型[CONSTRAIN約束名]UNIQUE[，...])CREATETABELReaderType(……rt_NameVARCHAR(10)UNIQUE,……)列級(jí)約束示例運(yùn)用T-SQL定義（二、三）UNIQUE約束返回CREATETABEL數(shù)據(jù)表名(

列名1數(shù)據(jù)類(lèi)型，

……

[CONSTRAIN約束名]UNIQUE

(列名1[,…n])

[,...])表級(jí)約束ALTERTABEL數(shù)據(jù)表名(ADD[CONSTRAIN約束名]UNIQUE(列名1[,…n])[，...])修改表：添加約束約束FOREIGNKEY約束定義也稱(chēng)為外鍵約束，用于強(qiáng)制實(shí)現(xiàn)參照完整性，建立兩個(gè)表之間的關(guān)系?？梢砸?guī)定表中的某列參照同一個(gè)表或另外一個(gè)表中已有的PRIMARYKEY約束或UNIQUE約束的列。一個(gè)表可以有多個(gè)FOREIGNKEY約束。創(chuàng)建SSMS方式T-SQL方式約束列表運(yùn)用SSMS工具Step1NEXTFOREIGNKEY約束運(yùn)用SSMS工具Step2NEXTFOREIGNKEY約束運(yùn)用SSMS工具Step3NEXTFOREIGNKEY約束運(yùn)用SSMS工具Step4返回FOREIGNKEY約束運(yùn)用T-SQL定義（一）示例CREATETABEL數(shù)據(jù)表名(列名數(shù)據(jù)類(lèi)型[CONSTRAIN約束名][FOREIGNKEY]

REFERENCES參照主鍵表[(參照列)]

[ONDELETECASCADE|ONUPDATECASCADE][，...])列級(jí)約束FOREIGNKEY約束NOACITON：默認(rèn)選項(xiàng)。將引發(fā)錯(cuò)誤，撤銷(xiāo)對(duì)父表的刪除（更新）操作

CASCADE：當(dāng)在主表刪除（更新）一行，則自動(dòng)將子表中的對(duì)應(yīng)行刪除（更新）

SETNULL：刪除（更新）父表的一行，子表相應(yīng)行的外鍵值被置為NULL

SETDEFAULT：刪除（更新）父表的一行，子表相應(yīng)行的外鍵值被置為外鍵的默認(rèn)值運(yùn)用T-SQL定義（一）FOREIGNKEY約束CREATETABELBookInfo(b_IDVARCHAR(16)PRIMARYKEY, b_NameVARCHAR(50)NOTNULL,bt_IDCHAR(10)REFERENCESBookType(bt_ID),……)示例NEXT運(yùn)用T-SQL定義（二）示例CREATETABEL數(shù)據(jù)表名(列名1數(shù)據(jù)類(lèi)型，……[CONSTRAIN約束名]FOREIGNKEY(列名)

REFERENCES

參照主鍵表[(參照列)]

[ONDELETECASCADE|ONUPDATECASCADE][,...])表級(jí)約束FOREIGNKEY約束運(yùn)用T-SQL定義（二）NEXTFOREIGNKEY約束CREATETABLEBorrowReturn(br_IDCHAR(6)PRIMARYKEY,s_IDCHAR(8)REFERENCESNOTNULL,r_IDCHAR(8)REFERENCESNOTNULL,……

FOREIGNKEY(s_ID)REFERENCES

BookStore(s_ID)

，

FOREIGNKEY(r_ID)REFERENCES

ReaderInfo(r_ID)

)示例運(yùn)用T-SQL定義（三）UNIQUE約束返回ALTERTABEL數(shù)據(jù)表名(ADD[CONSTRAIN約束名]FOREIGNKEY(列名)

REFERENCES參照主鍵表[(參照列)]

[ONDELETECASCADE|ONUPDATECASCADE][，...])修改表：添加約束ALTERTABELBookInfo(ADDFOREIGNKEY(bt_ID)REFERENCESBookType(bt_ID)

ONDELETECASCADE

ONUPDATECASCADE,)示例約束NOTNULL約束實(shí)質(zhì)表中列可以定義為允許或不允許空值。默認(rèn)狀況下，創(chuàng)建表列允許空值。一個(gè)列中出現(xiàn)NULL值，意味著用戶(hù)還沒(méi)有為該列輸入值。若設(shè)置為NOTNULL，用戶(hù)沒(méi)有輸入該列值，則引發(fā)錯(cuò)誤。示例SSMS方式T-SQL方式約束列表運(yùn)用SSMS工具返回NOTNULL約束運(yùn)用T-SQL定義NOTNULL約束CREATETABEL數(shù)據(jù)表名(列名數(shù)據(jù)類(lèi)型[CONSTRAIN約束名]NULL|NOTNULL[，…])表級(jí)約束CREATETABELBookInfo(b_IDvarchar(16)PRIMARYKEY, b_Namevarchar(50)UNIQUENOTNULL, bt_IDchar(10)REFERENCESBookType(bt_ID), b_Authorvarchar(20)NOTNULL,b_Translatorvarchar(20)NULL, …… )示例返回約束標(biāo)識(shí)列（IDENTITY）概述當(dāng)用戶(hù)向表中插入新的數(shù)據(jù)行時(shí)，系統(tǒng)自動(dòng)為該行的IDENTITY列賦值，并保證其值在表中的唯一性。常與PRIMARYKEY約束一起運(yùn)用。每個(gè)表中只能有一個(gè)IDENTITY列，其列值不能由用戶(hù)更新，也不允許綁定默認(rèn)值或建立DEFAULT約束標(biāo)識(shí)列的有效數(shù)據(jù)類(lèi)型可以是任何整數(shù)數(shù)據(jù)類(lèi)型分類(lèi)的數(shù)據(jù)類(lèi)型(bit數(shù)據(jù)類(lèi)型除外)，也可以是decimal數(shù)據(jù)類(lèi)型，但不允許出現(xiàn)小數(shù)。創(chuàng)建SSMS方式T-SQL方式約束列表運(yùn)用SSMS工具返回標(biāo)識(shí)列（IDENTITY）

運(yùn)用T-SQL定義返回CREATETABEL數(shù)據(jù)表名(列名數(shù)據(jù)類(lèi)型IDENTITY[(種子,增量)]，[，...])CREATETABELBorrowReturn(……br_IDINTIDENTITY(1,1)PRIMARYKEY，)語(yǔ)法示例標(biāo)識(shí)列（IDENTITY）

DEFAULT（默認(rèn)值）概述“默認(rèn)值”是一種數(shù)據(jù)庫(kù)對(duì)象，可以被綁定到列。假如綁定的列或者數(shù)據(jù)類(lèi)型沒(méi)有明確供應(yīng)值，則將默認(rèn)指定的數(shù)據(jù)插入?！澳J(rèn)值”的執(zhí)行與DEFAULT約束功能相同，但是DEFAULT約束定義和表存儲(chǔ)在一起，當(dāng)除去表時(shí)，將自動(dòng)除去它。當(dāng)在多個(gè)列中，特殊是不同表中的列中多次運(yùn)用同一默認(rèn)值時(shí)，適合接受“默認(rèn)”技術(shù)。管理管理“默認(rèn)值”創(chuàng)建綁定解綁示例CREATE

DEFAULT

默認(rèn)名稱(chēng)

AS

常數(shù)表達(dá)式sp_bindefault‘默認(rèn)名稱(chēng)’,‘?dāng)?shù)據(jù)表名.列名’sp_unbindefault‘?dāng)?shù)據(jù)表名.列名’DEFAULT（默認(rèn)值）示例：創(chuàng)建綁定解綁返回sp_bindefault‘d_Ssex’,‘Student.Ssex’sp_unbindefault

‘Student.Ssex’USEStudyGOCREATEDEFAULTd_SsexAS‘女’DEFAULT（默認(rèn)值）RULE（規(guī)則）概述“規(guī)則”是一種數(shù)據(jù)庫(kù)對(duì)象，可以綁定到列，定義之后可以反復(fù)運(yùn)用。執(zhí)行“規(guī)則”功能與CHECK約束相同。每個(gè)列只能有一個(gè)綁定的規(guī)則。但列可以同時(shí)具有規(guī)則和多個(gè)CHECK約束。約束列表RULE（規(guī)則）管理“規(guī)則”創(chuàng)建綁定解綁示例CREATE

RULE

規(guī)則名稱(chēng)

AS

條件表達(dá)式sp_bindrule‘規(guī)則名’,‘?dāng)?shù)據(jù)表名.列名’sp_unbindrule‘?dāng)?shù)據(jù)表名.列名’RULE（規(guī)則）示例：創(chuàng)建綁定解綁返回sp_bindrule‘r_Ssex’,‘Student.Ssex’sp_unbindrule

‘Student.Ssex’USEStudyGOCREATERULEr_SsexAS@sex=‘女’OR@sex=‘男’觸發(fā)器概述觸發(fā)器觸發(fā)器是特殊類(lèi)型的存儲(chǔ)過(guò)程，主要由操作事務(wù)(INSERT、UPDATE、DELETE)觸發(fā)而被自動(dòng)執(zhí)行。與表相關(guān)聯(lián)：必需定義在表上。不能干脆調(diào)用，也不能傳遞或接受參數(shù)觸發(fā)器可以實(shí)現(xiàn)比約束更困難的數(shù)據(jù)完整性，常常用于加強(qiáng)數(shù)據(jù)的完整性約束和業(yè)務(wù)規(guī)則。觸發(fā)器本身是一個(gè)特殊的事務(wù)單位。觸發(fā)器概述觸發(fā)器的類(lèi)型按觸發(fā)時(shí)刻分

AFTER觸發(fā)器執(zhí)行觸發(fā)操作和處理完約束后激發(fā)，只能定義于表上，同一個(gè)表上可定義多個(gè)。

INSTEAD觸發(fā)器

由觸發(fā)器的程序代替觸發(fā)語(yǔ)句的執(zhí)行，在處理約束之前激發(fā)，可在表或視圖上創(chuàng)建，同一個(gè)表或視圖只可定義一個(gè)。按觸發(fā)語(yǔ)句分

INSERT觸發(fā)器

UPDATE觸發(fā)器、

DELETE觸發(fā)器觸發(fā)器Inserted表和deleted表觸發(fā)器觸發(fā)時(shí)，系統(tǒng)自動(dòng)在內(nèi)存創(chuàng)建deleted表或inserted表。它們是只讀的，不允許修改；觸發(fā)器執(zhí)行完成后，自動(dòng)刪除。inserted表臨時(shí)保存了插入或更新后的記錄行可以從inserted表中檢查插入的數(shù)據(jù)是否滿(mǎn)足業(yè)務(wù)需求，假如不滿(mǎn)足，則向用戶(hù)報(bào)告錯(cuò)誤消息，并回滾插入操作。deleted表臨時(shí)保存了刪除或更新前的記錄行可以從deleted表中檢查被刪除的數(shù)據(jù)是否滿(mǎn)足業(yè)務(wù)需求，假如不滿(mǎn)足，則向用戶(hù)報(bào)告錯(cuò)誤消息，并回滾插入操作。觸發(fā)器觸發(fā)器運(yùn)用的限制CREATETRIGGER必需是批處理中的第一條語(yǔ)句。在同一個(gè)表中約束的執(zhí)行優(yōu)先于觸發(fā)器，若在操作中觸發(fā)器與約束發(fā)生沖突，觸發(fā)器將不執(zhí)行。不能在臨時(shí)表上建立觸發(fā)器，但是在觸發(fā)器定義中可以引用臨時(shí)表。觸發(fā)器中不允許以下T-SQL語(yǔ)句：ALTERDATABASECREATEDATABASEDISKINITDISKRESIZEDROPDATABASELOADDATABASELOADLOGRECONFIGURERESTOREDATABASERESTORELOG

觸發(fā)器Inserted表和deleted表

inserted表和deleted表存放的信息修改操作inserted表deleted表增加(INSERT)記錄存放新增的記錄------刪除(DELETE)記錄-----存放被刪除的記錄修改(UPDATE)記錄存放更新后的記錄存放更新前的記錄觸發(fā)器創(chuàng)建（SSMS）觸發(fā)器創(chuàng)建（SSMS）創(chuàng)建觸發(fā)器的模板，只需修改相應(yīng)的參數(shù)就可創(chuàng)建觸發(fā)器觸發(fā)器創(chuàng)建（T－SQL）基本語(yǔ)句格式CREATETRIGGER

觸發(fā)器名ON

表名或視圖名{[FOR|AFTER]|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}AS

[IFUPDATE(列名1)[{AND|OR}UPDATE(列名2)][...n]]SQL語(yǔ)句觸發(fā)器創(chuàng)建（T－SQL）基本語(yǔ)句格式CREATETRIGGER

觸發(fā)器名ON

表名或視圖名{[FOR|AFTER]|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}AS

[IFUPDATE(列名1)[{AND|OR}UPDATE(列名2)][...n]]SQL語(yǔ)句觸發(fā)器創(chuàng)建（T－SQL）參數(shù)說(shuō)明AFTER:執(zhí)行指定操作語(yǔ)句和處理完約束后激發(fā)觸發(fā)器。FOR:假如僅指定FOR關(guān)鍵字，則AFTER是默認(rèn)設(shè)置。INSTEADOF:指定語(yǔ)句激發(fā)觸發(fā)器并被代替執(zhí)行。DELETE、INSERT、UPDATE：指定激活觸發(fā)器的數(shù)據(jù)修改語(yǔ)句，必需至少指定一個(gè)選項(xiàng)，若指定的選項(xiàng)多于一個(gè)，需用逗號(hào)分隔這些選項(xiàng)。AS：指定觸發(fā)器要執(zhí)行的操作。IFUPDATE(列名1)[{AND|OR}UPDATE(列名2)]:指定何列數(shù)據(jù)修改時(shí)激發(fā)觸發(fā)器。不能用于DELETE語(yǔ)句。觸發(fā)器創(chuàng)建（T－SQL）示例1CREATETRI