數(shù)據(jù)庫 第05章 實(shí)現(xiàn)數(shù)據(jù)完整性課件

第5章實(shí)現(xiàn)數(shù)據(jù)完整性數(shù)據(jù)完整性的類型約束禁用約束默認(rèn)值和規(guī)則決定使用何種強(qiáng)制方法推薦操作第5章實(shí)現(xiàn)數(shù)據(jù)完整性數(shù)據(jù)完整性的類型數(shù)據(jù)完整性的類型域完整性

(列)實(shí)體完整性

（行）參照完整性

(表之間)數(shù)據(jù)完整性的類型域完整性

(列)實(shí)體完整性（行）參照完整性數(shù)據(jù)完整性的類型4.1數(shù)據(jù)完整性的類型數(shù)據(jù)完整性指的是數(shù)據(jù)庫中存儲的數(shù)據(jù)的一致性和準(zhǔn)確性數(shù)據(jù)完整性的類型：域完整性、實(shí)體完整性、引用完整性域完整性域（或列）完整性是指對列指定一組有效的值并決定是否可為空值實(shí)體完整性實(shí)體（或表）完整性要求表中的所有行都有一個(gè)惟一的標(biāo)識符，稱為主鍵值引用完整性引用完整性確保主鍵（在被引用表中）和外鍵（在引用表中）之間的關(guān)系得到保持?jǐn)?shù)據(jù)完整性的類型4.1數(shù)據(jù)完整性的類型數(shù)據(jù)完整性第5章實(shí)現(xiàn)數(shù)據(jù)完整性數(shù)據(jù)完整性的類型約束禁用約束默認(rèn)值和規(guī)則決定使用何種強(qiáng)制方法推薦操作第5章實(shí)現(xiàn)數(shù)據(jù)完整性數(shù)據(jù)完整性的類型決定使用何種約束約束的類型約束4.2約束決定使用何種約束約束4.2約束決定使用何種約束4.2.1決定使用何種約束完整性類型約束類型描述域DEFAULT如果在INSERT語句中未顯式提供

值，則指定為列提供的值CHECK指定列中可接受的數(shù)據(jù)值REFERENTIAL(通常使用外鍵)基于其他表中的列的值，指定可接受的用于更新的數(shù)據(jù)值實(shí)體PRIMARYKEY惟一標(biāo)識每一列，確保用戶沒有輸入重復(fù)的值。同時(shí)創(chuàng)建一個(gè)索引以增強(qiáng)性能。不允許空值UNIQUE確保在非主鍵列中不輸入重復(fù)值，并創(chuàng)建一個(gè)索引以增強(qiáng)性能。允許空值引用FOREIGNKEY定義一列或多列的值與同表或其他表中主鍵的值匹配CHECK基于同表中其他列的值，指定列中可接受的數(shù)據(jù)值決定使用何種約束4.2.1決定使用何種約束完整性類型約束創(chuàng)建約束使用CREATETABLE或者ALTERTABLECREATETABLE是在創(chuàng)建表時(shí)創(chuàng)建約束ALTERTABLE是在一個(gè)已有的表上創(chuàng)建約束可以添加約束到已有數(shù)據(jù)的表上可添加單列或多列約束若約束應(yīng)用于單列，稱為列級約束若約束引用了多列，稱為表級約束，即使它并沒有引用表中的所有列4.3.2創(chuàng)建約束創(chuàng)建約束使用CREATETABLE或者ALTERT使用約束的注意事項(xiàng)可直接在表上創(chuàng)建、更改和刪除約束，而不必刪除并重建表應(yīng)該在應(yīng)用程序和事務(wù)內(nèi)建立錯(cuò)誤檢查邏輯，以測試是否違反了某個(gè)約束SQLServer里的約束只是“最后防線”當(dāng)給一個(gè)表添加約束的時(shí)候，SQLServer將檢查現(xiàn)有數(shù)據(jù)是否違反約束建議創(chuàng)建約束的時(shí)候指定名稱，否則系統(tǒng)將為約束自動產(chǎn)生一個(gè)復(fù)雜的名稱名稱必須惟一，且符合SQLServer標(biāo)識符的規(guī)則查看約束的信息系統(tǒng)存儲過程：sp_helpconstraint、sp_help信息模式視圖：check_constraints、referential_constraints、table_constraints系統(tǒng)表：syscomments、sysreferences、sysconstraints使用約束的注意事項(xiàng)可直接在表上創(chuàng)建、更改和刪除約束，而不必刪約束的類型DEFAULT約束CHECK約束PRIMARYKEY約束UNIQUE約束FOREIGNKEY約束級聯(lián)引用完整性4.2.2約束的類型約束的類型DEFAULT約束4.2.2約束的類型PRIMARYKEY約束PRIMARYKEY約束在表中定義了一個(gè)惟一標(biāo)識每一列的主鍵

語法：[CONSTRAINT約束名]PRIMARYKEY[CLUSTERED|NONCLUSTERED]{(列[,...n])}4.2.2.3PRIMARYKEY約束PRIMARYKEY約束PRIMARYKEY約束在表

PRIMARYKEY約束利用表中的一列或多列數(shù)據(jù)唯一地標(biāo)識某一行數(shù)據(jù)每個(gè)表只有一個(gè)PRIMARYKEY約束PRIMARYKEY約束的值必須是唯一的不允許有空值SQLServer中最多可定義16列作為主鍵PRIMARYKEY約束PRIMARYKEY約束利用表中的一列或多列數(shù)據(jù)唯一地標(biāo)直接在列名后增加關(guān)鍵字

PRIMARYKEY

CREATETABLEStudent (snochar(5)PRIMARYKEY,snamevarchar(20)notnull,

……

);

列級主鍵創(chuàng)建sc表，其主碼為（sno,cno）×???

