第6章-實(shí)現(xiàn)數(shù)據(jù)完整性約束new

第6章實(shí)現(xiàn)數(shù)據(jù)庫(kù)完整性約束完整性的概念完整性約束條件完整性控制

SQLServer2000的完整性控制1數(shù)據(jù)庫(kù)完整性數(shù)據(jù)庫(kù)的完整性數(shù)據(jù)的正確性和相容性數(shù)據(jù)的完整性和安全性是兩個(gè)不同概念數(shù)據(jù)的完整性防止數(shù)據(jù)庫(kù)中存在不符合語(yǔ)義的數(shù)據(jù)，也就是防止數(shù)據(jù)庫(kù)中存在不正確的數(shù)據(jù)防范對(duì)象：不合語(yǔ)義的、不正確的數(shù)據(jù)數(shù)據(jù)的安全性保護(hù)數(shù)據(jù)庫(kù)防止惡意的破壞和非法的存取防范對(duì)象：非法用戶和非法操作2完整性約束條件完整性約束條件作用的對(duì)象列：對(duì)屬性的取值類型、范圍、精度等的約束條件元組：對(duì)元組中各個(gè)屬性列間的聯(lián)系的約束關(guān)系：對(duì)若干元組間、關(guān)系集合上以及關(guān)系之間的聯(lián)系的約束兩種狀態(tài)靜態(tài):對(duì)靜態(tài)對(duì)象的約束是反映數(shù)據(jù)庫(kù)狀態(tài)合理性的約束動(dòng)態(tài):對(duì)動(dòng)態(tài)對(duì)象的約束是反映數(shù)據(jù)庫(kù)狀態(tài)變遷的約束，新舊值之間滿足的約束3完整性約束的分類4靜態(tài)列級(jí)約束對(duì)列的取值域的說(shuō)明最常見(jiàn)、最簡(jiǎn)單、最容易實(shí)現(xiàn)的一類完整性約束五類靜態(tài)列級(jí)約束1)數(shù)據(jù)類型約束：數(shù)據(jù)的類型、長(zhǎng)度、單位、精度等2)對(duì)數(shù)據(jù)格式的約束：如日期為YY.MM.DD3)取值范圍或取值集合的約束4)對(duì)空值的約束：未定義或未知的值，與零值和空格不同5)其他約束：如關(guān)于列的排序說(shuō)明，組合列等

5靜態(tài)元組約束規(guī)定元組的各個(gè)列之間的約束關(guān)系例：訂貨關(guān)系中發(fā)貨量<=訂貨量工資關(guān)系中fact=salary+bonus靜態(tài)元組約束只局限在元組上6靜態(tài)關(guān)系約束關(guān)系的各個(gè)元組之間或若干關(guān)系之間存在的各種聯(lián)系或約束常見(jiàn)靜態(tài)關(guān)系約束：實(shí)體完整性約束參照完整性約束函數(shù)依賴約束：關(guān)系字段間存在的函數(shù)依賴統(tǒng)計(jì)約束：定義某個(gè)字段值一個(gè)關(guān)系多個(gè)元組的統(tǒng)計(jì)值之間的約束關(guān)系7動(dòng)態(tài)列級(jí)約束動(dòng)態(tài)列級(jí)約束是修改列定義或列值時(shí)應(yīng)滿足的約束條件1)修改列定義時(shí)的約束 例：將原來(lái)允許空值的列改為不允許空值時(shí):

該列目前已存在空值，則拒絕這種修改2)修改列值時(shí)的約束修改列值時(shí)新舊值之間要滿足的約束條件例：職工工資調(diào)整>=原來(lái)工資年齡只能增長(zhǎng)8動(dòng)態(tài)元組、關(guān)系約束動(dòng)態(tài)元組約束修改元組值:各個(gè)字段之間要滿足的約束條件例:職工工資調(diào)整不得低于其原來(lái)工資+工齡*1.5動(dòng)態(tài)關(guān)系約束關(guān)系變化前后狀態(tài)：限制條件例：事務(wù)一致性、原子性等約束條件9完整性約束條件小結(jié)

粒度狀態(tài)列級(jí)

元組級(jí)

關(guān)系級(jí)

靜態(tài)列定義類型格式值域空值…

元組值應(yīng)滿足的條件

實(shí)體完整性約束參照完整性約束函數(shù)依賴約束統(tǒng)計(jì)約束動(dòng)態(tài)改變列定義或列值元組新舊值之間應(yīng)滿足的約束條件

關(guān)系新舊狀態(tài)間應(yīng)滿足的約束條件

10完整性控制定義功能一個(gè)完善的完整性控制機(jī)制應(yīng)該允許用戶定義各類完整性約束條件檢查功能立即執(zhí)行的約束：語(yǔ)句執(zhí)行完后立即檢查是否違背完整性約束延遲執(zhí)行的約束：完整性檢查延遲到整個(gè)事務(wù)執(zhí)行結(jié)束后進(jìn)行違約反應(yīng)

