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

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

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

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

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

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

上節(jié)提到的完整性約束包括主鍵約束、唯一約束、檢查約束、外鍵約束等，都在CREATETABLE子句中定義。此時DBMS會自動為定義的約束生成一個隨機的名稱，如果要為這些約束指定名稱，則需要使用CONSTRAINT關鍵字。AnIntroductiontoDatabaseSystem*【例6-8】使用CONSTRAINT關鍵字，定義學生表中的主鍵約束。CREATETABLEstudent(SnoCHAR(12),SnameCHAR(8),SexCHAR(2),SbirthDATE,AddrVARCHAR(50),CONSTRAINTpk_noPRIMARYKEY(Sno)/*使用約束命名子句定義主鍵約束*/);AnIntroductiontoDatabaseSystem*【例6-9】使用CONSTRAINT關鍵字，定義學生選課成績表中的外鍵約束。CREATETABLEgrade(SnoCHAR(12),CnoCHAR(4),ScoreINT,CONSTRAINTpk_idPRIMARYKEY(Sno,Cno),CONSTRAINTfk_idFOREIGNKEY(Sno)REFERENCESstudent(Sno)ONDELETECASCADE/*使用約束命名子句定義外鍵約束*/);*【例6-10】使用CONSTRAINT關鍵字，定義學生表中“姓名”屬性值的唯一約束，和“性別”屬性值的檢查約束。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）是用戶定義在關系表上的一類由事件驅(qū)動的特殊過程。觸發(fā)器創(chuàng)建之后將被保存在數(shù)據(jù)庫服務器中。當用戶對表進行增、刪、改操作時，服務器自動激活相應的觸發(fā)器，在DBMS核心層進行集中的完整性控制。觸發(fā)器分為AFTER觸發(fā)器和BEFORE觸發(fā)器兩種。AFTER觸發(fā)器是在觸發(fā)它的操作（如INSERT、UPDATE和DELETE）之后觸發(fā)當前所創(chuàng)建的觸發(fā)器，如果觸發(fā)的操作失敗，則此觸發(fā)器不會執(zhí)行。BEFORE觸發(fā)器是在觸發(fā)它的操作（如INSERT、UPDATE和DELETE）之前觸發(fā)當前所創(chuàng)建的觸發(fā)器，然后執(zhí)行觸發(fā)的操作。觸發(fā)器先于觸發(fā)的操作執(zhí)行，用于對觸發(fā)的操作數(shù)據(jù)進行前期判斷，按情況修改數(shù)據(jù)。AnIntroductiontoDatabaseSystem*6.4.1定義觸發(fā)器創(chuàng)建觸發(fā)器的語法如下:

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

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

6.5.2MySQL觸發(fā)器實踐