CREATETABLEsc(snochar(5)PRIMARYKEY，cnochar(1)PRIMARYKEY，gradedecimal(4,1)));PRIMARYKEY約束直接在列名后增加關(guān)鍵字PRIMARYKEY列級主鍵創(chuàng)建在CREATETABLE語句各列定義的最后加:PRIMARYKEY(<屬性名表>)CREATETABLEsc(snochar(5)，cnochar(1)，gradedecimal(4,1),PRIMARYKEY(sno,cno));

CREATETABLEsc1(……….,

constraintPK_SCPRIMARYKEY(sno,cno));表級主鍵約束名PRIMARYKEY約束在CREATETABLE語句各列定義的最后加:CREA刪除表上已定義的主鍵

ALTERTABLESC1

DROPCONSTRAINTPK_SC;在沒有定義主鍵的表上，加上一個(gè)主鍵

ALTERTABLESC

ADDCONSTRAINTPK_SCprimarykey(sno,cno);刪除和添加主鍵刪除表上已定義的主鍵刪除和添加主鍵DEFAULT約束如果一個(gè)列的值在INSERT語句中沒有指定，DEFAULT約束將自動輸入一個(gè)值，可以是預(yù)先指定的常量、NULL或者一個(gè)系統(tǒng)函數(shù)運(yùn)行時(shí)的值

語法：

[CONSTRAINT約束名]DEFAULT約束表達(dá)式

創(chuàng)建DEFAULT約束的兩種方法創(chuàng)建一個(gè)默認(rèn)對象（CREATEDEFAULT），然后使用存儲過程sp_bindefault將默認(rèn)綁定到一個(gè)列CREATETABLE或ALTERTABLE時(shí)使用DEFAUTL約束4.2.2.1DEFAULT約束DEFAULT約束如果一個(gè)列的值在INSERT語句中沒DEFAULT約束（續(xù)）在創(chuàng)建表時(shí)使用default屬性4.2.2.1DEFAULT約束CREATETABLEuserInfo(...…countryvarchar(50)notnullDEFAULT‘China’,……)為已經(jīng)創(chuàng)建好的表添加default屬性USENorthwind

ALTERTABLEdbo.Customers

ADD

CONSTRAINTDF_contactnameDEFAULT'UNKNOWN'

FORContactName應(yīng)用DEFAULT約束的幾種情況DEFAULT約束（續(xù)）在創(chuàng)建表時(shí)使用default屬性4DEFAULT約束（續(xù)）應(yīng)用DEFAULT約束的注意事項(xiàng)允許使用常量、函數(shù)、系統(tǒng)提供的值：USER、CURRENT_USER、SESSION_USER、SYSTEM_USER及CURRENT_TIMESTAMP

例如：①DEFAULTUSER②DEFAULE(getdate())有可能會和CHECK約束沖突常量值外面可以加或者不加括號，字符或者日期常量必須加上單引號或雙引號4.2.2.1DEFAULT約束忽略，

不顯式地插入值插入NULL插入DEFAULT無默認(rèn)值有默認(rèn)值無默認(rèn)值有默認(rèn)值無默認(rèn)值有默認(rèn)值NULLNULL默認(rèn)值NULLNULLNULL默認(rèn)值NOTNULL錯(cuò)誤默認(rèn)值錯(cuò)誤錯(cuò)誤錯(cuò)誤默認(rèn)值DEFAULT約束（續(xù)）應(yīng)用DEFAULT約束的注意事CHECK約束限制輸入到指定列的值只能為某些特定值

語法：[CONSTRAINT約束名]CHECK(邏輯表達(dá)式)

兩種強(qiáng)制域完整性的方法：CHECK約束和規(guī)則CHECK約束定義了一個(gè)表達(dá)式，若數(shù)據(jù)修改語句使得表達(dá)式值為FALSE的話，將拒絕語句執(zhí)行規(guī)則的功能和CHECK約束基本相同，除了語法不同，能力稍弱。規(guī)則可定義一次，然后對多個(gè)列分別綁定；而CHECK約束則需要對每個(gè)列定義。但CHECK約束的功能略強(qiáng)一些（例如引用同行中其他列的值和調(diào)用系統(tǒng)函數(shù)等）4.2.2.2CHECK約束CHECK約束限制輸入到指定列的值只能為某些特定值4.2.CHECK約束（續(xù)）應(yīng)用CHECK約束的幾種情況4.2.2.2CHECK約束USENorthwind

ALTERTABLEdbo.Employees

ADD

CONSTRAINTCK_birthdate

CHECK(BirthDate>'01-01-1900'ANDBirthDate<getdate())在現(xiàn)有表中創(chuàng)建CHECK約束CREATETABLEuserInfo(...…birthdayvarchar(50)notnullCONSTRAINTck_birthdayCHECK(BirthDate>'01-01-1900'ANDBirthDate<getdate()),)在創(chuàng)建表時(shí)使用CHECK約束CHECK約束（續(xù)）應(yīng)用CHECK約束的幾種情況4.2.2在創(chuàng)建SC表時(shí)定義CHECK約束

CreateTableSC (snochar(5)notnull, cnochar(1)notnull, gradedecimal(4,1) primarykey(sno,cno));

創(chuàng)建CHECK約束列級CHECK約束check(grade>=0andgrade<=100),在創(chuàng)建SC表時(shí)定義CHECK約束創(chuàng)建CHECK約束列級CCreateTableSC (snochar(5)notnull, cnochar(1)notnull, gradedecimal(4,1),primarykey(sno,cno),check(grade>=0andgrade<=100));CreateTableSC(……..,

constraintchk_gmkcheck(grade>=0andgrade<=100));

創(chuàng)建CHECK約束表級CHECK約束CreateTableSC創(chuàng)建CHECK約束表級CHCreateTableStudent (snochar(5)notnull, birthdatedatetimenotnull schooldatedatetimenotnull,primarykey(sno),

);

創(chuàng)建CHECK約束表級CHECK約束check(birthdate<getdate()),check(birthdate<getdate()),列級CHECK約束CreateTableStudent創(chuàng)建CHECK約刪除SC表中的CHECK約束ALTERTABLESC

