數(shù)據(jù)庫(kù)原理與應(yīng)用課件第6章數(shù)據(jù)庫(kù)完整性

第六章數(shù)據(jù)庫(kù)完整性6.1數(shù)據(jù)庫(kù)完整性概述6.2數(shù)據(jù)庫(kù)完整性6.3完整性約束命名子句6.4觸發(fā)器6.5MySQL中的完整性約束與觸發(fā)器實(shí)踐6.6觸發(fā)器AnIntroductiontoDatabaseSystem*6.1數(shù)據(jù)庫(kù)完整性概述AnIntroductiontoDatabaseSystem*數(shù)據(jù)庫(kù)的完整性

數(shù)據(jù)的正確性和相容性數(shù)據(jù)的完整性和安全性是兩個(gè)不同概念數(shù)據(jù)的完整性防止數(shù)據(jù)庫(kù)中存在不符合語(yǔ)義的數(shù)據(jù)和不正確的數(shù)據(jù)防范對(duì)象：不合語(yǔ)義的、不正確的數(shù)據(jù)數(shù)據(jù)的安全性保護(hù)數(shù)據(jù)庫(kù)防止惡意的破壞和非法的存取防范對(duì)象：非法用戶和非法操作為維護(hù)數(shù)據(jù)庫(kù)的完整性，DBMS必須：

1.提供定義完整性約束條件的機(jī)制2.提供完整性檢查的方法3.違約處理AnIntroductiontoDatabaseSystem*6.2數(shù)據(jù)庫(kù)完整性定義和檢查*1.實(shí)體完整性規(guī)則實(shí)體完整性規(guī)則：主碼必須唯一且主碼的各個(gè)屬性都不能為空值。所謂空值，就是用于表示“不知道”、“沒(méi)意義”或“空白”的值，通常用NULL表示。例如，在學(xué)生選課關(guān)系中，如果學(xué)生還沒(méi)有考試，那么其成績(jī)就還不確定，則其成績(jī)可以用NULL表示，或讓其為一個(gè)空白。但是“(學(xué)號(hào),課號(hào))”是主鍵，所以“學(xué)號(hào)”和“課號(hào)”這兩個(gè)屬性都不能為空值。實(shí)體完整性給出了主鍵取值的約束條件。*2.參照完整性規(guī)則參照完整性規(guī)則：外碼或者取空值（要求外碼的每個(gè)屬性均為空值），或者等于被參照關(guān)系中的主碼的某個(gè)值。參照完整性也稱為引用完整性，參照完整性一般指多個(gè)實(shí)體或表之間的關(guān)聯(lián)關(guān)系?，F(xiàn)實(shí)世界中的實(shí)體之間往往存在著某種聯(lián)系，在關(guān)系模型中，實(shí)體以及實(shí)體之間的聯(lián)系都是用關(guān)系（表）來(lái)表示的，這樣就自然存在著關(guān)系（表）與關(guān)系（表）之間的引用關(guān)系。參照完整性用于描述實(shí)體之間的聯(lián)系。*3.用戶定義完整性規(guī)則用戶定義完整性規(guī)則：屬性的取值應(yīng)當(dāng)滿足用戶定義的約束條件。根據(jù)應(yīng)用環(huán)境的特殊要求，應(yīng)用系統(tǒng)中的關(guān)系往往還應(yīng)該滿足一些特殊的約束條件。用戶定義的完整性就是針對(duì)某一具體應(yīng)用領(lǐng)域定義的數(shù)據(jù)約束條件，它反映某一具體應(yīng)用涉及的數(shù)據(jù)必須滿足應(yīng)用語(yǔ)義的要求。6.2數(shù)據(jù)庫(kù)完整性定義和檢查*6.2.1定義實(shí)體完整性與實(shí)體完整性檢查6.2.2定義參照完整性及參照完整性檢查6.2.3定義用戶定義的完整性與用戶定義完整性檢查6.2.1定義實(shí)體完整性與實(shí)體完整性檢查AnIntroductiontoDatabaseSystem*1.定義實(shí)體完整性關(guān)系模型的實(shí)體完整性在CREATETABLE中用PRIMARYKEY定義。對(duì)單屬性構(gòu)成的碼有兩種說(shuō)明方法，一種是定義為列級(jí)約束條件，另一種是定義為表級(jí)約束條件。對(duì)多個(gè)屬性構(gòu)成的碼只有一種說(shuō)明方法，即定義為表級(jí)約束條件。*【例6-1】定義學(xué)生表中的碼。