AnIntroductiontoDatabaseSystem*6.5.1MySQL完整性約束實踐1.主鍵約束（主碼約束）主鍵約束是使用最頻繁的約束。在設計數(shù)據(jù)表時，一般情況下，都會要求表中設置一個主鍵，主鍵是表的一個特殊字段，該字段能唯一標識該表中的每條信息。*（1）設置單字段主鍵【例6-14】在test_db數(shù)據(jù)庫中創(chuàng)建teacher1數(shù)據(jù)表，其主鍵為id。MySQL語句和運行結(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)建表時設置聯(lián)合主鍵【例6-15】創(chuàng)建數(shù)據(jù)表teacher2，假設表中沒有主鍵id，為了唯一確定一個元組，可以把name、deptId聯(lián)合起來作為主鍵。MySQL語句和運行結(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）在修改表時添加主鍵約束【例6-16】創(chuàng)建并查看teacher3數(shù)據(jù)表的表結(jié)構(gòu)。MySQL語句和運行結(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設置為主鍵。MySQL語句和運行結(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語句和運行結(jié)果如下：mysql>ALTERTABLEteacher3->DROPPRIMARYKEY;QueryOK,0rowsaffected(0.94sec)Records:0Duplicates:0Warnings:0*2.外鍵約束（外碼約束）外鍵約束經(jīng)常和主鍵約束一起使用，用來確保數(shù)據(jù)的一致性。*（1）在創(chuàng)建表時設置外鍵約束【例6-19】為了展現(xiàn)表與表之間的外鍵關系，在test_db數(shù)據(jù)庫中創(chuàng)建一個部門表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作為外鍵關聯(lián)到表teacher_dept1的主鍵id。MySQL語句和運行結(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）在修改表時添加外鍵約束【例6-20】修改數(shù)據(jù)表teacher2，將字段deptId設置為外鍵，與數(shù)據(jù)表teacher_dept1的主鍵id進行關聯(lián)。MySQL語句和運行結(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語句和運行結(jié)果如下:mysql>ALTERTABLEteacher2->DROPFOREIGNKEYfk_teacher_dept2;QueryOK,0rowsaffected(0.19sec)Records:0Duplicates:0Warnings:0*3.唯一約束唯一約束與主鍵約束有一個相似的地方，就是它們都能夠確保列的唯一性。與主鍵約束不同的是，唯一約束在一個表中可以有多個，并且設置唯一約束的列是允許有空值。例如，在“學生表”中，要避免表中的學生重名，就可以把“姓名”列設置為唯一約束。*（1）在創(chuàng)建表時設置唯一約束【例6-22】創(chuàng)建數(shù)據(jù)表teacher_dept2，指定部門的名稱唯一。MySQL語句和運行結(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）在修改表時添加唯一約束【例6-23】修改數(shù)據(jù)表teacher_dept1指定部門的名稱唯一。MySQL語句和運行結(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語句和運行結(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.檢查約束檢查約束是用來檢查數(shù)據(jù)表中，字段值是否有效的一個手段。例如，“課程表”中的學分字段是沒有負數(shù)的，并且數(shù)值也是有限制的。在設置字段的檢查約束時要根據(jù)實際情況進行設置，這樣能夠減少無效數(shù)據(jù)的輸入。*（1）在創(chuàng)建表時設置檢查約束【例6-25】在test_db數(shù)據(jù)庫中創(chuàng)建teacher5數(shù)據(jù)表，要求salary字段值大于0且小于10000。MySQL語句和運行結(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）在修改表時添加檢查約束

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

【例6-27】刪除teacher5表中的check_id檢查約束。MySQL語句和運行結(jié)果如下：mysql>ALTERTABLEteacher5->DROPCONSTRAINTcheck_id;QueryOK,0rowsaffected(0.19sec)Records:0Duplicates:0Warnings:0*5.非空約束非空約束用來約束表中的字段不能為空。例如，在“學生表”中，規(guī)定“姓名”列非空，如果不添加學生姓名，那么這條記錄無用。*（1）在創(chuàng)建表時設置非空約束【例6-28】創(chuàng)建數(shù)據(jù)表teacher_dept3，指定部門名稱不能為空。MySQL語句和運行結(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）在修改表時添加非空約束【例6-29】修改數(shù)據(jù)表teacher_dept3，指定部門位置不能為空。MySQL語句和運行結(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語句和運行結(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.默認值約束默認值約束用來約束當數(shù)據(jù)表中某個字段不輸入值時，自動為其添加一個已經(jīng)設置好的值。例如，在插入學生信息時，如果不輸入學生的性別，那么會默認設置一個性別或者輸入一個“未知”。默認值約束通常用在已經(jīng)設置了非空約束的列，這樣能夠防止數(shù)據(jù)表在錄入數(shù)據(jù)時出現(xiàn)錯誤。*（1）在創(chuàng)建表時設置默認值約束【例6-31】創(chuàng)建數(shù)據(jù)表teacher_dept4，指定部門位置默認為Xian。MySQL語句和運行結(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）在修改表時添加默認值約束【例6-32】修改數(shù)據(jù)表teacher_dept4，將部門位置的默認值修改為Shanghai。MySQL語句和運行結(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