DROPCONSTRAINTchk_gmk向SC表中添加CHECK約束ALTERTABLESC

ADDCONSTRAINTchk_gmk

check(gmark>=0andgmark<=100)

添加和刪除CHECK約束刪除SC表中的CHECK約束添加和刪除CHECK約束CHECK約束（續(xù)）應(yīng)用CHECK約束的注意事項(xiàng)一個(gè)表可以定義多個(gè)CHECK約束，一個(gè)列上只允許創(chuàng)建一個(gè)列級CHECK約束。列級CHECK約束只能引用被約束的列，表級CHECK約束只能引用同一表中的列。當(dāng)列上存在規(guī)則和一個(gè)或多個(gè)CHECK約束時(shí)，將驗(yàn)證所有限制。4.2.2.2CHECK約束CHECK約束（續(xù)）應(yīng)用CHECK約束的注意事項(xiàng)4.2.UNIQUE約束UNIQUE約束指明列中的任意兩行不能有相同的值

語法：[CONSTRAINT約束名]UNIQUE[CLUSTERED|NONCLUSTERED]{(列[,...n])}

應(yīng)用UNIQUE約束的注意事項(xiàng)允許空值的列上定義UNIQUE約束在一個(gè)表上允許多個(gè)UNIQUE約束可在一個(gè)或者多個(gè)列上定義是通過一個(gè)惟一索引強(qiáng)制約束的4.2.2..4UNIQUE約束UNIQUE約束UNIQUE約束指明列中的任意兩行不能有UNIQUE約束（續(xù)）PRIMARYKEY和UNIQUE約束聲明PRIMARYKEY或UNIQUE約束的結(jié)果只是自動創(chuàng)建了一個(gè)指定列上的惟一索引，通過惟一索引來確保值的惟一性可空性PRIMARYKEY的各個(gè)列必須聲明為NOTNULL，而UNIQUE的各個(gè)列可以聲明為允許NULL值在惟一索引中，認(rèn)為所有的NULL值是相等的索引的屬性PRIMARYKEY約束所創(chuàng)建的惟一索引默認(rèn)為CLUSTERED，除非表中另外一列已經(jīng)聲明為CLUSTEREDUNIQUE約束所創(chuàng)建的惟一索引默認(rèn)為NONCLUSTERED選擇鍵保持鍵的長度盡可能短，必要時(shí)可另外創(chuàng)建一個(gè)代替鍵不要使用float或real數(shù)據(jù)類型的列作為主鍵UNIQUE約束（續(xù)）PRIMARYKEY和UNIQ在創(chuàng)建表時(shí)定義UNIQUE約束CREATETABLEtest2

(even_idintprimarykey,even_namechar(20),even_typechar(20),even_timedatetime,

)

CONSTRAINTUNIQ_EVENUNIQUE(even_type,even_time)要求：在even_type和even_time上共同建立惟一約束表級UNIQUE約束創(chuàng)建UNIQUE約束在創(chuàng)建表時(shí)定義UNIQUE約束CONSTRAINTUNIQ向test1表中的tname

列添加UNIQUE約束

ALTERTABLEtest1

ADDCONSTRAINTUNIQ_TNAME

UNIQUE(tname)刪除表上已定義的UNIQUE約束ALTERTABLEtest1

DROPCONSTRAINTUNIQ_TNAME

添加和刪除UNIQUE約束向test1表中的tname列添加UNIQUE約束FOREIGNKEY約束FOREIGNKEY約束：定義到同表或其他表中具有PRIMARYKEY或者UNIQUE約束的列的引用

語法： [CONSTRAINT約束名][FOREIGNKEY][(列[,…n])]

REFERENCES引用表[(引用列[,…n])]具有FOREIGNKEY約束的列的取值范圍只能是被引用的列的列值4.2.2.5FOREIGNKEY約束FOREIGNKEY約束FOREIGNKEY約束：定FOREIGNKEY約束（續(xù)）應(yīng)用FOREIGNKEY約束的注意事項(xiàng)FOREIGNKEY子句中指定的列的個(gè)數(shù)和數(shù)據(jù)類型必須和REFERENCES子句中指定的列的個(gè)數(shù)和數(shù)據(jù)類型匹配并不自動創(chuàng)建索引修改數(shù)據(jù)的時(shí)候，用戶必須在被FOREIGNKEY約束引用的表上具有SELECT或REFERENCES權(quán)限若引用的是同表中的列，那么可只用REFERENCES子句而省略FOREIGNKEY子句4.2.2.5FOREIGNKEY約束FOREIGNKEY約束（續(xù)）應(yīng)用FOREIGNKEFOREIGNKEY約束（續(xù)）應(yīng)用FOREIGNKEY約束的幾種情況：創(chuàng)建表的同時(shí)創(chuàng)建FOREIGNKEY約束

createtablestuscore(idintnotnull,courseIDintnotnull,scoreintnotnull,constraintfk_student_stuscoreforeignkey(id)referencesstudent(id))FOREIGNKEY約束（續(xù)）應(yīng)用FOREIGNKEYFOREIGNKEY約束（續(xù)）創(chuàng)建完表后通過修改表增加FOREIGNKEY約束使用FOREIGNKEY約束，確保Orders表中的客戶標(biāo)識與Customers表中的有效的客戶標(biāo)識相關(guān)聯(lián)4.2.2.5FOREIGNKEY約束altertablestuscoreaddconstraintfk_student_stuscoreforeignkey(id)referencesstudent(id)FOREIGNKEY約束（續(xù)）創(chuàng)建完表后通過修改表增加FFOREIGNKEY約束（續(xù)）FOREIGNKEY約束的刪除altertablestuscoredropconstraintfk_student_stuscoreFOREIGNKEY約束（續(xù)）FOREIGNKEY約級聯(lián)引用完整性4.2.2.6級聯(lián)引用完整性FOREIGNKEY約束包含一個(gè)CASCADE選項(xiàng)，允許對一個(gè)定義了UNIQUE或者PRIMARYKEY約束的列的值的修改自動傳播到引用它的外鍵上，這個(gè)動作稱為級聯(lián)引用完整性