CREATETABLEstudent(SnoCHAR(12)PRIMARYKEY,/*列級(jí)約束*/SnameCHAR(8),SexCHAR(2),SbirthDATE,AddrVARCHAR(50));或者CREATETABLEstudent(SnoCHAR(12)，SnameCHAR(8),SexCHAR(2),SbirthDATE,AddrVARCHAR(50)PRIMARYKEY(Sno)/*表級(jí)約束*/);【例6-2】定義學(xué)生選課成績(jī)表中的碼。CREATETABLEgrade(SnoCHAR(12),CnoCHAR(4),ScoreINT,PRIMARYKEY(Sno,Cno)/*表級(jí)約束*/）；*2.實(shí)體完整性檢查用PRIMARYKEY短語(yǔ)定義了關(guān)系的主碼后，每當(dāng)應(yīng)用程序需要對(duì)基本表插入一條記錄或?qū)χ鞔a列進(jìn)行更新操作時(shí)，DBMS將按照實(shí)體完整性規(guī)則自動(dòng)進(jìn)行檢查。主要包括∶（1）檢查主碼值是否唯一，如果不唯一則拒絕插入或修改；（2）檢查主碼的各個(gè)屬性是否為空，只要有一個(gè)為空就拒絕插入或修改。從而保證了實(shí)體完整性。6.2.2定義參照完整性與參照完整性檢查AnIntroductiontoDatabaseSystem*1.定義參照完整性關(guān)系模型的參照完整性在CREATETABLE中用FOREIGNKEY短語(yǔ)定義外碼列，用REFERENCES短語(yǔ)指明這些外碼與其它表的主碼的參照關(guān)系。*【例6-3】定義學(xué)生選課成績(jī)表中的參照完整性。

CREATETABLEstudent(SnoCHAR(12)PRIMARYKEY,SnameCHAR(8),SexCHAR(2),SbirthDATE,AddrVARCHAR(50))；CREATETABLEcourse(CnoCHAR(4)PRIMARYKEY,CpnoCHAR(4),CnameVARCHAR(50),CreditsTINYINT，);CREATETABLEgrade(SnoCHAR(12),CnoCHAR(4),ScoreINT,PRIMARYKEY(Sno,Cno),/*在表級(jí)定義實(shí)體完整性，定義“學(xué)生選課表”的主碼*/FOREIGNKEY(Sno)REFERENCESstudent(Sno),/*在表級(jí)定義參照完整性，定義“學(xué)生選課成績(jī)表”的外碼，“學(xué)生選課成績(jī)表”中“Sno”的取值是引用了“學(xué)生表”中“Sno”的取值*/FOREIGNKEY(Cno)REFERENCEScourse(Cno)/*在表級(jí)定義參照完整性，定義“學(xué)生選課成績(jī)表”的外碼，“學(xué)生選課成績(jī)表”中“Cno”的取值是引用了“課程表”中“Cno”的取值*/);AnIntroductiontoDatabaseSystem*2.參照完整性檢查參照完整性將兩個(gè)表中的相應(yīng)元組聯(lián)系起來(lái)，因此，對(duì)被參照表和參照表進(jìn)行增、刪、改操作時(shí)有可能破壞參照完整性，必須進(jìn)行檢查以保證這兩個(gè)表的相容性。（1）拒絕（NOACTION）執(zhí)行，不允許該操作執(zhí)行，該策略一般設(shè)置為默認(rèn)策略；（2）級(jí)聯(lián)（CASCADE）操作，當(dāng)刪除或修改被參照表（如“學(xué)生表”）的一個(gè)元組導(dǎo)致與參照表（如“學(xué)生選課成績(jī)表”）不一致時(shí)，刪除或修改參照表中的所有產(chǎn)生不一致的元組；（3）設(shè)置為空值，當(dāng)刪除或修改被參照表的一個(gè)元組時(shí)造成了不一致，則將參照表中的所有產(chǎn)生不一致元組的對(duì)應(yīng)屬性設(shè)置為空值。*【例6-4】定義學(xué)生選課成績(jī)表，如果存在外碼則說(shuō)明違約處理原則。CREATETABLEgrade(SnoCHAR(12)CnoCHAR(4)ScoreINT,PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESStudent(Sno)ONDELETECASCADE/*當(dāng)刪除“學(xué)生表”中的元組時(shí)，級(jí)聯(lián)刪除“學(xué)生選課成績(jī)表”中相應(yīng)的元組*/ONUPDATECASCADE,/*當(dāng)更新“學(xué)生表”中的“學(xué)號(hào)”屬性時(shí)，級(jí)聯(lián)更新“學(xué)生選課成績(jī)表”中相應(yīng)的元組*/FOREIGNKEY(Cno)REFERENCEScourse(Cno)ONDELETENOACTION/*當(dāng)刪除“課程表”中的元組造成與“學(xué)生選課成績(jī)表”不一致時(shí)，拒絕刪除*/ONUPDATECASCADE/*當(dāng)更新“課程表”中的“課號(hào)”屬性時(shí)，級(jí)聯(lián)更新“學(xué)生選課成績(jī)表”中相應(yīng)的元組*/);6.2.3定義用戶定義的完整性與用戶定義的完整性檢查AnIntroductiontoDatabaseSystem*1.定義用戶定義的完整性前面已經(jīng)提到用戶定義的完整性是針對(duì)某一具體應(yīng)用的數(shù)據(jù)必須滿足的語(yǔ)義要求。目前使用的DBMS基本都提供了定義和檢驗(yàn)這類完整性的機(jī)制，主要包括屬性上約束條件的定義和檢查、元組上約束條件的定義和檢查。2.屬性上約束條件的定義和檢查在CREATETABLE中定義屬性的同時(shí)，可以根據(jù)應(yīng)用要求定義屬性上的約束條件，如定義列值非空（NOTNULL）、列值唯一（UNIQUE）、檢查列值是否滿足某個(gè)條件（CHECK短語(yǔ)）等。*【例6-5】定義學(xué)生選課成績(jī)表，并要求學(xué)號(hào)、課號(hào)屬性不允許取空值，成績(jī)屬性的取值在0到100之間。CREATETABLEgrade(SnoCHAR(12)NOTNULL,CnoCnoCHAR(4)NOTNULL,ScoreINTCHECK(成績(jī)>=0AND成績(jī)<=100)，PRIMARYKEY(Sno,Cno),);【例6-6】定義學(xué)生表，要求“姓名”屬性取值唯一，“性別”屬性只允許取“男”或“女”。CREATETABLEstudent(SnoCHAR(12)PRIMARYKEY,SnameCHAR(8)UNIQUE,SexCHAR(2)CHECK(SexIN(‘男’,’女’)),SbirthDATE,AddrVARCHAR(50))；AnIntroductiontoDatabaseSystem*3.元組上約束條件的定義和檢查與屬性上約束條件的定義類似，在CREATETABLE語(yǔ)句中可以用CHECK短語(yǔ)定義元組上的約束條件，元組級(jí)的約束條件可以設(shè)置不同屬性之間的取值的相互約束條件。AnIntroductiontoDatabaseSystem*【例6-7】定義學(xué)生表時(shí)，要求女生姓名后加“同學(xué)”。CREATETABLEstudent(SnoCHAR(12)PRIMARYKEY,SnameCHAR(8)UNIQUE,SexCHAR(2)CHECK(SexIN(‘男’,’女’)),SbirthDATE,AddrVARCHAR(50),CHECK(Sex=’女’ANDSnameLIKE‘%同學(xué)’))當(dāng)往表中插入元組或修改屬性的值時(shí)，數(shù)據(jù)庫(kù)管理系統(tǒng)將檢查元組上的約束條件是否被滿足，如果不滿足則操作被拒絕執(zhí)行。6.3完整性約束命名子句AnIntroductiontoDatabaseSystem*

