第11講數(shù)據(jù)完整性

2023/2/31回顧索引索引的作用是提高查詢速度，但索引會降低更新速度。聚集索引與非聚集索引，唯一索引與非唯一索引，單列索引與復(fù)合索引，主鍵索引。視圖視圖是一個虛表，只存儲查詢定義，不存儲數(shù)據(jù)。在視圖上可以進行增、刪、改、查。增刪改等操作有一定限制。數(shù)據(jù)庫原理與應(yīng)用第11講數(shù)據(jù)完整性2023/2/33提綱重點分析數(shù)據(jù)完整性要求使用約束來強制完整性難點：外鍵約束與檢查約束1.數(shù)據(jù)完整性概述關(guān)鍵詞：實體完整、參照完整、自定義完整.2023/2/35數(shù)據(jù)完整性有3種類型實體完整性(EntityIntegrity)、參照完整性(ReferentialIntegrity)、用戶定義的完整性(User-definedIntegrity)又稱為域完整性。知識回顧關(guān)系的數(shù)據(jù)完整性3個規(guī)則實體完整性：通過主關(guān)鍵字約束和惟一約束實現(xiàn)參考完整性：通過外鍵約束來實現(xiàn)域完整性：通過有效性檢查來實現(xiàn)2023/2/37在SQLServer中保障數(shù)據(jù)完整性的辦法規(guī)則(Rule)默認(rèn)(Default)-------略約束(Constraint)-----重點學(xué)習(xí)觸發(fā)器(Trigger)------單獨的章節(jié)介紹2023/2/38規(guī)則(Rule)規(guī)則(Rule)只能通過T_sql語句建，不能使用管理平臺工具建。規(guī)則是單獨存儲的獨立的數(shù)據(jù)庫對象，可以多次被綁定。語法如下：CREATERULE語句用于在當(dāng)前數(shù)據(jù)庫中創(chuàng)建規(guī)則，其語法格式如下：

CREATE

RULE

rule_name

AS

condition_expression使用sp_helptext

系統(tǒng)存儲過程可以查看規(guī)則的文本信息。sp_helptext

（規(guī)則/黙認(rèn)值/觸發(fā)器/未加密的存儲過程或視圖）使用sp_bindrule綁定規(guī)則sp_bindrule

規(guī)則名,‘表名.字段名‘使用sp_unbindrule解除規(guī)則sp_unbindrule

'employee.hire_date'刪除規(guī)則。DROP

RULEhire_date_rule2023/2/39規(guī)則在管理臺查看。2023/2/310約束(Constraint)約束定義了一列數(shù)據(jù)或一行數(shù)據(jù)表的的限制條件。使用約束優(yōu)先于使用觸發(fā)器、規(guī)則和默認(rèn)值。約束在表定義中聲明。SQLServer中有6種約束：主鍵約束外鍵約束惟一性約束檢查約束默認(rèn)約束非空值約束①PRIMARYKEY約束

PRIMARYKEY約束標(biāo)識列或列集，這些列或列集的值唯一標(biāo)識表中的行。一個PRIMARYKEY約束可以：作為表定義的一部分在創(chuàng)建表時創(chuàng)建。添加到還沒有PRIMARYKEY約束的表中（一個表只能有一個PRIMARYKEY約束）。如果已有PRIMARYKEY約束，則可對其進行修改或刪除。例如，可以使表的PRIMARYKEY約束引用其他列，更改列的順序、索引名、聚集選項或PRIMARYKEY約束的填充因子。定義了PRIMARYKEY約束的列的列寬不能更改。②FOREIGNKEY約束

FOREIGNKEY約束稱為外鍵約束，用于標(biāo)識表之間的關(guān)系，以強制參照完整性，即為表中一列或者多列數(shù)據(jù)提供參照完整性。FOREIGNKEY約束也可以參照自身表中的其他列，這種參照稱為自參照。

