數(shù)據(jù)庫概論-完整性控制

數(shù)據(jù)庫概論第四章完整性控制約束類型primarykeyuniqueforeignkeycheckdefault查看約束：sp_helpconstraintSysconstraints系統(tǒng)表列名數(shù)據(jù)類型描述constidint約束號idint擁有該約束的表IDcolidsmallint在其上定義約束的列ID，如果是表約束則為0statusint位圖指示狀態(tài)?？赡艿闹蛋ǎ?=PRIMARYKEY約束

2=UNIQUEKEY約束3=FOREIGNKEY約束4=CHECK約束

5=DEFAULT約束

16=列級約束32=表級約束自定制sp_helpconstraintselect OBJECT_NAME(constid)'Constraintname',constid'ConstraintID', case(status&0xF) when1then'Primarykey' when2then'Unique' when3then'ForeignKey' when4then'Check' when5then'default' end'ConstraintType', case(status&0x30) when0x10then'Column' when0x20then'Table' else'NA' end'level'fromsysconstraintswhereid=OBJECT_ID('SC')primarykey與unique都是通過唯一性索引來支持它們有何區(qū)別？一個(gè)帶有唯一性聲明的屬性上有多少個(gè)null?createtablet1( col1intunique, col2int, col3int, unique(col2,col3))Col1Col2Col313425nullnull6null如何表達(dá)：每位老師在一定時(shí)間內(nèi)只能負(fù)責(zé)一個(gè)項(xiàng)目？primarykey與unique的背后EXECsp_helpindext1constraint_typeconstraint_nameconstraint_keysUNIQUE(non-clustered)UQ__tt__440B1D61col2,col3UNIQUE(non-clustered)UQ__tt__44FF419Acol1EXECsp_helpconstraintt1index_nameindex_descriptionindex_keysUQ__tt__440B1D61nonclustered,unique,uniquekeylocatedonPRIMARYcol2,col3UQ__tt__44FF419Anonclustered,unique,uniquekeylocatedonPRIMARYcol1foreignkey作為主碼的關(guān)系稱為基本關(guān)系，作為外碼的關(guān)系稱為依賴關(guān)系如何保證參照關(guān)系的完整性？僅僅監(jiān)視依賴關(guān)系上的修改操作是不完備的如果基本關(guān)系中的行可以隨意修改又會(huì)如何？dnodnamed1CSd2MATHsnosnamednos1Tomd1s2Bobd2dnodnamed2MATHsnosnamednos1Tomd1s2Bobd2deleted1foreignkey：三種定義方式RESTRICT方式：只有當(dāng)依賴關(guān)系中沒有一個(gè)外碼值與要?jiǎng)h除（更新）的基本關(guān)系的主碼值相對應(yīng)時(shí)，才可以刪除（更新）該行（的主碼），否則系統(tǒng)拒絕此刪除操作CASCADE方式：將依賴關(guān)系中所有外碼值與基本關(guān)系中要?jiǎng)h除的主碼值所對應(yīng)的行一起刪除（將依賴關(guān)系中所有與基本關(guān)系中要修改的主碼值所對應(yīng)的外碼值一起修改為新值）SETNULL方式：刪除（更新）基本關(guān)系中的行時(shí)，將依賴關(guān)系中與基本關(guān)系中被刪（更新）主碼值相對應(yīng)的外碼值置為空值外碼的三種定義方式效果示例deleted1ondeleteRESTRICTondeleteSETNULLondeleteCASCADESQLServer外碼定義方式：NOACTION、CASCADE、SETDEFAULTdnodnamed1CSd2MATHsnosnamednos1Tomd1s2Bobd2dnodnamed1CSd2MATHsnosnamednos1Tomd1s2Bobd2dnodnamed2MATHsnosnamednos2Bobd2dnodnamed2MATHsnosnamednos1Tomnulls2Bobd2全局約束涉及多個(gè)屬性間的或多個(gè)關(guān)系間的聯(lián)系(sno char(8),cno char(10),grade smallint,primaykey(sno,cno),check(snoin(selectsnofromS)),check(cnoin(selectcnofromC)))如果S中刪除行，不會(huì)觸發(fā)check檢查，只有SC表的更新才會(huì)觸發(fā)上述的check定義是否等價(jià)于外碼？約束命名及其定義constraint約束名<約束條件>示例

snochar(8)constraintS_PK

primarykey約束的撤消與添加撤消用altertable…dropconstraint…添加用altertable…addconstraint…示例

alter

tableSdropconstraintS_PK

altertableSCaddconstraintSC_CHECK

check(snoinselectsnofromS)

約束檢查Table1Col1_1(PK)Col1_2(FK)12Table2Col2_1(PK)Col2_2(FK)21相互參照的表，如何插入行？employeee#(PK)enamemgr(FK)e1tome2e2jerrye3e3bobnull自參照的表標(biāo)準(zhǔn)SQL中的延遲約束deferredconstraints將多個(gè)更新操作語句放入一個(gè)事務(wù)，在提交時(shí)才檢查約束如何設(shè)置延遲約束？在約束創(chuàng)建時(shí)：

[not]deferrable initiallydeferred[immediate]對現(xiàn)有約束：

setconstraint約束名deferried延遲約束檢查createtableemp( eno char(10)primarykey, ename char(20), mgr char(10)constraintFK_Constraintforeignkey referencesemp(eno)

deferrableinitiallyimmediate設(shè)置延遲約束：setconstraintFK_ConstraintdeferriedSQLServer的約束開關(guān)添加未驗(yàn)證的約束(withnocheck)createtablet1(col_aINT)insertintot1VALUES(-1)altertablet1withnocheck

addconstraintskip_checkcheck(col_a>1)insertintot1VALUES(-2)禁用并重新啟用一個(gè)約束(nocheck)

altertablet1nocheck

constraintskip_checkinsertintot1VALUES(-2)altertablet1check

constraintskip_checkdbcccheckconstraints函數(shù)約束noGap(nointprimarykey,namevarchar(10))createfunction

fn_noGap_minUnusedKey()returnsintasbegin if(notexists(select*from

noGap

whereno=1))return1 declare@min_unused_no

int select @min_unused_no=min(no+1)

from

noGapNO1 where notexists (select * from

noGapNO2 where NO2.no=NO1.no+1) return@min_unused_noendaltertablenoGapaddconstraintDF_no

default(fn_noGap_minUnusedKey())forno函數(shù)約束要求每門課的選課人數(shù)不能多于100人。createfunction

fn_registerCount(@cno

varchar

)returnsintasbeginreturn (select

count(*)

from sc where cno=@cno)End為表sc列添加如下check約束：altertablesc addconstraint