語法： [CONSTRAINT約束名][FOREIGNKEY][(列[,…n])]

REFERENCES引用表[(引用列[,…n])]

[ONDELETE{CASCADE|NOACTION}]

[ONUPDATE{CASCADE|NOACTION}]NOACTION：任何企圖刪除或者更新被其他表的外鍵所引用的鍵都將引發(fā)一個(gè)錯(cuò)誤，對數(shù)據(jù)的改變會被回滾。NOACTION是默認(rèn)值CASCADE：若父表中的行變化了，則引用表中相應(yīng)的行也自動變化級聯(lián)引用完整性4.2.2.6級聯(lián)引用完整性FOREIGN級聯(lián)引用完整性（續(xù)）4.2.2.6級聯(lián)引用完整性應(yīng)用CASCADE選項(xiàng)的注意事項(xiàng)可在多個(gè)具有引用關(guān)系的表之間組合CASCADE和NOACTION選項(xiàng)。若SQLServer遇到NOACTION，則中斷并回滾所有相關(guān)的CASCADE動作CASCADE選項(xiàng)不能對定義為rowversion數(shù)據(jù)類型的外鍵或主鍵列指定級聯(lián)引用完整性（續(xù)）4.2.2.6級聯(lián)引用完整性應(yīng)用CA第5章實(shí)現(xiàn)數(shù)據(jù)完整性數(shù)據(jù)完整性的類型約束禁用約束默認(rèn)值和規(guī)則決定使用何種強(qiáng)制方法推薦操作第5章實(shí)現(xiàn)數(shù)據(jù)完整性數(shù)據(jù)完整性的類型禁用約束禁用現(xiàn)有數(shù)據(jù)上的約束檢查在加載新數(shù)據(jù)時(shí)禁用約束檢查4.3禁用約束禁用約束禁用現(xiàn)有數(shù)據(jù)上的約束檢查4.3禁用約束禁用現(xiàn)有數(shù)據(jù)上的約束檢查當(dāng)在表上添加約束的時(shí)候，我們可以禁用對已有數(shù)據(jù)的約束檢查

語法：ALTERTABLE表名

[WITHCHECKWITHNOCHECK]ADDCONSTRAINT約束名

[FOREIGNKEY][(column[,…n])]REFERENCES引用表[(引用列[,…n])][CHECK(搜索條件)]

4.3.1禁用現(xiàn)有數(shù)據(jù)上的約束檢查禁用現(xiàn)有數(shù)據(jù)上的約束檢查當(dāng)在表上添加約束的時(shí)候，我們可以禁用禁用現(xiàn)有數(shù)據(jù)上的約束檢查（續(xù)）對已有數(shù)據(jù)禁用約束檢查的注意事項(xiàng)只能禁用CHECK和FOREIGNKEY約束當(dāng)為一個(gè)已有數(shù)據(jù)的表添加CHECK或FOREIGNKEY約束的時(shí)候，使用WITHNOCHECK選項(xiàng)來禁用對已有數(shù)據(jù)的約束檢查4.3.1禁用現(xiàn)有數(shù)據(jù)上的約束檢查禁用現(xiàn)有數(shù)據(jù)上的約束檢查（續(xù)）對已有數(shù)據(jù)禁用約束檢查的注意事在加載新數(shù)據(jù)時(shí)禁用約束檢查為了避免約束檢查的開銷，有時(shí)候可能希望禁用約束已經(jīng)確保數(shù)據(jù)符合約束數(shù)據(jù)并不符合約束，但稍后可以更改其值并重用約束語法：ALTERTABLE表名{CHECK|NOCHECK}CONSTRAINT{ALL|約束名[,...n]}查看約束是啟用還是禁用的狀態(tài)系統(tǒng)存儲過程sp_help系統(tǒng)函數(shù)OBJECTPROPERTY的CnstIsDisabled屬性4.3.2在加載新數(shù)據(jù)時(shí)禁用約束檢查在加載新數(shù)據(jù)時(shí)禁用約束檢查為了避免約束檢查的開銷，有時(shí)候可能第5章實(shí)現(xiàn)數(shù)據(jù)完整性數(shù)據(jù)完整性的類型約束禁用約束默認(rèn)值和規(guī)則決定使用何種強(qiáng)制方法推薦操作第5章實(shí)現(xiàn)數(shù)據(jù)完整性數(shù)據(jù)完整性的類型使用默認(rèn)值和規(guī)則語法：創(chuàng)建：CREATEDEFAULT

默認(rèn)值

AS常量表達(dá)式 刪除：DROPDEFAULT

默認(rèn)值[,...n] 綁定一個(gè)默認(rèn)：sp_bindefault 解除默認(rèn)值綁定：

sp_unbindefault創(chuàng)建默認(rèn)值的注意事項(xiàng)列的默認(rèn)值必須符合此列上的任何規(guī)則或CHECK約束4.4使用默認(rèn)值和規(guī)則使用默認(rèn)值和規(guī)則語法：創(chuàng)建：CREATEDEFAULT默使用默認(rèn)和規(guī)則（續(xù)）規(guī)則：規(guī)則指定了能插入列的可接受的值。它確保數(shù)據(jù)在指定值域內(nèi)，匹配某個(gè)模式，或者匹配指定列表中的項(xiàng)。

語法：創(chuàng)建：CREATERULE

規(guī)則AS

條件表達(dá)式 刪除：DROPRULE

規(guī)則[,...n] 綁定規(guī)則：sp_bindrule