FOREIGNKEY約束可以在下面情況下使用：作為表定義的一部分在創(chuàng)建表時創(chuàng)建。如果FOREIGNKEY約束與另一個表（或同一表）已有的PRIMARYKEY約束或UNIQUE約束相關(guān)聯(lián)，則可向現(xiàn)有表添加FOREIGNKEY約束。一個表可以有多個FOREIGNKEY約束。對已有的FOREIGNKEY約束進行修改或刪除。例如，要使一個表的FOREIGNKEY約束引用其他列。定義了FOREIGNKEY約束列的列寬不能更改。使用FOREIGNKEY約束，還應(yīng)注意以下幾個問題：一個表中最多可以有253個可以參照的表，因此每個表最多可以有253個FOREIGNKEY約束。在FOREIGNKEY約束中，只能參照同一個數(shù)據(jù)庫中的表，而不能參照其他數(shù)據(jù)庫中的表。FOREIGNKEY子句中的列數(shù)目和每個列指定的數(shù)據(jù)類型必須和REFERENCE子句中的列相同。FOREIGNKEY約束不能自動創(chuàng)建索引。參照同一個表中的列時，必須只使用REFERENCE子句，而不能使用FOREIGNKEY子句。在臨時表中，不能使用FOREIGNKEY約束。外鍵約束創(chuàng)建外鍵約束有兩種方法，通過管理平臺和SQL語句（1）在SQLServer管理平臺中添加外部鍵約束，在SQLServer管理平臺中添加外部鍵約束。如圖所示。

點擊此處對外鍵進行編輯選擇主鍵表、字段，外鍵表、字段外鍵約束外鍵約束外鍵約束與主鍵約束相同，也分為表約束與列約束。定義表級外鍵約束的語法格式如下：[CONSTRAINTconstraint_name]FOREIGNKEY(column_name[,…n])REFERENCESref_table[(ref_column[,…n])][ONDELETE{CASCADE|NOACTION}][ONUPDATE{CASCADE|NOACTION}]][NOTFORREPLICATION]定義列級外鍵約束的語法格式如下：[CONSTRAINTconstraint_name][FOREIGNKEY]REFERENCESref_table[NOTFORREPLICATION]外鍵約束FROEIGNKEY：指定外鍵名稱REFERENCES:指定關(guān)聯(lián)的表③UNIQUE約束

UNIQUE約束在列集內(nèi)強制執(zhí)行值的唯一性。對于UNIQUE約束中的列，表中不允許有兩行包含相同的非空值。主鍵也強制執(zhí)行唯一性，但主鍵不允許空值，而且每個表中主鍵只能有一個，但是UNIQUE列卻可以有多個。

UNIQUE約束優(yōu)先于唯一索引。惟一約束例子：在grade表中定義“課程編號”這列惟一約束CREATETABLEgrade(學(xué)號CHAR(8)NOTNULL,課程編號CHAR(8)NOTNULLCONSTRAINTu_grade_課程編號UNIQUENONCLUSTERED,分?jǐn)?shù)CHAT(10)NULL)定義列級約束惟一約束例子：在grade表中定義“課程編號”這列惟一約束，在表的列都定義完畢后，定義惟一約束定義約束：方法二CREATETABLEgrade(學(xué)號CHAR(8)NOTNULL,課程編號CHAR(8)NOTNULL,分?jǐn)?shù)CHAT(10)NULL，CONSTRAINTu_grade_課程編號UNIQUE

NONCLUSTERED（課程編號）)惟一約束通過管理平臺創(chuàng)建惟一約束，打開管理平臺，選擇studentsdb數(shù)據(jù)庫，選中g(shù)rade表，右鍵單擊選擇“修改表”，選擇其中要設(shè)置惟一約束的列，右鍵“索引/鍵”，如圖1，打開后，在“是否惟一”項中選擇“是”，則創(chuàng)建了惟一約束。如圖2如圖1如圖2檢查約束檢查(Check)約束對輸入列或整個表中的值設(shè)置檢查條件，以限制輸入值，保證數(shù)據(jù)庫的數(shù)據(jù)完整性。檢查約束用于定義插入或者是修改一行時所滿足的條件，通常限制某些字段的取值范圍，比如人的性別只能是男和女，檢查約束既可以作為列級約束，也可以作為表級約束。檢查約束用途21創(chuàng)建檢查約束常用的操作方法有如下兩種：使用SQLServer管理平臺創(chuàng)建檢查約束；用Transact-SQL語句創(chuàng)建檢查約束。（1）使用SQLServer管理平臺創(chuàng)建檢查約束，如圖14-3所示圖14-3創(chuàng)建檢查約束對話框