拒絕該操作其他處理方法11例子例：銀行數(shù)據(jù)庫(kù)中“借貸總金額應(yīng)平衡”的約束從賬號(hào)A轉(zhuǎn)一筆錢到賬號(hào)B為一個(gè)事務(wù)，從賬號(hào)A轉(zhuǎn)出去錢后賬就不平了，必須等轉(zhuǎn)入賬號(hào)B后賬才能重新平衡，這時(shí)才能進(jìn)行完整性檢查。應(yīng)該是延遲執(zhí)行的約束！12完整性規(guī)則五元組表示

(D，O，A，C，P)D（Data）：約束作用的數(shù)據(jù)對(duì)象O（Operation）：觸發(fā)完整性檢查的數(shù)據(jù)庫(kù)操作當(dāng)用戶發(fā)出什么操作請(qǐng)求時(shí)需要檢查該完整性規(guī)則是立即檢查還是延遲檢查；A（Assertion）：數(shù)據(jù)對(duì)象必須滿足的斷言或語(yǔ)義約束，這是規(guī)則的主體；C（Condition）：選擇A作用的數(shù)據(jù)對(duì)象值的謂詞P（Procedure）：違反完整性規(guī)則時(shí)觸發(fā)的過(guò)程13例子實(shí)例例1：學(xué)號(hào)不能為空例2：教授工資不得低于1000元數(shù)據(jù)對(duì)象D約束作用的對(duì)象為Sno屬性約束作用的對(duì)象為工資Sal屬性操作O插入或修改Student元組時(shí)插入或修改職工元組時(shí)斷言ASno不能為空Sal不能小于1000謂詞C無(wú)（A可作用于所有記錄的Sno屬性）職稱=‘教授’(A僅作用于職稱=‘教授’的記錄)過(guò)程P拒絕執(zhí)行該操作拒絕執(zhí)行該操作14SQLServer2000的完整性控制約束管理技術(shù)：約束默認(rèn)規(guī)則觸發(fā)器數(shù)據(jù)完整性：實(shí)體完整性域完整性引用完整性用戶定義完整性15實(shí)體完整性主鍵（PK，PrimaryKey）UNIQUE約束要求關(guān)系的主鍵中屬性值不能為空，這是數(shù)據(jù)庫(kù)完整性的最基本要求，因?yàn)橹麈I是惟一決定元組的，如為空則其惟一性就成為不可能的了。常通過(guò)以下技術(shù)進(jìn)行定義：16域完整性字段數(shù)據(jù)類型數(shù)據(jù)格式（通過(guò)CHECK約束和規(guī)則）取值范圍，通過(guò)：FK約束CHECK約束DEFAULT定義NOTNULL定義其他列規(guī)則又稱“列完整性”，它指定某一列的輸入數(shù)據(jù)的有效性，定義域完整性的方法有：17引用（參照）完整性引用完整性的實(shí)現(xiàn)技術(shù)：外鍵（FK，F(xiàn)oreignKey）a)

要求子表中的相關(guān)項(xiàng)必須在主表中存在b)

如果建立了主表和子表的關(guān)系，則：子表中的相關(guān)項(xiàng)目的數(shù)據(jù)，在主表中必須存在；主表中相關(guān)項(xiàng)的數(shù)據(jù)更改了，則子表對(duì)應(yīng)的數(shù)據(jù)項(xiàng)也應(yīng)當(dāng)隨之更改；在刪除子表之前，不能夠刪除主表；18用戶定義完整性是針對(duì)具體數(shù)據(jù)環(huán)境與應(yīng)用環(huán)境由用戶具體設(shè)置的約束，它反映了具體應(yīng)用中數(shù)據(jù)的語(yǔ)義要求。可以使用CREATETBALE中的所有列級(jí)和表級(jí)約束、存儲(chǔ)過(guò)程（createproc）和觸發(fā)器（createtrigger）來(lái)進(jìn)行定義。19約束分類PRIMARYKEY：主鍵約束FOREIGNKEY：外鍵約束UNIQUE：唯一約束CHECK：檢查約束DEFAULT：默認(rèn)值約束20SQLServer添加約束的語(yǔ)法ALTERTABLE

表名

ADDCONSTRAINT

約束名

約束類型

具體的約束說(shuō)明約束名的取名規(guī)則推薦采用：約束類型_約束字段主鍵（PrimaryKey）約束：如

PK_sno

唯一（UniqueKey）約束：如

UQ_stuID

默認(rèn)（DefaultKey）約束：如

DF_stuAddress

檢查（CheckKey）約束：如

CK_sage