分離已綁定的規(guī)則：sp_unbindrule關(guān)于規(guī)則的注意事項(xiàng)規(guī)則定義可以包含任何在WHERE子句中有效的表達(dá)式一個(gè)列或者用戶定義數(shù)據(jù)類型只能被一個(gè)規(guī)則綁定4.4使用默認(rèn)和規(guī)則使用默認(rèn)和規(guī)則（續(xù)）規(guī)則：規(guī)則指定了能插入列的可接受的值。它第5章實(shí)現(xiàn)數(shù)據(jù)完整性數(shù)據(jù)完整性的類型約束禁用約束默認(rèn)值和規(guī)則決定使用何種強(qiáng)制方法推薦操作第5章實(shí)現(xiàn)數(shù)據(jù)完整性數(shù)據(jù)完整性的類型決定使用何種強(qiáng)制方法4.1決定使用何種強(qiáng)制方法應(yīng)綜合考慮功能性和性能開銷對于基本的完整性邏輯，例如有效值和維護(hù)表間的關(guān)系，最好使用聲明式完整性約束如果要維護(hù)復(fù)雜的、大量的、非主鍵或外鍵關(guān)系一部分的數(shù)據(jù)，必須使用觸發(fā)器或存儲過程數(shù)據(jù)完整性類型效果功能性性能開銷在數(shù)據(jù)修改之前或之后約束和表一起定義，數(shù)據(jù)在事務(wù)開始前驗(yàn)證，使得性能更好中低之前默認(rèn)和規(guī)則作為獨(dú)立的對象來實(shí)現(xiàn)數(shù)據(jù)完整性，可以與一個(gè)或多個(gè)表關(guān)聯(lián)低低之前觸發(fā)器提供了額外的功能性，例如層疊和復(fù)雜的應(yīng)用邏輯。任何修改必須被回滾高中高之后（除了INSTEADOF觸發(fā)器）數(shù)據(jù)類型、NULL/NOTNULL提供最底層的數(shù)據(jù)完整性。當(dāng)表創(chuàng)建時(shí)為每個(gè)列實(shí)現(xiàn)，數(shù)據(jù)在事務(wù)開始前驗(yàn)證低低之前決定使用何種強(qiáng)制方法4.1決定使用何種強(qiáng)制方法應(yīng)綜合考慮第5章實(shí)現(xiàn)數(shù)據(jù)完整性數(shù)據(jù)完整性的類型約束禁用約束默認(rèn)值和規(guī)則決定使用何種強(qiáng)制方法推薦操作第5章實(shí)現(xiàn)數(shù)據(jù)完整性數(shù)據(jù)完整性的類型推薦操作使用級聯(lián)引用完整性代替觸發(fā)器使用約束，因?yàn)樗鼈兪茿NSI兼容的4.6

推薦操作推薦操作使用級聯(lián)引用完整性代替觸發(fā)器使用約束，因?yàn)樗鼈兪茿目標(biāo)定義和使用DEFAULT和CHECK約束定義和使用PRIMARYKEY和FOREIGNKEY約束創(chuàng)建和使用SQLServer2000規(guī)則和默認(rèn)值練習(xí)1定義DEFAULT約束練習(xí)2定義CHECK約束練習(xí)3定義PRIMARYKEY約束練習(xí)4定義FOREIGNKEY約束可選創(chuàng)建默認(rèn)值和規(guī)則實(shí)驗(yàn)

實(shí)現(xiàn)數(shù)據(jù)完整性目標(biāo)實(shí)驗(yàn)實(shí)現(xiàn)數(shù)據(jù)完整性回顧學(xué)習(xí)完本章后，將能夠：描述數(shù)據(jù)完整性的類型描述強(qiáng)制數(shù)據(jù)完整性的方法決定使用何種約束和創(chuàng)建約束定義和使用DEFAULT、CHECK、PRIMARYKEY、UNIQUE和FOREIGNKEY約束禁用約束檢查描述和使用默認(rèn)值和規(guī)則決定使用何種數(shù)據(jù)完整性強(qiáng)制方法回顧學(xué)習(xí)完本章后，將能夠：第5章實(shí)現(xiàn)數(shù)據(jù)完整性數(shù)據(jù)完整性的類型約束禁用約束默認(rèn)值和規(guī)則決定使用何種強(qiáng)制方法推薦操作第5章實(shí)現(xiàn)數(shù)據(jù)完整性數(shù)據(jù)完整性的類型數(shù)據(jù)完整性的類型域完整性

(列)實(shí)體完整性

（行）參照完整性

(表之間)數(shù)據(jù)完整性的類型域完整性

(列)實(shí)體完整性（行）參照完整性數(shù)據(jù)完整性的類型4.1數(shù)據(jù)完整性的類型數(shù)據(jù)完整性指的是數(shù)據(jù)庫中存儲的數(shù)據(jù)的一致性和準(zhǔn)確性數(shù)據(jù)完整性的類型：域完整性、實(shí)體完整性、引用完整性域完整性域（或列）完整性是指對列指定一組有效的值并決定是否可為空值實(shí)體完整性實(shí)體（或表）完整性要求表中的所有行都有一個(gè)惟一的標(biāo)識符，稱為主鍵值引用完整性引用完整性確保主鍵（在被引用表中）和外鍵（在引用表中）之間的關(guān)系得到保持?jǐn)?shù)據(jù)完整性的類型4.1數(shù)據(jù)完整性的類型數(shù)據(jù)完整性第5章實(shí)現(xiàn)數(shù)據(jù)完整性數(shù)據(jù)完整性的類型約束禁用約束默認(rèn)值和規(guī)則決定使用何種強(qiáng)制方法推薦操作第5章實(shí)現(xiàn)數(shù)據(jù)完整性數(shù)據(jù)完整性的類型決定使用何種約束約束的類型約束4.2約束決定使用何種約束約束4.2約束決定使用何種約束4.2.1決定使用何種約束完整性類型約束類型描述域DEFAULT如果在INSERT語句中未顯式提供