點擊此處可進行CHECK約束編輯檢查約束CHECK創(chuàng)建檢查約束檢查約束CHECK第二種方法，通過SQL語句創(chuàng)建如果表已經(jīng)存在，則可以通過下面的SQL語句創(chuàng)建例如：grade(學(xué)號，課程編號，分?jǐn)?shù))為分?jǐn)?shù)這一列設(shè)置檢查約束，分?jǐn)?shù)的值在0~100之間創(chuàng)建檢查約束ALTERTABLEgradeADDCONSTRAINTCK_分?jǐn)?shù)CHECK(分?jǐn)?shù)<100and分?jǐn)?shù)>0)注意：多個CHECK條件需要用邏輯運算符連接檢查約束CHECK第二種方法，通過SQL語句創(chuàng)建如果表不存在，則需要在創(chuàng)建表的時候通過SQL語句創(chuàng)建某個列或者是多列的檢查約束。例如：創(chuàng)建grade(學(xué)號，課程編號，分?jǐn)?shù))表，指定分?jǐn)?shù)的取值范圍在0~100之間創(chuàng)建檢查約束CREATETABLEgrade(學(xué)號CHAR(8)NOTNULL,課程編號CHAR(8)NOTNULL，分?jǐn)?shù)CHAT(10)NULL，CONSTRAINTCK_grade_分?jǐn)?shù)CHECK(分?jǐn)?shù)<100and分?jǐn)?shù)>0),

)練習(xí)為studentsdb數(shù)據(jù)庫的student_info表的學(xué)號添加有效性約束：學(xué)號的最左邊兩位字符是00。ALTERTABLEstudent_infoADDCONSTRAINTCK_學(xué)號

CHECK(學(xué)號LIKE'00%')默認(rèn)約束默認(rèn)(Default)約束通過定義列的默認(rèn)值或使用數(shù)據(jù)庫的默認(rèn)值對象綁定表的列，以確保在沒有為某列指定數(shù)據(jù)時，來指定列的值。默認(rèn)值可以是常量，也可以是表達式，還可以為NULL值。默認(rèn)約束的定義和用途26創(chuàng)建默認(rèn)約束常用的操作方法有如下兩種：使用SQLServer管理平臺創(chuàng)建默認(rèn)約束；創(chuàng)建默認(rèn)約束的Transact-SQL語句操作法。（1）使用SQLServer管理平臺創(chuàng)建默認(rèn)約束，如圖所示圖:創(chuàng)建默認(rèn)約束對話框

默認(rèn)約束DEFAULT管理平臺創(chuàng)建默認(rèn)約束默認(rèn)約束DEFAULT默認(rèn)約束的語法格式如下[CONSTRAINTconstraint_name]DEFAULTconstant_expression[FORcolumn_name]SQL語句創(chuàng)建默認(rèn)約束默認(rèn)約束DEFAULT例子：在創(chuàng)建表的同時，添加默認(rèn)約束創(chuàng)建一個表student_info(學(xué)號，姓名，性別，家庭住址)，其中性別的默認(rèn)值是“男”CREATETBALEstudent_info(學(xué)號CHAR(8)NOTNULL,姓名CHAR(12)NOTNULL,性別CHAR(2)DEFAULT‘男’NULL)SQL語句創(chuàng)建默認(rèn)約束默認(rèn)約束DEFAULT例子：為student_info表中的性別添加默認(rèn)值，默認(rèn)值是“男”ALTERTABLEstudent_infoADDCONSTRAINTsex_defaultDEFAULT‘男’FOR

性別SQL語句創(chuàng)建默認(rèn)約束默認(rèn)約束DEFAULT練習(xí)：為grade（學(xué)號，課程編號，分?jǐn)?shù)）中的“課程編號”創(chuàng)建默認(rèn)約束，默認(rèn)值為“0001”ALTERTABLEgradeADDCONSTRAINT

課程編號_defaultDEFAULT‘0001’FOR

課程編號2023/2/331工號商品號數(shù)量銷售日期工號姓名性別身份證號生日入職日期移動電話商品號商品名單位單價員工表銷售表商品表數(shù)據(jù)完整性要求：（1）實體完整，不能有重復(fù)行，用主鍵約束，員工表中的身份證不能出現(xiàn)同號，用唯一約束。（2）參照完整，銷售表中的工號取值必須來自員工表，商品號取值必須來自商品表，用外鍵約束（3）姓名不能為空，用非空約束。（4）性別只能取‘男’或‘女’，入職日期不能比生日大18年，移動電話必須為“1？？？？？？？？？”，用檢查約束。（5）當(dāng)性別沒有輸入值時，默認(rèn)為‘男’,當(dāng)單位沒有輸入值時，默認(rèn)為‘個’用默認(rèn)約束。分析實例2023/2/332案例表2.通過創(chuàng)建約束來強制完整性關(guān)鍵詞：可以在每列上定義（稱為列約束)，也可以在所有的列之后定義(稱為表約束).2023/2/334說明：創(chuàng)建的所有約束都有約束名、約束類型和約束對象。約束名可以顯式指出，如“CONSTRAINT

