數(shù)據(jù)庫系統(tǒng)原理-第七章約束與觸發(fā)器

第7章約束與觸發(fā)器

7.1鍵與外鍵7.2屬性和元組上的約束7.3修改約束7.4斷言7.5觸發(fā)器Page1SQL中約束種類數(shù)據(jù)庫設計質(zhì)量體現(xiàn)為約束constraints所提供的可靠性保障。約束以表達式或語句的形式存儲在數(shù)據(jù)庫中。約束是一種主動性(active)元素，當數(shù)據(jù)庫特定狀態(tài)發(fā)生改變時自動運行。SQL2提供部分完整性約束：鍵、參照完整性、域約束、元組約束等。SQL3提供觸發(fā)器trigger機制：由特定事件觸發(fā)某種主動性元素。Page2在第二章中已經(jīng)介紹了SQL中通過保留字PRIMARYKEY或UNIQUE來定義一個屬性或一組屬性為關系的鍵。如：CREATETABLEMovies(titlechar(20),yearint,lengthint,genrechar(10),studioNamechar(30),producerCint,PRIMARYKEY(title,year));7.1鍵與外鍵Page3鍵key是最重要的約束。每個表都必須確定自己的鍵。每個表都可能有多個屬性集可作為鍵，稱為“候選鍵candidatekey”。一個表只能確定一個主鍵(PrimaryKey)。若某個屬性說明為Unique，則它是一個候選鍵。若關系的某個屬性說明為外鍵，則該屬性出現(xiàn)的值，一定會在另一個關系的主鍵中出現(xiàn)。7.1鍵與外鍵Page47.1鍵與外鍵外鍵約束聲明隱含兩層意思：①被引用的另一個關系的屬性必須是主鍵②外鍵屬性取值必須屬于被引用的另一個關系的主鍵屬性的值。Page5舉例學籍管理數(shù)據(jù)庫Page6聲明外鍵的方法和聲明主鍵的方法一樣，聲明外鍵的方法也有兩種。①如果外鍵是單個屬性，則可以在此屬性的名字和類型之后，聲明其引用某個表的某個屬性。格式如下：REFERENCES<表名>(<屬性名>)Page7舉例電影數(shù)據(jù)庫模式如下：Movies(title,year,length,genre,studioName,producerC)

名稱年份長度(分鐘)流派電影公司名稱導演證書號MovieStar(name,address,gender,birthdate)

姓名住址性別生日StarsIn(movieTitle,movieYear,starName)

影片名稱年份主演姓名MovieExec(name,address,cert,netWorth)

導演姓名住址導演證書號凈資產(chǎn)Studio(name,address,presC)

電影公司名稱地址經(jīng)理證書號要聲明StarsIn表中的starName屬性對MovieStar表的name屬性的引用完整性約束，聲明方法如下：Page8舉例CREATETABLEStarsIn(movieTitlechar(20),movieYearint,starNamechar(12)REFERENCESMovieStar(name),PRIMARYKEY(movieTitle,movieYear,starName));Page9舉例CREATETABLEStarsIn(movieTitlechar(20),movieYearint,starNamechar(12)REFERENCESMovieStar(name),PRIMARYKEY(movieTitle,movieYear,starName));如果在StarsIn表中插入周星馳參演少林足球信息(周星馳未在MovieStar中出現(xiàn))，能否插入？INSERTINTOStarsInVALUES('少林足球',2001,'周星馳');不能，因為違反了在starName屬性上定義的引用完整性約束。Page10舉例CREATETABLEStarsIn(movieTitlechar(20),movieYearint,starNamechar(12)REFERENCESMovieStar(name),PRIMARYKEY(movieTitle,movieYear,starName));如果在StarsIn表中插入徐帆參演唐山大地震的信息（徐帆在MovieStar）中出現(xiàn)，能否插入？INSERTINTOStarsInVALUES('唐山大地震',2001,'徐帆');能，因為不違反在starName屬性上定義的引用完整性約束。Page11聲明外鍵的方法和聲明主鍵的方法一樣，聲明外鍵的方法也有兩種。②如果外鍵是由兩個屬性以上的屬性組成，則要在CREATETABLE語句中專門追加一個聲明，格式如下：FOREIGNKEY(<屬性名列表>)REFERENCES<表名>(<屬性名列表>)Page12舉例電影數(shù)據(jù)庫模式如下：Movies(title,year,length,genre,studioName,producerC)

