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