上節(jié)提到的完整性約束包括主鍵約束、唯一約束、檢查約束、外鍵約束等，都在CREATETABLE子句中定義。此時(shí)DBMS會(huì)自動(dòng)為定義的約束生成一個(gè)隨機(jī)的名稱，如果要為這些約束指定名稱，則需要使用CONSTRAINT關(guān)鍵字。AnIntroductiontoDatabaseSystem*【例6-8】使用CONSTRAINT關(guān)鍵字，定義學(xué)生表中的主鍵約束。CREATETABLEstudent(SnoCHAR(12),SnameCHAR(8),SexCHAR(2),SbirthDATE,AddrVARCHAR(50),CONSTRAINTpk_noPRIMARYKEY(Sno)/*使用約束命名子句定義主鍵約束*/);AnIntroductiontoDatabaseSystem*【例6-9】使用CONSTRAINT關(guān)鍵字，定義學(xué)生選課成績(jī)表中的外鍵約束。CREATETABLEgrade(SnoCHAR(12),CnoCHAR(4),ScoreINT,CONSTRAINTpk_idPRIMARYKEY(Sno,Cno),CONSTRAINTfk_idFOREIGNKEY(Sno)REFERENCESstudent(Sno)ONDELETECASCADE/*使用約束命名子句定義外鍵約束*/);*【例6-10】使用CONSTRAINT關(guān)鍵字，定義學(xué)生表中“姓名”屬性值的唯一約束，和“性別”屬性值的檢查約束。CREATETABLEstudent(SnoCHAR(12),SnameCHAR(8),SexCHAR(2),SbirthDATE,AddrVARCHAR(50),CONSTRAINTpk_noPRIMARYKEY(Sno),CONSTRAINTuk_nameUNIQUE(Sname),/*使用約束命名子句定義屬性值唯一約束*/CONSTRAINTck_sexCHECK(SexIN(‘男’,’女’))/*使用約束命名子句定義屬性值檢查約束*/);6.4觸發(fā)器6.4.1定義觸發(fā)器