外鍵（ForeignKey）約束：如

FK_sno21

主鍵（PrimaryKey）約束/*為學(xué)員信息表添加主鍵約束（sno作為主鍵）*/ALTERTABLEstudentADDCONSTRAINT

PK_sno

PRIMARYKEY(sno)

要求主鍵列數(shù)據(jù)唯一，并且不允許為空。例：22外鍵約束（ForeignKeyConstraint）/*添加外鍵約束（主表student和從表sc建立關(guān)系，關(guān)聯(lián)字段為sno）*/ALTERTABLEscADDCONSTRAINT

FK_sno

FOREIGNKEY(sno)

REFERENCES

student(sno)用于兩表間建立關(guān)系，需要指定引用主表的那列。例：23唯一（UniqueKey）約束/*添加唯一約束（因?yàn)槊咳说纳矸葑C號(hào)全國(guó)唯一）*/ALTERTABLEstudentADDCONSTRAINT

UQ_stuID

UNIQUE(stuID)要求該列唯一，允許為空，但只能出現(xiàn)一個(gè)空值。

例：24檢查約束（CheckConstraint）/*添加檢查check約束，要求年齡只能在15－40歲之間*/ALTERTABLEstudentADDCONSTRAINT

CK_sage

CHECK(sageBETWEEN15AND40)某列取值范圍限制、格式限制等，如有關(guān)年齡的約束。例：25默認(rèn)（DefaultKey）約束/*添加默認(rèn)約束（如果地址不填，默認(rèn)為“地址不詳”）*/ALTERTABLEstudentADDCONSTRAINT

DF_stuAddress

DEFAULT('地址不詳')FOR

stuAddress某列的默認(rèn)值，如我們的男性學(xué)員較多，性別默認(rèn)為“男”。例：26SQLServer中刪除約束如果錯(cuò)誤地添加了約束，我們還可以刪除約束刪除約束的語(yǔ)法：ALTERTABLE

表名

DROPCONSTRAINT

約束名例如：刪除stuInfo表中地址默認(rèn)約束ALTER

TABLE

stuInfo

DROP

CONSTRAINT

DF_stuAddress27SQL中完整性約束：觸發(fā)器前面提到的一些約束機(jī)制屬于被動(dòng)約束機(jī)制，在檢查出違背約束后，只能做些比較簡(jiǎn)單的動(dòng)作，比如拒絕操作。比較復(fù)雜的操作還需要程序員去安排。如果希望在某個(gè)操作后，系統(tǒng)能自動(dòng)根據(jù)條件轉(zhuǎn)去執(zhí)行各種操作，甚至執(zhí)行與原操作無(wú)關(guān)的一些操作，那么這種設(shè)想可以用觸發(fā)器機(jī)制實(shí)現(xiàn)。觸發(fā)器是一個(gè)能由系統(tǒng)自動(dòng)執(zhí)行對(duì)數(shù)據(jù)庫(kù)修改的語(yǔ)句。觸發(fā)器有時(shí)也稱為主動(dòng)規(guī)則或事件-條件-動(dòng)作規(guī)則(簡(jiǎn)稱為ECA規(guī)則)。28SQL觸發(fā)器的結(jié)構(gòu)---三個(gè)部分觸發(fā)事件：是指對(duì)數(shù)據(jù)庫(kù)的插入Insert

、刪除delete

、修改update等操作。在這些事件發(fā)生時(shí)，觸發(fā)器將開(kāi)始工作。約束條件：觸發(fā)器將測(cè)試約束條件是否成立。如果成立就執(zhí)行相應(yīng)的動(dòng)作，否則什么也不做。違約反應(yīng)動(dòng)作：指明觸發(fā)器執(zhí)行的動(dòng)作是什么。如果觸發(fā)器測(cè)試滿足預(yù)定的條件，那么就由DBMS執(zhí)行這些動(dòng)作（即對(duì)數(shù)據(jù)庫(kù)的操作）。這些動(dòng)作能使觸發(fā)事件不發(fā)生，這些動(dòng)作也可以是一系列對(duì)數(shù)據(jù)庫(kù)的操作，甚至可以是與觸發(fā)事件本身無(wú)關(guān)的其他操作。29設(shè)有關(guān)系模式：sc(sno,cno,grade)假設(shè)SC控制的規(guī)則是：當(dāng)插入成績(jī)時(shí)，檢查grade是否在0-100之間，如果grade<0，則發(fā)出“成績(jī)不能為負(fù)數(shù)”的信息，并回滾事務(wù)；如果成績(jī)大于100，則發(fā)出“成績(jī)不能超過(guò)滿分”的信息，且回滾事務(wù)；當(dāng)插入成績(jī)符合要求時(shí)，允許插入數(shù)據(jù)。SQL觸發(fā)器：例30CreatetriggerCTR