值，則指定為列提供的值CHECK指定列中可接受的數(shù)據(jù)值REFERENTIAL(通常使用外鍵)基于其他表中的列的值，指定可接受的用于更新的數(shù)據(jù)值實(shí)體PRIMARYKEY惟一標(biāo)識每一列，確保用戶沒有輸入重復(fù)的值。同時(shí)創(chuàng)建一個(gè)索引以增強(qiáng)性能。不允許空值UNIQUE確保在非主鍵列中不輸入重復(fù)值，并創(chuàng)建一個(gè)索引以增強(qiáng)性能。允許空值引用FOREIGNKEY定義一列或多列的值與同表或其他表中主鍵的值匹配CHECK基于同表中其他列的值，指定列中可接受的數(shù)據(jù)值決定使用何種約束4.2.1決定使用何種約束完整性類型約束創(chuàng)建約束使用CREATETABLE或者ALTERTABLECREATETABLE是在創(chuàng)建表時(shí)創(chuàng)建約束ALTERTABLE是在一個(gè)已有的表上創(chuàng)建約束可以添加約束到已有數(shù)據(jù)的表上可添加單列或多列約束若約束應(yīng)用于單列，稱為列級約束若約束引用了多列，稱為表級約束，即使它并沒有引用表中的所有列4.3.2創(chuàng)建約束創(chuàng)建約束使用CREATETABLE或者ALTERT使用約束的注意事項(xiàng)可直接在表上創(chuàng)建、更改和刪除約束，而不必刪除并重建表應(yīng)該在應(yīng)用程序和事務(wù)內(nèi)建立錯(cuò)誤檢查邏輯，以測試是否違反了某個(gè)約束SQLServer里的約束只是“最后防線”當(dāng)給一個(gè)表添加約束的時(shí)候，SQLServer將檢查現(xiàn)有數(shù)據(jù)是否違反約束建議創(chuàng)建約束的時(shí)候指定名稱，否則系統(tǒng)將為約束自動產(chǎn)生一個(gè)復(fù)雜的名稱名稱必須惟一，且符合SQLServer標(biāo)識符的規(guī)則查看約束的信息系統(tǒng)存儲過程：sp_helpconstraint、sp_help信息模式視圖：check_constraints、referential_constraints、table_constraints系統(tǒng)表：syscomments、sysreferences、sysconstraints使用約束的注意事項(xiàng)可直接在表上創(chuàng)建、更改和刪除約束，而不必刪約束的類型DEFAULT約束CHECK約束PRIMARYKEY約束UNIQUE約束FOREIGNKEY約束級聯(lián)引用完整性4.2.2約束的類型約束的類型DEFAULT約束4.2.2約束的類型PRIMARYKEY約束PRIMARYKEY約束在表中定義了一個(gè)惟一標(biāo)識每一列的主鍵

語法：[CONSTRAINT約束名]PRIMARYKEY[CLUSTERED|NONCLUSTERED]{(列[,...n])}4.2.2.3PRIMARYKEY約束PRIMARYKEY約束PRIMARYKEY約束在表

PRIMARYKEY約束利用表中的一列或多列數(shù)據(jù)唯一地標(biāo)識某一行數(shù)據(jù)每個(gè)表只有一個(gè)PRIMARYKEY約束PRIMARYKEY約束的值必須是唯一的不允許有空值SQLServer中最多可定義16列作為主鍵PRIMARYKEY約束PRIMARYKEY約束利用表中的一列或多列數(shù)據(jù)唯一地標(biāo)直接在列名后增加關(guān)鍵字

PRIMARYKEY

CREATETABLEStudent (snochar(5)PRIMARYKEY,snamevarchar(20)notnull,

……

);

列級主鍵創(chuàng)建sc表，其主碼為（sno,cno）×???

CREATETABLEsc(snochar(5)PRIMARYKEY，cnochar(1)PRIMARYKEY，gradedecimal(4,1)));PRIMARYKEY約束直接在列名后增加關(guān)鍵字PRIMARYKEY列級主鍵創(chuàng)建在CREATETABLE語句各列定義的最后加:PRIMARYKEY(<屬性名表>)CREATETABLEsc(snochar(5)，cnochar(1)，gradedecimal(4,1),PRIMARYKEY(sno,cno));

CREATETABLEsc1(……….,

constraintPK_SCPRIMARYKEY(sno,cno));表級主鍵約束名PRIMARYKEY約束在CREATETABLE語句各列定義的最后加:CREA刪除表上已定義的主鍵

ALTERTABLESC1

DROPCONSTRAINTPK_SC;在沒有定義主鍵的表上，加上一個(gè)主鍵

ALTERTABLESC

ADDCONSTRAINTPK_SCprimarykey(sno,cno);刪除和添加主鍵刪除表上已定義的主鍵刪除和添加主鍵DEFAULT約束如果一個(gè)列的值在INSERT語句中沒有指定，DEFAULT約束將自動輸入一個(gè)值，可以是預(yù)先指定的常量、NULL或者一個(gè)系統(tǒng)函數(shù)運(yùn)行時(shí)的值

語法：

[CONSTRAINT約束名]DEFAULT約束表達(dá)式

創(chuàng)建DEFAULT約束的兩種方法創(chuàng)建一個(gè)默認(rèn)對象（CREATEDEFAULT），然后使用存儲過程sp_bindefault將默認(rèn)綁定到一個(gè)列CREATETABLE或ALTERTABLE時(shí)使用DEFAUTL約束4.2.2.1DEFAULT約束DEFAULT約束如果一個(gè)列的值在INSERT語句中沒DEFAULT約束（續(xù)）在創(chuàng)建表時(shí)使用default屬性4.2.2.1DEFAULT約束CREATETABLEuserInfo(...…countryvarchar(50)notnullDEFAULT‘China’,……)為已經(jīng)創(chuàng)建好的表添加default屬性USENorthwind

ALTERTABLEdbo.Customers

ADD

CONSTRAINTDF_contactnameDEFAULT'UNKNOWN'

FORContactName應(yīng)用DEFAULT約束的幾種情況DEFAULT約束（續(xù)）在創(chuàng)建表時(shí)使用default屬性4DEFAULT約束（續(xù)）應(yīng)用DEFAULT約束的注意事項(xiàng)允許使用常量、函數(shù)、系統(tǒng)提供的值：USER、CURRENT_USER、SESSION_USER、SYSTEM_USER及CURRENT_TIMESTAMP