6.4.2激活觸發(fā)器

6.4.3刪除觸發(fā)器

AnIntroductiontoDatabaseSystem*觸發(fā)器觸發(fā)器（trigger）是用戶定義在關(guān)系表上的一類由事件驅(qū)動(dòng)的特殊過(guò)程。觸發(fā)器創(chuàng)建之后將被保存在數(shù)據(jù)庫(kù)服務(wù)器中。當(dāng)用戶對(duì)表進(jìn)行增、刪、改操作時(shí)，服務(wù)器自動(dòng)激活相應(yīng)的觸發(fā)器，在DBMS核心層進(jìn)行集中的完整性控制。觸發(fā)器分為AFTER觸發(fā)器和BEFORE觸發(fā)器兩種。AFTER觸發(fā)器是在觸發(fā)它的操作（如INSERT、UPDATE和DELETE）之后觸發(fā)當(dāng)前所創(chuàng)建的觸發(fā)器，如果觸發(fā)的操作失敗，則此觸發(fā)器不會(huì)執(zhí)行。BEFORE觸發(fā)器是在觸發(fā)它的操作（如INSERT、UPDATE和DELETE）之前觸發(fā)當(dāng)前所創(chuàng)建的觸發(fā)器，然后執(zhí)行觸發(fā)的操作。觸發(fā)器先于觸發(fā)的操作執(zhí)行，用于對(duì)觸發(fā)的操作數(shù)據(jù)進(jìn)行前期判斷，按情況修改數(shù)據(jù)。AnIntroductiontoDatabaseSystem*6.4.1定義觸發(fā)器創(chuàng)建觸發(fā)器的語(yǔ)法如下:

CREATETRIGGER<觸發(fā)器名>{BEFORE|AFTER}<INSERT|DELETE|UPDATE>ON<表名>FOREACH{ROW|STATEMENT}<觸發(fā)體>AnIntroductiontoDatabaseSystem*定義觸發(fā)器的語(yǔ)法說(shuō)明:（1）只有創(chuàng)建表的用戶才可以在表上創(chuàng)建觸發(fā)器，并且一個(gè)表上只能創(chuàng)建一定數(shù)量的觸發(fā)器。觸發(fā)器的具體數(shù)量由具體的數(shù)據(jù)庫(kù)管理系統(tǒng)在設(shè)計(jì)時(shí)確定。（2）觸發(fā)器名可以包含模式名，也可以不包含模式名。同一模式下，觸發(fā)器名必須是唯一的，并且觸發(fā)器名和表名必須在同一模式下。（3）觸發(fā)器只能定義在基本表上，不能定義在視圖上。當(dāng)基本表的數(shù)據(jù)發(fā)生變化時(shí)，將激活定義在該表上相應(yīng)觸發(fā)事件的觸發(fā)器。（4）觸發(fā)事件可以是INSERT、DELETE或UPDATE，也可以是這幾個(gè)事件的組合AFTER表示在觸發(fā)事件的操作執(zhí)行之后激活觸發(fā)器；BEFORE表示在觸發(fā)事件的操作執(zhí)行之前激活觸發(fā)器。（5）觸發(fā)器按照所觸發(fā)動(dòng)作的間隔尺寸可以分為行級(jí)觸發(fā)器（FOREACHROW）和語(yǔ)句級(jí)觸發(fā)器（FOREACHSTATEMENT）。（6）觸發(fā)體既可以是一個(gè)匿名PLSQL過(guò)程塊，也可以是對(duì)已創(chuàng)建存儲(chǔ)過(guò)程的調(diào)用，還可以是一個(gè)SQL語(yǔ)句。如果觸發(fā)體執(zhí)行失敗，激活觸發(fā)器的事件就會(huì)終止執(zhí)行，定義觸發(fā)器的基本表或觸發(fā)器可能影響的其他對(duì)象不發(fā)生任何變化。AnIntroductiontoDatabaseSystem*【例6-11】創(chuàng)建觸發(fā)器delete_trigger，該觸發(fā)器將記錄哪些用戶刪除了“學(xué)生表”中的數(shù)據(jù)，以及刪除的時(shí)間。首先創(chuàng)建“日志信息表”rec_log，用于存儲(chǔ)用戶對(duì)表的操作；CREATETABLErec_log（uservarchar(30),dateDATE,);然后在“學(xué)生表”上創(chuàng)建觸發(fā)器，它會(huì)在用戶對(duì)“學(xué)生表”使用DELETE操作時(shí)觸發(fā)，并向rec_log表中添加操作的用戶名和日期。CREATETRIGGERdelete_triggerAFTERDELETEONstudentFOREACHROWINSERTINTOrec_log(user,date)VALUES(USER(),SYSDATE());*6.4.2激活觸發(fā)器觸發(fā)器的執(zhí)行，是由觸發(fā)事件激活的，并由數(shù)據(jù)庫(kù)服務(wù)器自動(dòng)執(zhí)行一個(gè)數(shù)據(jù)表上可能定義了多個(gè)觸發(fā)器同一個(gè)表上的多個(gè)觸發(fā)器激活時(shí)遵循如下的執(zhí)行順序：（1）執(zhí)行該表上的BEFORE觸發(fā)器；（2）激活觸發(fā)器的SQL語(yǔ)句；（3）執(zhí)行該表上的AFTER觸發(fā)器。*為了測(cè)試【例6-11】創(chuàng)建的觸發(fā)器是否正常運(yùn)行，在“學(xué)生表”中刪除學(xué)號(hào)為“20418010005”的學(xué)生的記錄，并查詢“日志信息表”rec_log。DELETEFROMstudentWHERESno＝20418010005SELECT?FROMrec_log+-----------------+-----------------+|user|date|+----------------+-----------------+|root@localhost|2020-11-16|+-------------------+---------------+在MySQL觸發(fā)器的觸發(fā)體中，在列名前加上“OLD．”限定詞表示變化前的值，在列名前加上“NEW．”限定詞表示變化后的值。這樣SQL語(yǔ)句就可以訪問(wèn)受觸發(fā)語(yǔ)句影響的每行的列值，例如可以通過(guò)觸發(fā)器實(shí)現(xiàn)級(jí)聯(lián)更新。*【例6-12】當(dāng)修改“學(xué)生表”中的“學(xué)號(hào)”屬性值之后（AFTER）級(jí)聯(lián)地、自動(dòng)地修改“學(xué)生選課成績(jī)表”中相應(yīng)的“學(xué)號(hào)”屬性值。CREATETRIGGERtr_student_gradeAFTERUPDATEONstudentFOREACHROWUPDATEgradeSETSno＝NEW.SnoWHERESno＝OLD.sno；在創(chuàng)建了該觸發(fā)器之后，如果修改“學(xué)生表”中的“學(xué)號(hào)”屬性的值，就會(huì)級(jí)聯(lián)修改“學(xué)生選課成績(jī)表”中相應(yīng)的“學(xué)號(hào)”屬性的值*6.4.3刪除觸發(fā)器刪除觸發(fā)器的SQL語(yǔ)法：

