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

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

(列)實體完整性

（行）參照完整性

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

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

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

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

PRIMARYKEY

CREATETABLEStudent (snochar(5)PRIMARYKEY,

sname

varchar(20)notnull,

……

);

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

CREATETABLEsc(snochar(5)PRIMARYKEY，cnochar(1)PRIMARYKEY，gradedecimal(4,1)));PRIMARYKEY約束在CREATETABLE語句各列定義的最后加:PRIMARYKEY(<屬性名表>)CREATETABLEsc(snochar(5)，

cnochar(1)，gradedecimal(4,1),PRIMARYKEY(sno,cno));

CREATETABLEsc1(……….,

constraintPK_SCPRIMARYKEY(sno,cno));表級主鍵約束名PRIMARYKEY約束刪除表上已定義的主鍵

ALTERTABLESC1

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

ALTERTABLESC

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

語法：

[CONSTRAINT約束名]DEFAULT約束表達式

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

ALTERTABLEdbo.Customers

ADD

CONSTRAINTDF_contactnameDEFAULT'UNKNOWN'

FORContactName應用DEFAULT約束的幾種情況DEFAULT約束（續(xù)）應用DEFAULT約束的注意事項允許使用常量、函數(shù)、系統(tǒng)提供的值：USER、CURRENT_USER、SESSION_USER、SYSTEM_USER及CURRENT_TIMESTAMP

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

不顯式地插入值插入NULL插入DEFAULT無默認值有默認值無默認值有默認值無默認值有默認值NULLNULL默認值NULLNULLNULL默認值NOTNULL錯誤默認值錯誤錯誤錯誤默認值CHECK約束限制輸入到指定列的值只能為某些特定值

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

兩種強制域完整性的方法：CHECK約束和規(guī)則CHECK約束定義了一個表達式，若數(shù)據(jù)修改語句使得表達式值為FALSE的話，將拒絕語句執(zhí)行規(guī)則的功能和CHECK約束基本相同，除了語法不同，能力稍弱。規(guī)則可定義一次，然后對多個列分別綁定；而CHECK約束則需要對每個列定義。但CHECK約束的功能略強一些（例如引用同行中其他列的值和調(diào)用系統(tǒng)函數(shù)等）4.2.2.2CHECK約束CHECK約束（續(xù)）應用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)建表時使用CHECK約束在創(chuàng)建SC表時定義CHECK約束

CreateTableSC (snochar(5)notnull,

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

創(chuàng)建CHECK約束列級CHECK約束check(grade>=0andgrade<=100),CreateTableSC (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約束CreateTableStudent (snochar(5)notnull,

birthdate

datetimenotnull

schooldate

datetimenotnull,primarykey(sno),

);

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

DROPCONSTRAINT

chk_gmk向SC表中添加CHECK約束ALTERTABLESC

ADDCONSTRAINT

chk_gmk

check(gmark>=0andgmark<=100)

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

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

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

列添加UNIQUE約束

ALTERTABLEtest1

ADDCONSTRAINTUNIQ_TNAME

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

DROPCONSTRAINTUNIQ_TNAME

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

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

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

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

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

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

[ONDELETE{CASCADE|NOACTION}]

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

語法：ALTERTABLE表名

[WITHCHECK

WITHNOCHECK]ADDCONSTRAINT約束名

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

4.3.1禁用現(xiàn)有數(shù)據(jù)上的約束檢查禁用現(xiàn)有數(shù)據(jù)上的約束檢查（續(xù)）對已有數(shù)據(jù)禁用約束檢查的注意事項只能禁用CHECK和FOREIGNKEY約束當為一個已有數(shù)據(jù)的表添加CHECK或FOREIGNKEY約束的時候，使用WITHNOCHECK選項來禁用對已有數(shù)據(jù)的約束檢查4.3.1禁用現(xiàn)有數(shù)據(jù)上的約束檢查在加載新數(shù)據(jù)時禁用約束檢查為了避免約束檢查的開銷，有時候可能希望禁用約束已經(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ù)時禁用約束檢查第5章實現(xiàn)數(shù)據(jù)完整性數(shù)據(jù)完整性的類型約束禁用約束默認值和規(guī)則決定使用何種強制方法推薦操作使用默認值和規(guī)則語法：創(chuàng)建：CREATEDEFAULT

默認值

AS常量表達式 刪除：DROPDEFAULT

默認值[,...n] 綁定一個默認：sp_bindefault 解除默認值綁定：

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

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

規(guī)則AS

條件表達式 刪除：DROPRULE

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