名稱年份長度(分鐘)流派電影公司名稱導演證書號MovieStar(name,address,gender,birthdate)

姓名住址性別生日StarsIn(movieTitle,movieYear,starName)

影片名稱年份主演姓名MovieExec(name,address,cert,netWorth)

導演姓名住址導演證書號凈資產(chǎn)Studio(name,address,presC)

電影公司名稱地址經(jīng)理證書號要聲明StarsIn表中的movieTitle,movieYear屬性對Movies表的title,year屬性的引用完整性約束。Page13舉例CREATETABLEStarsIn(movieTitlechar(20),movieYearint,starNamechar(12)REFERENCESMovieStar(name),PRIMARYKEY(movieTitle,movieYear,starName),FOREIGNKEY(movieTitle,movieYear)REFERENCESMovies(title,year));聲明StarsIn表中的movieTitle,movieYear屬性對Movies表的title,year屬性的引用完整性約束的方法如下：Page14舉例此時，如果在StarsIn表中插入徐帆參演唐山大地震的信息（未出現(xiàn)在Movies中），能否插入，為什么？INSERTINTOStarsInVALUES('唐山大地震',2001,'徐帆');不能，雖然不違反在starName屬性上定義的引用完整性約束。但是違反了在movieTitle和movieYear屬性上定義的引用完整性約束。Page15外鍵約束聲明是否可定義一個表參照自己？可以。例如：salesman(empid,idno,name,managerid,deptid,…)外鍵是否可取NULL值？可以。Page16維護引用完整性數(shù)據(jù)庫更新時如何保證參照完整性?