DROPTRIGGER<觸發(fā)器名>ON<表名>;觸發(fā)器必須是一個(gè)已經(jīng)創(chuàng)建的觸發(fā)器，并且只能由具有相應(yīng)權(quán)限的用戶刪除?！纠?-13】刪除【例6-12】建立的tr_student_grade觸發(fā)器DROPTRIGGERtr_student_grade;6.5MySQL中的完整性約束與觸發(fā)器實(shí)踐6.5.1MySQL完整性約束實(shí)踐

6.5.2MySQL觸發(fā)器實(shí)踐

AnIntroductiontoDatabaseSystem*6.5.1MySQL完整性約束實(shí)踐1.主鍵約束（主碼約束）主鍵約束是使用最頻繁的約束。在設(shè)計(jì)數(shù)據(jù)表時(shí)，一般情況下，都會(huì)要求表中設(shè)置一個(gè)主鍵，主鍵是表的一個(gè)特殊字段，該字段能唯一標(biāo)識(shí)該表中的每條信息。*（1）設(shè)置單字段主鍵【例6-14】在test_db數(shù)據(jù)庫(kù)中創(chuàng)建teacher1數(shù)據(jù)表，其主鍵為id。MySQL語(yǔ)句和運(yùn)行結(jié)果如下：mysql>CREATETABLEteacher1->(->idINT(11)PRIMARYKEY,->nameVARCHAR(25),->deptIdINT(11),->salaryFLOAT->);QueryOK,0rowsaffected(0.39sec)*mysql>DESCteacher1;+--------+-------------+------+-----+----------+-------+|Field|Type|Null|Key|Default|Extra|+--------+----------------+--------+------+---------+-------+|id|int(11)|NO|PRI|NULL|||name|varchar(25)|YES||NULL|||deptId|int(11)|YES||NULL|||salary|float|YES||NULL||+--------+---------------+--------+------+----------+--------+4rowsinset(0.14sec)（2）在創(chuàng)建表時(shí)設(shè)置聯(lián)合主鍵【例6-15】創(chuàng)建數(shù)據(jù)表teacher2，假設(shè)表中沒(méi)有主鍵id，為了唯一確定一個(gè)元組，可以把name、deptId聯(lián)合起來(lái)作為主鍵。MySQL語(yǔ)句和運(yùn)行結(jié)果如下：mysql>CREATETABLEteacher2->(->nameVARCHAR(25),->deptIdINT(11),->salaryFLOAT,->PRIMARYKEY(name,deptId)->);QueryOK,0rowsaffected(0.39sec)*mysql>DESCteacher2;+--------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+--------+-------------+------+-----+---------+-------+|name|varchar(25)|NO|PRI|NULL|||deptId|int(11)|NO|PRI|NULL|||salary|float|YES||NULL||+--------+-------------+------+-----+---------+-------+3rowsinset(0.14sec)（3）在修改表時(shí)添加主鍵約束【例6-16】創(chuàng)建并查看teacher3數(shù)據(jù)表的表結(jié)構(gòu)。MySQL語(yǔ)句和運(yùn)行結(jié)果如下：mysql>CREATETABLEteacher3->(->idINT(11),->nameVARCHAR(25),->deptIdINT(11),->salaryFLOAT->);*mysql>DESCteacher3;+--------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+--------+-------------+------+-----+---------+-------+|id|int(11)|NO||NULL|||name|varchar(25)|YES||NULL|||deptId|int(11)|YES||NULL|||salary|float|YES||NULL||+--------+-------------+------+-----+---------+-------+4rowsinset(0.14sec)【例6-17】修改數(shù)據(jù)表teacher3，將字段id設(shè)置為主鍵。MySQL語(yǔ)句和運(yùn)行結(jié)果如下：mysql>ALTERTABLEteacher3->ADDPRIMARYKEY(id);QueryOK,0rowsaffected(0.94sec)Records:0Duplicates:0Warnings:0*mysql>DESCteacher3;+--------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+--------+-------------+------+-----+---------+-------+|id|int(11)|NO|PRI|NULL|||name|varchar(25)|YES||NULL|||deptId|int(11)|YES||NULL|||salary|float|YES||NULL||+--------+-------------+------+-----+---------+-------+4rowsinset(0.12sec)（4）刪除主鍵約束【例6-18】刪除teacher3表中的主鍵約束。MySQL語(yǔ)句和運(yùn)行結(jié)果如下：mysql>ALTERTABLEteacher3->DROPPRIMARYKEY;QueryOK,0rowsaffected(0.94sec)Records:0Duplicates:0Warnings:0*2.外鍵約束（外碼約束）外鍵約束經(jīng)常和主鍵約束一起使用，用來(lái)確保數(shù)據(jù)的一致性。*（1）在創(chuàng)建表時(shí)設(shè)置外鍵約束【例6-19】為了展現(xiàn)表與表之間的外鍵關(guān)系，在test_db數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)部門表teacher_dept1，mysql>CREATETABLEteacher_dept1->(->idINT(11)PRIMARYKEY,->nameVARCHAR(22)NOTNULL,->locationVARCHAR(50)->);QueryOK,0rowsaffected(0.37sec)*創(chuàng)建數(shù)據(jù)表teacher4，并在表teacher4上創(chuàng)建外鍵約束，讓其屬性deptId作為外鍵關(guān)聯(lián)到表teacher_dept1的主鍵id。MySQL語(yǔ)句和運(yùn)行結(jié)果如下：mysql>CREATETABLEteacher4->(->idINT(11)PRIMARYKEY,->nameVARCHAR(25),->deptIdINT(11),->salaryFLOAT,->CONSTRAINTfk_teacher_dept1->FOREIGNKEY(deptId)REFERENCESteacher_dept1(id)->);QueryOK,0rowsaffected(0.37sec)*mysql>DESCteacher4;+--------+---------------+--------+--------+---------+-------+|Field|Type|Null|Key|Default|Extra|+--------+---------------+--------+--------+---------+--------+|id|int(11)|NO|PRI|NULL|||name|varchar(25)|YES||NULL|||deptId|int(11)|YES|MUL|NULL|||salary|float|YES||NULL||+--------+-------------+----------+--------+---------+---------+4rowsinset(1.33sec)（2）在修改表時(shí)添加外鍵約束【例6-20】修改數(shù)據(jù)表teacher2，將字段deptId設(shè)置為外鍵，與數(shù)據(jù)表teacher_dept1的主鍵id進(jìn)行關(guān)聯(lián)。MySQL語(yǔ)句和運(yùn)行結(jié)果如下：mysql>ALTERTABLEteacher2->ADDCONSTRAINTfk_teacher_dept2->FOREIGNKEY(deptId)->REFERENCESteacher_dept1(id);QueryOK,0rowsaffected(1.38sec)Records:0Duplicates:0Warnings:0*（3）刪除外鍵約束【例6-21】刪除數(shù)據(jù)表teacher2中的外鍵約束fk_teacher_dept2。MySQL語(yǔ)句和運(yùn)行結(jié)果如下:mysql>ALTERTABLEteacher2->DROPFOREIGNKEYfk_teacher_dept2;QueryOK,0rowsaffected(0.19sec)Records:0Duplicates:0Warnings:0*3.唯一約束唯一約束與主鍵約束有一個(gè)相似的地方，就是它們都能夠確保列的唯一性。與主鍵約束不同的是，唯一約束在一個(gè)表中可以有多個(gè)，并且設(shè)置唯一約束的列是允許有空值。例如，在“學(xué)生表”中，要避免表中的學(xué)生重名，就可以把“姓名”列設(shè)置為唯一約束。*（1）在創(chuàng)建表時(shí)設(shè)置唯一約束【例6-22】創(chuàng)建數(shù)據(jù)表teacher_dept2，指定部門的名稱唯一。MySQL語(yǔ)句和運(yùn)行結(jié)果如下：mysql>CREATETABLEteacher_dept2->(->idINT(11)PRIMARYKEY,->nameVARCHAR(22)UNIQUE,->locationVARCHAR(50)->);QueryOK,0rowsaffected(0.37sec)*mysql>DESCteacher_dept2;+----------+-------------+--------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+----------+--------------+-------+------+---------+-------+|id|int(11)|NO|PRI|NULL|||name|varchar(22)|YES|UNI|NULL|||location|varchar(50)|YES||NULL||+----------+--------------+------+------+---------+---------+3rowsinset(0.08sec)（2）在修改表時(shí)添加唯一約束【例6-23】修改數(shù)據(jù)表teacher_dept1指定部門的名稱唯一。MySQL語(yǔ)句和運(yùn)行結(jié)果如下：mysql>ALTERTABLEteacher_dept1->ADDCONSTRAINTunique_nameUNIQUE(name);QueryOK,0rowsaffected(0.63sec)Records:0Duplicates:0Warnings:0*mysql>DESCteacher_dept1;+----------+-------------+------+-----+---------+-----------+|Field|Type|Null|Key|Default|Extra|+----------+--------------+-------+-----+-----------+--------+|id|int(11)|NO|PRI|NULL|||name|varchar(22)|NO|UNI|NULL|||location|varchar(50)|YES||NULL||+----------+--------------+------+------+----------+---------+3rowsinset(0.00sec)（3）刪除唯一約束【例6-24】刪除數(shù)據(jù)表teacher_dept1中的唯一約束unique_name。MySQL語(yǔ)句和運(yùn)行結(jié)果如下：mysql>ALTERTABLEteacher_dept1->DROPINDEXunique_name;QueryOK,0rowsaffected(0.20sec)Records:0Duplicates:0Warnings:0*mysql>DESCteacher_dept1;+----------+----------------+---------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+---------+-----------------+---------+-----+---------+-------+|id|int(11)|NO|PRI|NULL|||name|varchar(22)|NO||NULL|||location|varchar(50)|YES||NULL||+----------+-----------------+--------+-----+------------+------+3rowsinset(0.00sec)4.檢查約束檢查約束是用來(lái)檢查數(shù)據(jù)表中，字段值是否有效的一個(gè)手段。例如，“課程表”中的學(xué)分字段是沒(méi)有負(fù)數(shù)的，并且數(shù)值也是有限制的。在設(shè)置字段的檢查約束時(shí)要根據(jù)實(shí)際情況進(jìn)行設(shè)置，這樣能夠減少無(wú)效數(shù)據(jù)的輸入。*（1）在創(chuàng)建表時(shí)設(shè)置檢查約束【例6-25】在test_db數(shù)據(jù)庫(kù)中創(chuàng)建teacher5數(shù)據(jù)表，要求salary字段值大于0且小于10000。MySQL語(yǔ)句和運(yùn)行結(jié)果如下：mysql>CREATETABLEteacher5->(->idINT(11)PRIMARYKEY,->nameVARCHAR(25),->deptIdINT(11),->salaryFLOAT,->CHECK(salary>0ANDsalary<100),->FOREIGNKEY(deptId)REFERENCESteacher_dept1(id)->);QueryOK,0rowsaffected(0.39sec)*（2）在修改表時(shí)添加檢查約束

