數(shù)據(jù)庫系統(tǒng)基礎(chǔ)教程(第7章)

第7章約束和觸發(fā)器1SQL中約束種類數(shù)據(jù)庫設(shè)計(jì)質(zhì)量體現(xiàn)為約束constraints所提供的可靠性保障。約束以表達(dá)式或語句的形式存儲在數(shù)據(jù)庫中。約束是一種主動性(active)元素，當(dāng)數(shù)據(jù)庫特定狀態(tài)發(fā)生改變時(shí)自動運(yùn)行。SQL2提供部分完整性約束：鍵、參照完整性、域約束、元組約束等。SQL3提供觸發(fā)器trigger機(jī)制：由特定事件觸發(fā)某種主動性元素。27.1鍵和外鍵3鍵與外鍵鍵key是最重要的約束。每個(gè)表都必須確定自己的鍵。每個(gè)表都可能有多個(gè)屬性集可作為鍵，稱為“候選鍵candicatekey”。一個(gè)表只能確定一個(gè)主鍵(PrimaryKey)。若某個(gè)屬性說明為Unique，則它是一個(gè)候選鍵。若關(guān)系的某個(gè)屬性說明為外鍵，則該屬性出現(xiàn)的值，一定會在另一個(gè)關(guān)系的主鍵中出現(xiàn)。4主鍵聲明如何說明表中的主鍵？SQL語言有兩種說明主鍵的方式。方式1：CreateTable語句中，某屬性說明之后加PrimaryKey方式2：屬性表之后，加PrimaryKey(屬性1，屬性2，…)若主鍵有多個(gè)屬性，則只能用方式2。GUI操作方式更方便直觀。5用UNIQUE聲明鍵如何說明表中的主鍵？SQL語言有兩種說明主鍵的方式。方式1：CreateTable語句中，某屬性說明之后加UNIQUE方式2：屬性表之后，加UNIQUE(屬性1，屬性2，…)若用UNIQUE說明鍵有多個(gè)屬性，則只能用方式2。GUI操作方式更方便直觀6例子考慮關(guān)系SalesMan的模式(方式1)CREATETABLESalesMan(empidVARCHAR(10)PRIMARYKEY,idnoVARCHAR(18)UNIQUE,nameVARCHAR(30),genderCHAR(1),birthdayDATE,addressVARCHAR(255),phoneVARCHAR(13));7例子考慮關(guān)系SalesMan的模式(方式2)CREATETABLESalesMan(empidVARCHAR(10),idnoVARCHAR(18),nameVARCHAR(30),genderCHAR(1),birthdayDATE,addressVARCHAR(255),phoneVARCHAR(13),PRIMARYKEY(empid),UNIQUE(idno));8主鍵和Unique屬性之間有何區(qū)別和聯(lián)系一個(gè)表有且僅有一個(gè)主鍵；而Unique屬性可有多個(gè)或沒有。主鍵是單個(gè)屬性，則該屬性隱含為Unique。主鍵不允許有NULL值，屬性為Unique則允許有NULL值。若主鍵是多個(gè)屬性，則每個(gè)屬性都不可能為Unique。DBMS對主鍵往往自動賦予一些特征，如建立索引index等。9強(qiáng)制鍵約束什么操作會導(dǎo)致違背鍵約束？對表的delete操作不會違背該表的鍵約束。insert和update可能違背該表的鍵約束。SQL實(shí)施主鍵約束即是在insert和update時(shí)檢驗(yàn)鍵值，避免空值或重復(fù)鍵值。10外鍵約束聲明如何說明外鍵兩種SQL方式：方式1：某屬性之后加references<被參照表>(被參照屬性)。方式2：屬性表之后加foreignkey<參照屬性>references<被參照表>(被參照屬性)。其中：被參照屬性應(yīng)是被參照表的PrimaryKey或unique屬性。11例子例：CREATETABLEStarsIn(MovietitleVARCHAR(90)NOTNULL,MovieyearINTNOTNULL,StarNameVARCHAR(30)NOTNULL,PRIMARYKEY(Movietitle,Movieyear,StarName),FOREIGNKEY(Movietitle,Movieyear)ReferencesMovie(title,year),FOREIGNKEYStarNameReferencesStars(name));12外鍵約束聲明是否可定義一個(gè)表參照自己？可以。例如：salesman(empid,idno,name,managerid,deptid,…)外鍵是否可取NULL值？可以。13維護(hù)引用完整性數(shù)據(jù)庫更新時(shí)如何保證參照完整性?有三種可選策略，以保證參照完整性：1Restrict限制(缺省)2Cascade級聯(lián)3SetNull置空14Restrict限制(缺省)以StarsIn(movieTitle,MovieYear,…)參照Movie(title,year,…)為例：對于StarsIn(參照表)，下面操作被拒絕：insert語句中movieTitle和MovieYear值不是Movie中已有的一個(gè)主鍵值。update語句中改變movieTitle和MovieYear值為不是Movie中已有的主鍵值。對于Movie(被參照表)，Restrict拒絕以下操作：delete語句刪除一個(gè)被StarsIn參照的元組。update語句修改一個(gè)被StarsIn參照的title和year主鍵值。15Cascade級聯(lián)影響被參照表的delete和update操作。以StarsIn(movieTitle,MovieYear,…)參照Movie(title,year,…)為例：當(dāng)Movie(被參照表)delete刪除某個(gè)元組時(shí)，StarsIn(參照表)中所有參照元組被自動刪除。當(dāng)Movie(被參照表)update修改某個(gè)元組的title或year值時(shí)，StarsIn(參照表)中所有參照元組被自動修改。16SetNull置空影響被參照表的delete和update操作。以Movie(title,year,…,ProducerC#)參照MovieExec(name,…,cert#,…)為例：首先ProducerC#應(yīng)允許NULL。當(dāng)MovieExec(被參照表)delete刪除某個(gè)元組時(shí)，Movie(參照表)中所有參照元組的ProducerC#被置空。當(dāng)MovieExec(被參照表)update修改某個(gè)元組的cert#時(shí)，Movie(參照表)中所有參照元組的ProducerC#被置空。17維護(hù)引用完整性策略的語法這些策略可在說明外鍵的同時(shí)描述。references<被參照表>(屬性表)[Action]其中：Action:ON{Update|Delete}{Restrict|Cascade|SetNull}18例子CREATETABLEMovie(titleVARCHAR(90)NOTNULL,yearINTNOTNULL,lengthINT,colorInCHAR(1)NOTNULL,producerC#INT,PRIMARYKEY(title,year),FOREIGNKEYproducerC#ReferencesMovieExec(cert#)ONDELETECASCADEONUPDATECASCADE);注意：對于一個(gè)外鍵，Update和Delete可分別采用不同的策略。19“懸掛元組”danglingtuples什么是“懸掛元組”danglingtuples？對于參照關(guān)系A(chǔ)，外鍵值未出現(xiàn)在被參照表中的元組，即違背參照完整性的元組。如何避免出現(xiàn)“懸掛元組”？1Restrict策略：在參照關(guān)系中對產(chǎn)生懸掛元組的操作予以禁止。2Cascade策略：自動刪除或修改產(chǎn)生出來的懸掛元組。3SetNull策略：產(chǎn)生出來的每個(gè)懸掛元組的外鍵值置空NULL，使其不參照任何元組。207.2屬性和元組上的約束21屬性值約束限制某些屬性的值在特定范圍內(nèi).這類約束可用下列方式表達(dá):⑴在關(guān)系式定義中給出屬性的約束。⑵在整個(gè)元組上的約束。該約束是關(guān)系模式的一部分，不與任何屬性相關(guān)。22非空約束如何說明某屬性非空NOTNULL？在createtable指令中，屬性說明之后加NOTNULL；若不顯式說明，該屬性隱含為允許空值。說明非空屬性有何效果？⑴不能用update修改其值為NULL。⑵insert時(shí)必須指定一個(gè)非空值。⑶不能使用外鍵的SetNull策略。注意：主鍵屬性和unique屬性隱含非空；允許為空的屬性不可能是主鍵或unique。23基于屬性的Check約束如何限制某屬性的值在特定范圍？在屬性說明之后，增加check(條件)，屬性的值應(yīng)使條件為真。條件的語法與Where子句的條件一樣。當(dāng)insert和update使屬性值改變時(shí)，執(zhí)行check檢驗(yàn)，拒絕預(yù)約屬性不一致的更新。24例子設(shè)有關(guān)系模式：Stodio(name,address,presC#)要求其證書號必須至少為６位數(shù)字，則可寫出約束：presC#INTREFERENCESMovieExec(cert#)CHECK(presC#>=100000)設(shè)有關(guān)系模式：MovieStar(name,address,gender,birthday)要求其性別只能為’Ｆ’和’Ｍ’，則可寫出約束：genderCHAR(1)CHECK(genderIN(’Ｆ’,’Ｍ’))25基于屬性的Check約束何時(shí)檢查check條件？只有當(dāng)該表執(zhí)行insert或update時(shí)才檢測check條件。注意：check條件中可包含其它關(guān)系的屬性，但應(yīng)避免。例如，設(shè)有關(guān)系Stodio(name,address,presC#)其關(guān)系中presC#值必須在關(guān)系MovieExec(name,address,cert#,networth)的cert#之中出現(xiàn)約束，則在定義中給出約束：presC#INTCHECK(presC#IN(SELECTcert#FROMMovieExec))修改或刪除cert#的值，使某個(gè)presC#對應(yīng)的鍵值不在MovieExec中是可能的。26域約束域domain也稱為用戶定義數(shù)據(jù)類型user-defineddatatype.基于某種基本數(shù)據(jù)類型且增加特定約束，存于數(shù)據(jù)庫中，可供多個(gè)表使用。例如：CREATEDOMAIN"sex"integerNOTNULLDEFAULT1CHECK(VALUEIN(0,1))27基于元組的Check約束對某個(gè)表中元組的約束而不是對某個(gè)屬性的約束。例如：在關(guān)系模式MovieStar(name,address,gender,birthday)中要求每個(gè)元組的審核：如果影星為男性，則他的名字不能與’Ms.’開頭。在定義表的性質(zhì)時(shí)增加如下約束：CHECK(gender=’F’ORnameNOTLIKE’Ms.%’)何時(shí)檢查check條件？只有當(dāng)該表執(zhí)行insert或update時(shí)才檢測check條件。注意：如果在表中既有屬性check也有元組check，則先檢測屬性。287.3修改約束29給約束命名為修改或刪除一個(gè)已經(jīng)存在的約束，約束必須有名字。為了命名，在約束前加保留字CONSTRAINT和該約束的名字。例如：設(shè)有關(guān)系模式MovieStar(name,address,gender,birthday)⑴為其主鍵約束命名：nameCHAR(30)CONSTRAINTNameIsKeyPRIMARYKEY⑵為影星的性別約束命名：genderCHAR(1)CONSTRAINTNoAndroCHECK(genderIN(’Ｆ’,’Ｍ’))30修改表上的約束可用ALTERTABLE來修改約束。用保留字DROP和要?jiǎng)h除的約束名字來刪除約束。用保留ADD，后跟要添加的約束來實(shí)現(xiàn)約束的添加。例如：要?jiǎng)h除MovieStar的主鍵約束ALTERTABLEMovieStarDROPNameIsKey要添加MovieStar的主鍵約束ALTERTABLEMovieStarADDCONSTRAINTNameIsKeyPRIMARYKEY(name)31斷言(Assertion)SQL標(biāo)準(zhǔn)提出了一種強(qiáng)制任何條件的簡單的斷言形式，與check類似。斷言的定義為CREATEASSERTION<斷言名>CHECK(<條件>)當(dāng)建立斷言時(shí)，斷言的條件必須是真，并且要永遠(yuǎn)保持為真。任何引起斷言條件為假的數(shù)據(jù)庫更新都被拒絕。32斷言(Assertion)斷言要寫什么？在寫斷言條件時(shí)所引用的任何屬性都必須標(biāo)明。何時(shí)檢查斷言的check條件？只有當(dāng)該表執(zhí)行insert或update時(shí)才檢測check條件。何時(shí)刪除斷言？DROPASSERTION<斷言名>33例子假定希望其凈資產(chǎn)值少于10000000的人不能成為制片廠經(jīng)理。CREATEASSERTIONRichPresCHECK(NOTEXISTS(SELECT*FROMStudio,MovieExecWHEREpresC#=cert#ANDnetworth<10000000))注意與約束的區(qū)別：CREATETABLEStudio(nameCHAR(30)PRIMARYKEY,addressVARCHAR(255),presC#INTREFERENCESMovieExec(cert#),CHECK(presC#NOTIN(SELECTcert#FROMMovieExecWHEREnetworth<10000000)))34約束的比較下面表格列出了基于屬性的檢查約束、基于元組的檢查約束和斷言之間的主要區(qū)別。約束類型聲明的位置動作的時(shí)間確保成立？基于屬性的CHECK屬性對關(guān)系插入元組或?qū)傩孕薷娜绻亲硬樵?，則不能確保基于元組的CHECK關(guān)系模式元素對關(guān)系插入元組或?qū)傩孕薷娜绻亲硬樵?，則不能確保斷言數(shù)據(jù)庫模式元素對任何提及的關(guān)系改變是357.4觸發(fā)器36觸發(fā)器(trigger)基于特定事件觸發(fā)的特定的約束檢驗(yàn)。注意：各種商業(yè)數(shù)據(jù)庫可能使用不同語法。觸發(fā)器是什么？一個(gè)觸發(fā)器trigger是存儲在某個(gè)表中的一個(gè)命名的數(shù)據(jù)庫對象。當(dāng)該表進(jìn)行某種數(shù)據(jù)更新時(shí)，將自動觸發(fā)一組SQL語句的執(zhí)行。37事件－條件－動作規(guī)則Event事件：數(shù)據(jù)更新指令：insert/delete/updateCondition條件：當(dāng)事件發(fā)生后，檢查條件是否滿足：若不滿足，則不執(zhí)行動作而狀態(tài)轉(zhuǎn)移。若滿足，則執(zhí)行一組動作之后狀態(tài)轉(zhuǎn)移。Action動作：一組SQL指令，通常是更新操作Old狀態(tài)New狀態(tài)Event/Condition[Action]38觸發(fā)器與約束不同當(dāng)數(shù)據(jù)庫程序員聲明的事件發(fā)生時(shí)，觸發(fā)器被激活。事件可以是對某個(gè)特定關(guān)系的插入、刪除或修改。當(dāng)觸發(fā)器被事件激活時(shí)，不是立即執(zhí)行，而是首先由觸發(fā)器測試觸發(fā)條件。如果條件不成立，則響應(yīng)事件的觸發(fā)器不做任何事情。如果觸發(fā)器聲明的條件滿足，則與觸發(fā)器相連的動作由DBMS執(zhí)行。39SQL中的觸發(fā)器特征動作可以在觸發(fā)事件之前或之后被執(zhí)行。在被觸發(fā)的事件中，動作既可指向被插入、刪除、修改元組的新值，也可以指向其舊值。更新事件可以被局限到某個(gè)特定的屬性或某一些屬性。條件由WHEN短語給出。僅僅當(dāng)規(guī)則被觸發(fā)，并且觸發(fā)事件的發(fā)生使條件成立時(shí)，動作才被執(zhí)行。程序員可以選擇動作執(zhí)行的聲明方式：一次只對一個(gè)更新元組，或者一次針對在數(shù)據(jù)庫操作中被改變的所有元組。40觸發(fā)器的語法CREATETRIGGERtrigger-nameON[table-name]FORtrigger-eventAS……trigger-name：一個(gè)表可定義多個(gè)不重名的觸發(fā)器。trigger-event:DELETE|INSERT|UPDATEAS：是觸發(fā)器要執(zhí)行的操作。41例子要求：每個(gè)銷售員只能屬于某一個(gè)部門；部門經(jīng)理只能由本部門中某個(gè)銷售員擔(dān)任。Salesman(empid,idno,name,gender,phone,deptid)Department(deptid,name,headerid)42例子僅靠外鍵建立的參照完整性，不能解決的問題：1當(dāng)updatedepartmentsetheaderid=?wheredeptid=?時(shí)不能保證新headerid在Salesman中是本部門成員。例如：updatedepartmentsetheaderid='A0044'wheredeptid=2;'A0044'是部門1的成員卻擔(dān)任了部門2的經(jīng)理。應(yīng)在其擔(dān)任部門2經(jīng)理之前，先更新salesman使其作為部門2的成員。updatesalesmansetdeptid=2whereempid='A0044'43例子CREATETRIGGERT_EditPresONdepartme