有三種可選策略，以保證參照完整性：1Restrict限制(缺省)2Cascade級聯(lián)3SetNull置空Page17Restrict限制(缺省)以StarsIn(movieTitle,MovieYear,…)參照Movie(title,year,…)為例：對于StarsIn(參照表)，下面操作被拒絕：insert語句中movieTitle和MovieYear值不是Movie中已有的一個主鍵值。update語句中改變movieTitle和MovieYear值為不是Movie中已有的主鍵值。對于Movie(被參照表)，Restrict拒絕以下操作：delete語句刪除一個被StarsIn參照的元組。update語句修改一個被StarsIn參照的title和year主鍵值。Page18Cascade級聯(lián)影響被參照表的delete和update操作。以StarsIn(movieTitle,MovieYear,…)參照Movie(title,year,…)為例：當Movie(被參照表)delete刪除某個元組時，StarsIn(參照表)中所有參照元組被自動刪除。當Movie(被參照表)update修改某個元組的title或year值時，StarsIn(參照表)中所有參照元組被自動修改。Page19SetNull置空影響被參照表的delete和update操作。以Movie(title,year,…,ProducerC#)參照MovieExec(name,…,cert#,…)為例：首先ProducerC#應允許NULL。當MovieExec(被參照表)delete刪除某個元組時，Movie(參照表)中所有參照元組的ProducerC#被置空。當MovieExec(被參照表)update修改某個元組的cert#時，Movie(參照表)中所有參照元組的ProducerC#被置空。Page20維護引用完整性策略的語法這些策略可在說明外鍵的同時描述。

references<被參照表>(屬性表)[Action]

其中：

Action:ON{Update|Delete}{Restrict|Cascade|SetNull}Page21例子CREATETABLEStudio(nameVARCHAR(30)NOTNULL,addressVARCHAR(255),presC#INT,

PRIMARYKEY(Name),

FOREIGNKEY(presC#)ReferencesMovieExec(cert#)

ONDELETESETNULL

ONUPDATECASCADE);注意：對于一個外鍵，Update和Delete可分別采用不同的策略。Page22“懸掛元組”danglingtuples什么是“懸掛元組”danglingtuples？對于參照關系A，外鍵值未出現(xiàn)在被參照表中的元組，即違背參照完整性的元組。如何避免出現(xiàn)“懸掛元組”？1Restrict策略：在參照關系中對產(chǎn)生懸掛元組的操作予以禁止。2Cascade策略：自動刪除或修改產(chǎn)生出來的懸掛元組。3SetNull策略：產(chǎn)生出來的每個懸掛元組的外鍵值置空NULL，使其不參照任何元組。Page23延遲約束檢查假設施瓦辛格卸任加州州長后，建立一個電影公司，公司名稱為LaVista，他作為公司的經(jīng)理，則InsertintoStudioValues('LaVista','NewYork',23456)則數(shù)據(jù)插入有一些麻煩，原因是假設23456是最新頒發(fā)的證書。InsertintoStudio(name,address)Values('LaVista','NewYork')將證書號插入MovieExec關系后，修改UpdateStudioSETpresC#=23456Wherename='LaVista'Page24延遲約束檢查當如果發(fā)生循環(huán)約束(circularconstraint)時，上述的安排也不能解決問題。例：如果電影制片人被約束為電影公司經(jīng)理，則要聲明cent#是引用Studio(presC#)的外鍵。于是presC#必須聲明為UNIQUE。這樣就不能進行插入操作。解決方法：1.必須將兩個插入操作組成一個單一的事務。2.需要有一種方法通知DBMS不要檢查約束，直到整個事務完成執(zhí)行并提交為止。Page25延遲約束檢查在任何的約束聲明后可有DEFERRABLE或NOTDEFERRABLE(缺省值)選項。如果沒有明確寫明則表示進行更新操作時，立即檢查該約束。但如果約束被聲明為DEFERRABLE，則約束檢查將推遲到當前事務完成時進行。保留字DEFERRABLE后面有兩個選項：INITIALLYDEFERRED:檢查僅推遲到事務提交前執(zhí)行INITIALLYIMMERDIATE:檢查在每個語句后立即執(zhí)行對于推遲約束檢查，有兩點要記?。喝魏晤愋偷募s束都可以命名。如果約束有名字，則可以用SQL語句將該約束從立即檢查改為推遲檢查。Page26練習P.188/7.1.1/b)CreateTableMovies(titleChar(30),yearINT,….producerC#INTReferencesMovieExec(cert#)OnDeleteSetNULLOnUpdateSetNULLPrimaryKey(title,year));Page27SQL的CREATETABLE語句中還可以聲明兩種其他約束：①在單一屬性上的約束；②在整個元組上的約束。7.2.1非空值約束7.2.2基于屬性的CHECK約束7.2.3基于元組的CHECK約束7.2屬性和元組上的約束Page28屬性值約束限制某些屬性的值在特定范圍內(nèi).這類約束可用下列方式表達:⑴在關系式定義中給出屬性的約束。⑵在整個元組上的約束。該約束是關系模式的一部分，不與任何屬性相關。Page29非空約束如何說明某屬性非空NOTNULL？在createtable指令中，屬性說明之后加NOTNULL；若不顯式說明，該屬性隱含為允許空值。說明非空屬性有何效果？⑴不能用update修改其值為NULL。⑵insert時必須指定一個非空值。⑶不能使用外鍵的SetNull策略。注意：主鍵屬性和unique屬性隱含非空；允許為空的屬性不可能是主鍵或unique。Page30舉例CREATETABLEStarsIn(movieTitlechar(20)NOTNULL,movieYearintNOTNULL,starNamechar(12)REFERENCESMovieStar(name)

NOTNULL,PRIMARYKEY(movieTitle,movieYear,starName),FOREIGNKEY(movieTitle,movieYear)REFERENCESMovies(title,year));聲明非空值約束的方法如下：Page31基于屬性的Check約束如何限制某屬性的值在特定范圍？在屬性說明之后，增加check(條件)，屬性的值應使條件為真。條件的語法與Where子句的條件一樣。當insert和update使屬性值改變時，執(zhí)行check檢驗，拒絕導致屬性值不一致的更新。Page32例子設有關系模式：

Studio(name,address,presC#)

要求其證書號必須至少為６位數(shù)字，則可寫出約束：

presC#INTREFERENCESMovieExec(cert#)CHECK(presC#>=10000000)設有關系模式：

MovieStar(name,address,gender,birthday)

要求其性別只能為'Ｆ'

和'Ｍ'，則可寫出約束：

genderCHAR(1)CHECK(genderIN('Ｆ','Ｍ'))Page33基于屬性的Check約束何時檢查check條件？只有當該表執(zhí)行insert或update時才檢測check條件。注意：check條件中可包含其它關系的屬性，但應避免（除了是子查詢的From子句中出現(xiàn)的關系）。例如，設有關系

Studio(name,address,presC#)

其關系中presC#值必須在關系

MovieExec(name,address,cert#,networth)

的cert#之中出現(xiàn)，則在定義中給出約束：

presC#INTCHECK(presC#IN(SELECTcert#FROMMovieExec))

修改或刪除cert#的值，使某個presC#對應的鍵值不在MovieExec中是可能的。Page34基于元組的Check約束對某個表中元組的約束而不是對某個屬性的約束。例如：在關系模式

MovieStar(name,address,gender,birthday)

中要求每個元組的審核：如果影星為男性，則他的名字不能以’Ms.’開頭。在定義表的性質(zhì)時增加如下約束：

CHECK(gender='F'ORnameNOTLIKE'Ms.%')何時檢查check條件？只有當該表執(zhí)行insert或update時才檢測check條件。注意：如果在表中既有屬性check也有元組check，則先檢測屬性。Page35基于元組和基于屬性約束的比較如果元組上的約束涉及該元組的多個屬性，那么就必須作為基于元組的約束。如果約束僅涉及元組的一個屬性，那么可以作為基于元組或基于屬性的約束。約束檢查基于屬性的約束：條件檢查是一樣的?；谠M的約束：比基于屬性的約束更頻繁的被檢查。Page36練習P.192/7.2.3/a)CreateTableStarsIn(….starnameChar(30)Check(starnameIn(SelectnameFromMovieStarWhereYear(birthdate)<movieYear))….);Page3738在SQL中提供了增加、刪除和修改約束的方式。7.3.1給約束命名7.3.2修改表上的約束7.3修改約束397.3.1給約束命名修改或刪除約束是通過約束的名字進行的。創(chuàng)建約束時用戶可以給約束命名，否則DBMS會給約束起一個名字。用戶給約束命名的方式：在約束前面加保留字CONSTRAINT和該約束的名字。40舉例CREATETABLEMovieStar(namechar(12)CONSTRAINTNameIsKeyPRIMARYKEY,addresschar(30),genderchar(2)CONSTRAINTNoAndroCHECK(genderIN('M','F')),birthdatedate,

CONSTRAINTRightTitleCHECK(gender='F'ORnameNOTLIKE'Ms.%'));417.3.2修改表上的約束通過ALTERTABLE語句對約束進行修改ALTERTABLE語句有多種方式影響約束：①用保留字DROP和要刪除的約束的名字可以刪除約束；②用保留字ADD，后跟要添加的約束實現(xiàn)約束添加。42舉例刪除MovieStar表中的約束。ALTERTABLEMovieStarDROPCONSTRAINTNameIsKey;ALTERTABLEMovieStarDROPCONSTRAINTNoAndro;ALTERTABLEMovieStarDROPCONSTRAINTRightTitle;43舉例在MovieStar表中添加約束。ALTERTABLEMovieStarADDCONSTRAINTNameIsKeyPRIMARYKEY(name);ALTERTABLEMovieStarADDCONSTRAINTNoAndroCHECK(genderIN('M','F'));ALTERTABLEMovieStarADDCONSTRAINTRightTitleCHECK(gender='F'ORnameNOTLIKE'Ms.%');SQL標準提出了一種強制任何條件的簡單的斷言形式，與check類似，但其用于關系之間的約束。斷言的定義為

CREATEASSERTION<斷言名>CHECK(<條件>)當建立斷言時，斷言的條件必須是真，并且要永遠保持為真。任何引起斷言條件為假的數(shù)據(jù)庫更新都被拒絕。7.4斷言Page44斷言使用斷言要寫什么？在寫斷言條件時所引用的任何屬性都必須標明。何時檢查斷言的check條件？當任何提及的表執(zhí)行delete,insert或update時檢測check條件。刪除斷言

DROPASSERTION<斷言名>Page45例子假定希望其凈資產(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)))

Page46例子聲明對一個給定電影公司，其所有電影的總長度不能超過10000分鐘。用斷言實現(xiàn)

CREATEASSERTIONSumLengthCHECK(10000>=ALL(SELECTSUM(length)FROMMoviesGroupBystudioname));基于元組的Check約束：

CHECK(10000>=ALL(SELECTSUM(length)FROMMoviesGroupBystudioname));

Page47約束的比較下面表格列出了基于屬性的檢查約束、基于元組的檢查約束和斷言之間的主要區(qū)別。約束類型聲明的位置動作的時間確保成立？基于屬性的CHECK屬性對關系插入元組或?qū)傩孕薷娜绻亲硬樵?，則不能確?；谠M的CHECK關系模式元素對關系插入元組或?qū)傩孕薷娜绻亲硬樵?，則不能確保斷言數(shù)據(jù)庫模式元素對任何提及的關系改變是Page48練習P.196/7.4.1/b)CreateAssertionCheck(NotExists(SelectmakerFromProductNaturalJoinPCWherespeed>All(SelectL2.speedFromProductP2,LaptopL2WhereP2.maker=makerANDP2.model=L2.model)));