【例6-26】修改teacher5數(shù)據(jù)表，要求id字段值大于0。MySQL語(yǔ)句和運(yùn)行結(jié)果如下：mysql>ALTERTABLEteacher5->ADDCONSTRAINTcheck_id->CHECK(id>0);QueryOK,0rowsaffected(0.19sec)Records:0Duplicates:0Warnings:0*（3）刪除檢查約束

【例6-27】刪除teacher5表中的check_id檢查約束。MySQL語(yǔ)句和運(yùn)行結(jié)果如下：mysql>ALTERTABLEteacher5->DROPCONSTRAINTcheck_id;QueryOK,0rowsaffected(0.19sec)Records:0Duplicates:0Warnings:0*5.非空約束非空約束用來(lái)約束表中的字段不能為空。例如，在“學(xué)生表”中，規(guī)定“姓名”列非空，如果不添加學(xué)生姓名，那么這條記錄無(wú)用。*（1）在創(chuàng)建表時(shí)設(shè)置非空約束【例6-28】創(chuàng)建數(shù)據(jù)表teacher_dept3，指定部門名稱不能為空。MySQL語(yǔ)句和運(yùn)行結(jié)果如下：mysql>CREATETABLEteacher_dept3->(->idINT(11)PRIMARYKEY,->nameVARCHAR(22)NOTNULL,->locationVARCHAR(50)->);QueryOK,0rowsaffected(0.37sec)*mysql>DESCteacher_dept3;+----------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+----------+-------------+------+-----+---------+-------+|id|int(11)|NO|PRI|NULL|||name|varchar(22)|NO||NULL|||location|varchar(50)|YES||NULL||+----------+-------------+------+-----+---------+-------+3rowsinset(0.06sec)（2）在修改表時(shí)添加非空約束【例6-29】修改數(shù)據(jù)表teacher_dept3，指定部門位置不能為空。MySQL語(yǔ)句和運(yùn)行結(jié)果如下：mysql>ALTERTABLEteacher_dept3->CHANGECOLUMNlocation->locationVARCHAR(50)NOTNULL;QueryOK,0rowsaffected(0.15sec)Records:0Duplicates:0Warnings:0*mysql>DESCteacher_dept3;+----------+-------------+------+-----+----------+-------+|Field|Type|Null|Key|Default|Extra|+----------+-------------+--------+-----+---------+-------+|id|int(11)|NO|PRI|NULL|||name|varchar(22)|NO||NULL|||location|varchar(50)|NO||NULL||+----------+--------------+--------+------+---------+-----+3rowsinset(0.00sec)（3）刪除非空約束【例6-30】修改數(shù)據(jù)表teacher_dept3，將部門位置的非空約束刪除。MySQL語(yǔ)句和運(yùn)行結(jié)果如下：。mysql>ALTERTABLEteacher_dept3->CHANGECOLUMNlocation->locationVARCHAR(50)NULL;QueryOK,0rowsaffected(0.15sec)Records:0Duplicates:0Warnings:0*mysql>DESCteacher_dept3;+----------+-------------+------+-----+----------+-------+|Field|Type|Null|Key|Default|Extra|+----------+-------------+------+-----+----------+-------+|id|int(11)|NO|PRI|NULL|||name|varchar(22)|NO||NULL|||location|varchar(50)|YES||NULL||+----------+-------------+------+------+---------+--------+3rowsinset(0.00sec)6.默認(rèn)值約束默認(rèn)值約束用來(lái)約束當(dāng)數(shù)據(jù)表中某個(gè)字段不輸入值時(shí)，自動(dòng)為其添加一個(gè)已經(jīng)設(shè)置好的值。例如，在插入學(xué)生信息時(shí)，如果不輸入學(xué)生的性別，那么會(huì)默認(rèn)設(shè)置一個(gè)性別或者輸入一個(gè)“未知”。默認(rèn)值約束通常用在已經(jīng)設(shè)置了非空約束的列，這樣能夠防止數(shù)據(jù)表在錄入數(shù)據(jù)時(shí)出現(xiàn)錯(cuò)誤。*（1）在創(chuàng)建表時(shí)設(shè)置默認(rèn)值約束【例6-31】創(chuàng)建數(shù)據(jù)表teacher_dept4，指定部門位置默認(rèn)為Xian。MySQL語(yǔ)句和運(yùn)行結(jié)果如下：mysql>CREATETABLEteacher_dept4->(->idINT(11)PRIMARYKEY,->nameVARCHAR(22),->locationVARCHAR(50)DEFAULT'Xian'->);QueryOK,0rowsaffected(0.37sec)*mysql>DESCteacher_dept4;+----------+-------------+----------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+----------+--------------+---------+-----+---------+-------+|id|int(11)|NO|PRI|NULL|||name|varchar(22)|YES||NULL|||location|varchar(50)|YES||Xian||+----------+--------------+--------+-----+---------+---------+3rowsinset(0.06sec)（2）在修改表時(shí)添加默認(rèn)值約束【例6-32】修改數(shù)據(jù)表teacher_dept4，將部門位置的默認(rèn)值修改為Shanghai。MySQL語(yǔ)句和運(yùn)行結(jié)果如下：mysql>ALTERTABLEteacher_dept4->CHANGECOLUMNlocation->locationVARCHAR(50)DEFAULT'Shanghai';QueryOK,0rowsaffected(0.15sec)Records:0Duplicates:0Warnings:0*mysql>DESCteacher_dept4;+----------+-------------+------+-----+----------+-------+|Field|Type|Null|Key|D