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

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

CREATE

RULE

rule_name

AS

condition_expression使用sp_helptext

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

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

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

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

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

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

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

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

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

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

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

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

點(diǎn)擊此處可進(jìn)行CHECK約束編輯檢查約束CHECK創(chuàng)建檢查約束檢查約束CHECK第二種方法，通過(guò)SQL語(yǔ)句創(chuàng)建如果表已經(jīng)存在，則可以通過(guò)下面的SQL語(yǔ)句創(chuàng)建例如：grade(學(xué)號(hào)，課程編號(hào)，分?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)注意：多個(gè)CHECK條件需要用邏輯運(yùn)算符連接檢查約束CHECK第二種方法，通過(guò)SQL語(yǔ)句創(chuàng)建如果表不存在，則需要在創(chuàng)建表的時(shí)候通過(guò)SQL語(yǔ)句創(chuàng)建某個(gè)列或者是多列的檢查約束。例如：創(chuàng)建grade(學(xué)號(hào)，課程編號(hào)，分?jǐn)?shù))表，指定分?jǐn)?shù)的取值范圍在0~100之間創(chuàng)建檢查約束CREATETABLEgrade(學(xué)號(hào)CHAR(8)NOTNULL,課程編號(hào)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ù)庫(kù)的student_info表的學(xué)號(hào)添加有效性約束：學(xué)號(hào)的最左邊兩位字符是00。ALTERTABLEstudent_infoADDCONSTRAINTCK_學(xué)號(hào)

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

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

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

課程編號(hào)_defaultDEFAULT‘0001’FOR

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

約束名”，如果沒(méi)有顯式指出，系統(tǒng)會(huì)自動(dòng)生成一個(gè)約束名。如果一個(gè)約束只涉及一個(gè)列，可以在列的定義中創(chuàng)建該約束。一個(gè)約束涉及一個(gè)或多個(gè)列，則約束都可以在所有列之后創(chuàng)建。案例2023/2/3358.4.2外鍵約束例：關(guān)系圖課堂小練如下圖建表，表名：student，注意表中約束：1、主鍵約束2、姓名不為空3、性別默認(rèn)為‘男’4、身份證號(hào)唯一，長(zhǎng)度為185、手機(jī)約束：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.綜合練---課后體會(huì)2023/2/338如下表學(xué)號(hào)課程號(hào)成績(jī)學(xué)號(hào)姓名性別身份證號(hào)生日手機(jī)號(hào)課程號(hào)課程名學(xué)分學(xué)生表課程表成績(jī)表2023/2/339--創(chuàng)建數(shù)據(jù)庫(kù)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é)號(hào)char(10)PrimaryKey,

姓名nchar(4)NOTNULL,

性別nchar(1)NOTNULLDEFAULT'男',生日DateTime,身份證號(hào)char(18)UNIQUE,手機(jī)char(11),CHECK(性別='男'or性別='女'),CHECK(手機(jī)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課程表(課程號(hào)char(6)PRIMARYKEYNONCLUSTERED,

課程名nchar(10)NOTNULLUNIQUE,

學(xué)分intNOTNULL)goCREATETABLE成績(jī)表(學(xué)號(hào)char(10)NOTNULLFOREIGNKEYREFERENCES學(xué)生表(學(xué)號(hào)),

課程號(hào)char(6)NOTNULLFOREIGNKEYREFERENCES課程表(課程號(hào)),

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