Page49觸發(fā)器是什么？基于特定事件觸發(fā)的特定的約束檢驗。一個觸發(fā)器trigger是存儲在某個表中的一個命名的數(shù)據(jù)庫對象。當該表進行某種數(shù)據(jù)更新時，將自動觸發(fā)一組SQL語句的執(zhí)行。注意：各種商業(yè)數(shù)據(jù)庫可能使用不同語法。7.5觸發(fā)器Page50觸發(fā)器(trigger)觸發(fā)器與約束不同：僅當數(shù)據(jù)庫程序員聲明的事件發(fā)生時，觸發(fā)器被激活。所允許的事件種類通常是對某個特定關系的插入、刪除或修改。當觸發(fā)器被事件激活時，觸發(fā)器測試觸發(fā)條件。如果條件不成立，則相應該事件的觸發(fā)器不做任何事情。如果觸發(fā)器滿足聲明的條件，則與該觸發(fā)器相連的動作由DBMS執(zhí)行。Page51事件－條件－動作規(guī)則Event事件：數(shù)據(jù)更新指令：insert/delete/updateCondition條件：當事件發(fā)生后，檢查條件是否滿足：若不滿足，則不執(zhí)行動作。若滿足，則執(zhí)行一組動作之后狀態(tài)轉(zhuǎn)移。Action動作：一組SQL指令，通常是更新操作Old狀態(tài)New狀態(tài)Event/Condition[Action]Page52SQL中的觸發(fā)器特征動作可以在觸發(fā)事件之前或之后被執(zhí)行。在被觸發(fā)的事件中，動作既可指向被插入、刪除、修改元組的新值，也可以指向其舊值。更新事件可以被局限到某個特定的屬性或某一些屬性。條件由WHEN短語給出。僅僅當規(guī)則被觸發(fā)，并且觸發(fā)事件的發(fā)生使條件成立時，動作才被執(zhí)行。程序員可以選擇動作執(zhí)行的方式：一次只對一個更新元組(行級觸發(fā)器)