約束名”，如果沒有顯式指出，系統(tǒng)會自動生成一個約束名。如果一個約束只涉及一個列，可以在列的定義中創(chuàng)建該約束。一個約束涉及一個或多個列，則約束都可以在所有列之后創(chuàng)建。案例2023/2/3358.4.2外鍵約束例：關(guān)系圖課堂小練如下圖建表，表名：student，注意表中約束：1、主鍵約束2、姓名不為空3、性別默認(rèn)為‘男’4、身份證號唯一，長度為185、手機約束：11位，如：'1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]‘2023/2/3363.綜合練---課后體會2023/2/338如下表學(xué)號課程號成績學(xué)號姓名性別身份證號生日手機號課程號課程名學(xué)分學(xué)生表課程表成績表2023/2/339--創(chuàng)建數(shù)據(jù)庫CreateDatabaseTEACHONPRIMARY(NAME=Teach_dat,FILENAME='D:\YZ\TEACH.mdf')LOGON(NAME=Teach_log,FILENAME='D:\YZ\TEACH.ldf')gouseteachgo2023/2/340--創(chuàng)建數(shù)據(jù)表CREATETABLE學(xué)生表(學(xué)號char(10)PrimaryKey,

姓名nchar(4)NOTNULL,

性別nchar(1)NOTNULLDEFAULT'男',生日DateTime,身份證號char(18)UNIQUE,手機char(11),CHECK(性別='男'or性別='女'),CHECK(手機Like'1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))go2023/2/341CREATETABLE課程表(課程號char(6)PRIMARYKEYNONCLUSTERED,

課程名nchar(10)NOTNULLUNIQUE,

學(xué)分intNOTNULL)goCREATETABLE成績表(學(xué)號char(10)NOTNULLFOREIGNKEYREFERENCES學(xué)生表(學(xué)號),

課程號char(6)NOTNULLFOREIGNKEYREFERENCES課程表(課程號),

成績intDEFAULT(0),PRIMARYKEYNONCLUSTERED(學(xué)號,課程號))go2023/2/342--添加數(shù)據(jù)INSERTINTO學(xué)生表(學(xué)號,姓名,性別,生日,身份證號,手機)VALUES('1000000001','一','男','1981-1-1',,)INSERTINTO學(xué)生表(學(xué)號,姓名,性別,生日,身份證號,手機)VALUES('1000000002','二','男','1981-1-1',,)INSERTINTO學(xué)生表(學(xué)號,姓名,性別,生日,身份證號,手機)VALUES('1000000003','三','男','1981-1-1',,)goINSERTINTO課程表(課程號,課程名,學(xué)分)VALUES('NN0001','計算機網(wǎng)絡(luò)',4)INSERTINTO課程表(課程號,課程名,學(xué)分)VALUES('NN0002','數(shù)據(jù)庫原理與應(yīng)用',4)INSERTINTO課程表(課程號,課程名,學(xué)分)VALUES('NN0003','計算機高級語言',4)goINSERTINTO成績表(學(xué)號,課程號,成績)VALUES('1000000001','NN0001',65)INSERTINTO成績表(學(xué)號,課程號,成績)VALUES('1000000002','NN0001',74)INSERTINTO成績表(學(xué)號,課程號,成績)VALUES('1000000003','NN0001',53)INSERTINTO成績表(學(xué)號,課程號,成績)VALUES('1000000001','NN0002',77)INSERTINTO成績表(學(xué)號,課程號,成績)VALUES('1000000002','NN0002',82)INSERTINTO成績表(學(xué)號,課程號,成績)VALUES('1000000003','NN0002',75)INSERTINTO成績表(學(xué)號,課程號,成績)VALUES('1000000001','NN0003',81)INSERTINTO成績表(學(xué)號,課程號,成績)VALUES('1000000002','NN0003',91)INSERTINTO成績表(學(xué)號,課程號,成績)VALUES('1000000003','NN0003',66)go2023/2/343--創(chuàng)建視圖CREATEVIEW課程平均成績視圖asSELECT課程名,AVG(b.成績)as平均成績FROM課程表aJOIN成績表bona.課程號=b.課程號GROUPBYa.課程名goCREATEVIEW學(xué)生修讀學(xué)分視圖asSELECTa.姓名,SUM(c.學(xué)分)as修讀學(xué)分,sum(casewhenb.成績>60thenc.學(xué)分else0