例如：①DEFAULTUSER②DEFAULE(getdate())有可能會和CHECK約束沖突常量值外面可以加或者不加括號，字符或者日期常量必須加上單引號或雙引號4.2.2.1DEFAULT約束忽略，

不顯式地插入值插入NULL插入DEFAULT無默認(rèn)值有默認(rèn)值無默認(rèn)值有默認(rèn)值無默認(rèn)值有默認(rèn)值NULLNULL默認(rèn)值NULLNULLNULL默認(rèn)值NOTNULL錯(cuò)誤默認(rèn)值錯(cuò)誤錯(cuò)誤錯(cuò)誤默認(rèn)值DEFAULT約束（續(xù)）應(yīng)用DEFAULT約束的注意事CHECK約束限制輸入到指定列的值只能為某些特定值

語法：[CONSTRAINT約束名]CHECK(邏輯表達(dá)式)

兩種強(qiáng)制域完整性的方法：CHECK約束和規(guī)則CHECK約束定義了一個(gè)表達(dá)式，若數(shù)據(jù)修改語句使得表達(dá)式值為FALSE的話，將拒絕語句執(zhí)行規(guī)則的功能和CHECK約束基本相同，除了語法不同，能力稍弱。規(guī)則可定義一次，然后對多個(gè)列分別綁定；而CHECK約束則需要對每個(gè)列定義。但CHECK約束的功能略強(qiáng)一些（例如引用同行中其他列的值和調(diào)用系統(tǒng)函數(shù)等）4.2.2.2CHECK約束CHECK約束限制輸入到指定列的值只能為某些特定值4.2.CHECK約束（續(xù)）應(yīng)用CHECK約束的幾種情況4.2.2.2CHECK約束USENorthwind

ALTERTABLEdbo.Employees

ADD

CONSTRAINTCK_birthdate

CHECK(BirthDate>'01-01-1900'ANDBirthDate<getdate())在現(xiàn)有表中創(chuàng)建CHECK約束CREATETABLEuserInfo(...…birthdayvarchar(50)notnullCONSTRAINTck_birthdayCHECK(BirthDate>'01-01-1900'ANDBirthDate<getdate()),)在創(chuàng)建表時(shí)使用CHECK約束CHECK約束（續(xù)）應(yīng)用CHECK約束的幾種情況4.2.2在創(chuàng)建SC表時(shí)定義CHECK約束

CreateTableSC (snochar(5)notnull, cnochar(1)notnull, gradedecimal(4,1) primarykey(sno,cno));

創(chuàng)建CHECK約束列級CHECK約束check(grade>=0andgrade<=100),在創(chuàng)建SC表時(shí)定義CHECK約束創(chuàng)建CHECK約束列級CCreateTableSC (snochar(5)notnull, cnochar(1)notnull, gradedecimal(4,1),primarykey(sno,cno),check(grade>=0andgrade<=100));CreateTableSC(……..,

constraintchk_gmkcheck(grade>=0andgrade<=100));

創(chuàng)建CHECK約束表級CHECK約束CreateTableSC創(chuàng)建CHECK約束表級CHCreateTableStudent (snochar(5)notnull, birthdatedatetimenotnull schooldatedatetimenotnull,primarykey(sno),

);

創(chuàng)建CHECK約束表級CHECK約束check(birthdate<getdate()),check(birthdate<getdate()),列級CHECK約束CreateTableStudent創(chuàng)建CHECK約刪除SC表中的CHECK約束ALTERTABLESC

DROPCONSTRAINTchk_gmk向SC表中添加CHECK約束ALTERTABLESC

ADDCONSTRAINTchk_gmk

check(gmark>=0andgmark<=100)

添加和刪除CHECK約束刪除SC表中的CHECK約束添加和刪除CHECK約束CHECK約束（續(xù)）應(yīng)用CHECK約束的注意事項(xiàng)一個(gè)表可以定義多個(gè)CHECK約束，一個(gè)列上只允許創(chuàng)建一個(gè)列級CHECK約束。列級CHECK約束只能引用被約束的列，表級CHECK約束只能引用同一表中的列。當(dāng)列上存在規(guī)則和一個(gè)或多個(gè)CHECK約束時(shí)，將驗(yàn)證所有限制。4.2.2.2CHECK約束CHECK約束（續(xù)）應(yīng)用CHECK約束的注意事項(xiàng)4.2.UNIQUE約束UNIQUE約束指明列中的任意兩行不能有相同的值

語法：[CONSTRAINT約束名]UNIQUE[CLUSTERED|NONCLUSTERED]{(列[,...n])}

應(yīng)用UNIQUE約束的注意事項(xiàng)允許空值的列上定義UNIQUE約束在一個(gè)表上允許多個(gè)UNIQUE約束可在一個(gè)或者多個(gè)列上定義是通過一個(gè)惟一索引強(qiáng)制約束的4.2.2..4UNIQUE約束UNIQUE約束UNIQUE約束指明列中的任意兩行不能有UNIQUE約束（續(xù)）PRIMARYKEY和UNIQUE約束聲明PRIMARYKEY或UNIQUE約束的結(jié)果只是自動創(chuàng)建了一個(gè)指定列上的惟一索引，通過惟一索引來確保值的惟一性可空性PRIMARYKEY的各個(gè)列必須聲明為NOTNULL，而UNIQUE的各個(gè)列可以聲明為允許NULL值在惟一索引中，認(rèn)為所有的NULL值是相等的索引的屬性PRIMARYKEY約束所創(chuàng)建的惟一索引默認(rèn)為CLUSTERED，除非表中另外一列已經(jīng)聲明為CLUSTEREDUNIQUE約束所創(chuàng)建的惟一索引默認(rèn)為NONCLUSTERED選擇鍵保持鍵的長度盡可能短，必要時(shí)可另外創(chuàng)建一個(gè)代替鍵不要使用float或real數(shù)據(jù)類型的列作為主鍵UNIQUE約束（續(xù)）PRIMARYKEY和UNIQ在創(chuàng)建表時(shí)定義UNIQUE約束CREATETABLEtest2