，或者一次針對在數(shù)據(jù)庫操作中被改變的所有元組(語句級觸發(fā)器)。Page5354示例7.131)CREATETRIGGERNetWorthTrigger2)AFTERUPDATEOFnetWorthONMovieExec3)REFERENCING4)OLDROWASOldTuple，5)NEWROWASNewTuple6)FOREACHROW7)WHEN(OldTWorth>NewTWorth)8)UPDATEMovieExec9)SETnetWorth=OldTWorthl0)WHEREcert#=NewTuple.cert#；觸發(fā)聲明指出觸發(fā)事件并告訴觸發(fā)器在觸發(fā)事件之前還是之后使用數(shù)據(jù)庫狀態(tài)允許觸發(fā)器的條件和動作引用正被修改的元組。本例該子句允許給在改變之前和之后的元組命名該觸發(fā)器是每修改一個元組執(zhí)行一次的方式——行級觸發(fā)器觸發(fā)條件動作部分55觸發(fā)器設計的選項1AFTER：條件測試和動作將在觸發(fā)事件之后的數(shù)據(jù)庫狀態(tài)上被執(zhí)行。BEFORE：WHEN條件將在觸發(fā)事件執(zhí)行之前的數(shù)據(jù)庫狀態(tài)上測試。如果條件是真，則在該狀態(tài)上執(zhí)行觸發(fā)器的動作最后執(zhí)行喚醒觸發(fā)器的事件，不管條件是否仍為真INSTEADOF：將在8.2.3節(jié)中討論，它與視圖的修改有關。56觸發(fā)器設計的選項2UPDATEOF<屬性(組)>短語是UPDATE事件的可選項若給出該選項，那么它定義的事件僅僅是OF保留字后列出的屬性(組)的修改。INSERTDELETEOF短語在INSERT或DELETE事件中不可使用，因為這兩個事件都是作用在整個元組上。57觸發(fā)器設計的選項3WHEN短語是可選項如果該短語缺省，則只要觸發(fā)器被喚醒，都要執(zhí)行動作。若有該短語，則僅當WHEN后的條件為真時執(zhí)行動作。58觸發(fā)器設計的選項4觸發(fā)器的動作部分可以由單個或任意多個SQL語句組成。這些語句需由BEGIN…END括起，并且語句之間用分號分隔。59觸發(fā)器設計的選項5FOREACHROW：行級觸發(fā)器行級觸發(fā)器觸發(fā)事件是修改舊元組：修改之前的元組，用OLDROWAS短語命名新元組：修改之后的元組，用NEWROWAS短語命名行級觸發(fā)器觸發(fā)事件是插入舊元組：OLDROWAS不可使用新元組：使用NEWROWAS短語命名被插入的元組行級觸發(fā)器觸發(fā)事件是刪除舊元組：OLDROWAS被用于命名被刪除的元組新元組：NEWROWAS不可使用60觸發(fā)器設計的選項6FOREACHSTATEMENT：語句級觸發(fā)器[默認]一旦有合適類型的語句被執(zhí)行，語句級觸發(fā)器就被執(zhí)行，而不問它實際上會影響多少元組(0個、1個或多個)例如：如果用SQL更新語句更新整個表語句級的修改觸發(fā)器將只執(zhí)行一次元組級觸發(fā)器將對要修改的元組一次一個地執(zhí)行。61觸發(fā)器設計的選項7語句級觸發(fā)器中不能直接引用舊的和新的元組任何觸發(fā)器(元組級或語句級)都可引用舊元組(刪除的元組或更新的元組的舊版本)的關系和新元組(插入元組或更新元組的新版本)的關系聲明方式OLDTABLEASOldStuffNEWTABLEASNewSuff62關于OldStuff和NewSuff的說明語句級觸發(fā)器觸發(fā)事件是修改NewStuff和OldStuff中分別是被更新元組的新版本和舊版本語句級觸發(fā)器觸發(fā)事件是刪除刪除元組是OldStuff，不能聲明NewStuff語句級觸發(fā)器觸發(fā)事件是插入插入的元組是NewStuff，不能聲明OldStuff63示例7.14假定要阻止電影制作人的平均凈資產(chǎn)值降到$500000對關系MovieExec(