(even_idintprimarykey,even_namechar(20),even_typechar(20),even_timedatetime,

)

CONSTRAINTUNIQ_EVENUNIQUE(even_type,even_time)要求：在even_type和even_time上共同建立惟一約束表級UNIQUE約束創(chuàng)建UNIQUE約束在創(chuàng)建表時(shí)定義UNIQUE約束CONSTRAINTUNIQ向test1表中的tname

列添加UNIQUE約束

ALTERTABLEtest1

ADDCONSTRAINTUNIQ_TNAME

UNIQUE(tname)刪除表上已定義的UNIQUE約束ALTERTABLEtest1

DROPCONSTRAINTUNIQ_TNAME

添加和刪除UNIQUE約束向test1表中的tname列添加UNIQUE約束FOREIGNKEY約束FOREIGNKEY約束：定義到同表或其他表中具有PRIMARYKEY或者UNIQUE約束的列的引用

語法： [CONSTRAINT約束名][FOREIGNKEY][(列[,…n])]

REFERENCES引用表[(引用列[,…n])]具有FOREIGNKEY約束的列的取值范圍只能是被引用的列的列值4.2.2.5FOREIGNKEY約束FOREIGNKEY約束FOREIGNKEY約束：定FOREIGNKEY約束（續(xù)）應(yīng)用FOREIGNKEY約束的注意事項(xiàng)FOREIGNKEY子句中指定的列的個(gè)數(shù)和數(shù)據(jù)類型必須和REFERENCES子句中指定的列的個(gè)數(shù)和數(shù)據(jù)類型匹配并不自動創(chuàng)建索引修改數(shù)據(jù)的時(shí)候，用戶必須在被FOREIGNKEY約束引用的表上具有SELECT或REFERENCES權(quán)限若引用的是同表中的列，那么可只用REFERENCES子句而省略FOREIGNKEY子句4.2.2.5FOREIGNKEY約束FOREIGNKEY約束（續(xù)）應(yīng)用FOREIGNKEFOREIGNKEY約束（續(xù)）應(yīng)用FOREIGNKEY約束的幾種情況：創(chuàng)建表的同時(shí)創(chuàng)建FOREIGNKEY約束

createtablestuscore(idintnotnull,courseIDintnotnull,scoreintnotnull,constraintfk_student_stuscoreforeignkey(id)referencesstudent(id))FOREIGNKEY約束（續(xù)）應(yīng)用FOREIGNKEYFOREIGNKEY約束（續(xù)）創(chuàng)建完表后通過修改表增加FOREIGNKEY約束使用FOREIGNKEY約束，確保Orders表中的客戶標(biāo)識與Customers表中的有效的客戶標(biāo)識相關(guān)聯(lián)4.2.2.5FOREIGNKEY約束altertablestuscoreaddconstraintfk_student_stuscoreforeignkey(id)referencesstudent(id)FOREIGNKEY約束（續(xù)）創(chuàng)建完表后通過修改表增加FFOREIGNKEY約束（續(xù)）FOREIGNKEY約束的刪除altertablestuscoredropconstraintfk_student_stuscoreFOREIGNKEY約束（續(xù)）FOREIGNKEY約級聯(lián)引用完整性4.2.2.6級聯(lián)引用完整性FOREIGNKEY約束包含一個(gè)CASCADE選項(xiàng)，允許對一個(gè)定義了UNIQUE或者PRIMARYKEY約束的列的值的修改自動傳播到引用它的外鍵上，這個(gè)動作稱為級聯(lián)引用完整性

語法： [CONSTRAINT約束名][FOREIGNKEY][(列[,…n])]

REFERENCES引用表[(引用列[,…n])]

[ONDELETE{CASCADE|NOACTION}]

[ONUPDATE{CASCADE|NOACTION}]NOACTION：任何企圖刪除或者更新被其他表的外鍵所引用的鍵都將引發(fā)一個(gè)錯(cuò)誤，對數(shù)據(jù)的改變會被回滾。NOACTION是默認(rèn)值CASCADE：若父表中的行變化了，則引用表中相應(yīng)的行也自動變化級聯(lián)引用完整性4.2.2.6級聯(lián)引用完整性FOREIGN級聯(lián)引用完整性（續(xù)）4.2.2.6級聯(lián)引用完整性應(yīng)用CASCADE選項(xiàng)的注意事項(xiàng)可在多個(gè)具有引用關(guān)系的表之間組合CASCADE和NOACTION選項(xiàng)。若SQLServer遇到NOACTION，則中斷并回滾所有相關(guān)的CASCADE動作CASCADE選項(xiàng)不能對定義為rowversion數(shù)據(jù)類型的外鍵或主鍵列指定級聯(lián)引用完整性（續(xù)）4.2.2.6級聯(lián)引用完整性應(yīng)用CA第5章實(shí)現(xiàn)數(shù)據(jù)完整性數(shù)據(jù)完整性的類型約束禁用約束默認(rèn)值和規(guī)則決定使用何種強(qiáng)制方法推薦操作第5章實(shí)現(xiàn)數(shù)據(jù)完整性數(shù)據(jù)完整性的類型禁用約束禁用現(xiàn)有數(shù)據(jù)上的約束檢查在加載新數(shù)據(jù)時(shí)禁用約束檢查4.3禁用約束禁用約束禁用現(xiàn)有數(shù)據(jù)上的約束檢查4.3禁用約束禁用現(xiàn)有數(shù)據(jù)上的約束檢查當(dāng)在表上添加約束的時(shí)候，我們可以禁用對已有數(shù)據(jù)的約束檢查

語法：ALTERTABLE表名

[WITHCHECKWITHNOCHECK]ADDCONSTRAINT約束名