第12章-約束-《SQLServer2005程序設(shè)計(jì)語言》

SQLServer2005數(shù)據(jù)庫程序設(shè)計(jì)第12章約束第12章約束12.1約束概述12.2設(shè)計(jì)約束12.3主鍵約束12.4外鍵約束12.5UNIQUE約束12.6CHECK約束12.7DEFAULT約束12.8禁用約束12.1.1數(shù)據(jù)完整性數(shù)據(jù)完整性指的是數(shù)據(jù)庫中存儲的數(shù)據(jù)的一致性和準(zhǔn)確性數(shù)據(jù)完整性的類型：域完整性、實(shí)體完整性、引用完整性實(shí)體完整性實(shí)體（或表）完整性要求表中的所有行都有一個(gè)惟一的標(biāo)識符，稱為主鍵值域完整性域（或列）完整性是指對列指定一組有效的值并決定是否可為空值引用完整性引用完整性確保主鍵（在被引用表中）和外鍵（在引用表中）之間的關(guān)系得到保持用戶定義完整性12.1.2約束的概念和類型完整性類型約束類型描述域DEFAULT指定列的默認(rèn)值CHECK指定列的允許值FOREIGNKEY指定必須存在值的列NULL指定是否允許為NULL實(shí)體PRIMARYKEY唯一標(biāo)識每一行UNIQUE防止非主鍵重復(fù)引用FOREIGNKEY定義值與同一個(gè)表或另一個(gè)表的主鍵值匹配的一列或多列組合CHECK指定根據(jù)同一個(gè)表中其他列的值可在列中接受的數(shù)據(jù)值域（或列）完整性實(shí)體（或行）完整性引用完整性（在表或者同一個(gè)表的列之間）12.1.2約束的概念和類型實(shí)施數(shù)據(jù)完整性的選項(xiàng)機(jī)制描述數(shù)據(jù)類型定義可存儲在列中的數(shù)據(jù)類型規(guī)則定義可插入到列中的可接受的值默認(rèn)值定義如果值沒有指定的時(shí)候列的值約束定義數(shù)據(jù)庫引擎如何強(qiáng)制數(shù)據(jù)完整性觸發(fā)器定義當(dāng)表被修改的時(shí)候自動執(zhí)行的代碼XML構(gòu)架定義XML文檔的命名空間、結(jié)構(gòu)和可接受內(nèi)容

強(qiáng)制數(shù)據(jù)完整性聲明式數(shù)據(jù)完整性作為對象定義的一部分來定義數(shù)據(jù)必須達(dá)到的標(biāo)準(zhǔn)SQLServer自動強(qiáng)制完整性通過使用約束、默認(rèn)和規(guī)則來實(shí)現(xiàn)過程式數(shù)據(jù)完整性在腳本中定義數(shù)據(jù)必須達(dá)到的標(biāo)準(zhǔn)通過使用觸發(fā)器和存儲過程來實(shí)現(xiàn)可在客戶端或服務(wù)器用其他編程語言和工具來實(shí)現(xiàn)強(qiáng)制數(shù)據(jù)完整性的首選方法使用約束的注意事項(xiàng)可直接在表上創(chuàng)建、更改和刪除約束，而不必刪除并重建表應(yīng)該在應(yīng)用程序和事務(wù)內(nèi)建立錯誤檢查邏輯，以測試是否違反了某個(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第12章約束12.1約束概述12.2設(shè)計(jì)約束12.3主鍵約束12.4外鍵約束12.5UNIQUE約束12.6CHECK約束12.7DEFAULT約束12.8禁用約束12.2設(shè)計(jì)約束使用CREATETABLE或者ALTERTABLECREATETABLE是在創(chuàng)建表時(shí)創(chuàng)建約束ALTERTABLE是在一個(gè)已有的表上創(chuàng)建約束可以添加約束到已有數(shù)據(jù)的表上可添加單列或多列約束若約束應(yīng)用于單列，稱為列級約束若約束引用了多列，稱為表級約束，即使它并沒有引用表中的所有列第12章約束12.1約束概述12.2設(shè)計(jì)約束12.3主鍵約束12.4外鍵約束12.5UNIQUE約束12.6CHECK約束12.7DEFAULT約束12.8禁用約束12.3主鍵約束PRIMARYKEY約束在表中定義了一個(gè)惟一標(biāo)識每一列的主鍵 語法：[CONSTRAINT約束名]PRIMARYKEY[CLUSTERED|NONCLUSTERED]{(列[,...n])}應(yīng)用PRIMARYKEY約束的注意事項(xiàng)每張表只能有一個(gè)PRIMARYKEY約束輸入的值必須是惟一的不允許空值將在指定列上創(chuàng)建惟一索引可以指定聚集索引和非聚集索引（如果先前不存在聚集索引，那么默認(rèn)是聚集索引）PRIMARYKEY約束示例在Customers表上創(chuàng)建PRIMARYKEY約束，指明表的主鍵值是CustomerID，并且創(chuàng)建非聚集索引以強(qiáng)制約束USENorthwindALTERTABLEdbo.Customers

ADDCONSTRAINTPK_CustomersPRIMARYKEYNONCLUSTERED(CustomerID)12.3主鍵約束第12章約束12.1約束概述12.2設(shè)計(jì)約束12.3主鍵約束12.4外鍵約束12.5UNIQUE約束12.6CHECK約束12.7DEFAULT約束12.8禁用約束12.4外鍵約束FOREIGNKEY約束：定義到同表或其他表中具有PRIMARYKEY或者UNIQUE約束(即唯一性)的列的引用

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

REFERENCES引用表

[(引用列

[,…n])]具有FOREIGNKEY約束的列的取值范圍只能是被引用的列的列值應(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子句12.4外鍵約束FOREIGNKEY約束的示例使用FOREIGNKEY約束，確保Orders表中的客戶標(biāo)識與Customers表中的有效的客戶標(biāo)識相關(guān)聯(lián)USENorthwindALTERTABLEdbo.Orders

ADDCONSTRAINTFK_Orders_CustomersFOREIGNKEY(CustomerID)REFERENCESdbo.Customers(CustomerID)12.4外鍵約束級聯(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è)錯誤，對數(shù)據(jù)的改變會被回滾。NOACTION是默認(rèn)值CASCADE：若父表中的行變化了，則引用表中相應(yīng)的行也自動變化級聯(lián)引用完整性Cascade選項(xiàng)UPDATE行為DELETE行為NOACTION

(Default)引發(fā)錯誤；回滾操作CASCADE在引用表中更新外鍵在引用表中刪除行SETNULL在引用表中設(shè)置外鍵為NULLSETDEFAULT在引用表中設(shè)置外鍵為DEFAULT值FOREIGNKEY約束包含一個(gè)CASCADE選項(xiàng)級聯(lián)引用完整性（續(xù)）應(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ù)類型的外鍵或主鍵列指定示例向order表中添加FOREIGNKEY約束，該表級聯(lián)任何對customer中cust_id的更新，但是不允許在customer中刪除任何被引用的行CREATETABLEorders(order_id

intNOTNULLIDENTITYPRIMARYKEY

cust_id

intNOTNULLREFERENCES

Customer(cust_id)ONUPDATECASCADEONDELETENOACTION)

級聯(lián)引用完整性（續(xù)）第12章約束12.1約束概述12.2設(shè)計(jì)約束12.3主鍵約束12.4外鍵約束12.5UNIQUE約束12.6CHECK約束12.7DEFAULT約束12.8禁用約束12.5UNIQUE約束UNIQUE約束指明列中的任意兩行不能有相同的值

語法：[CONSTRAINT約束名]UNIQUE[CLUSTERED|NONCLUSTERED]{(列[,...n])}應(yīng)用UNIQUE約束的注意事項(xiàng)允許一個(gè)空值在一個(gè)表上允許多個(gè)UNIQUE約束可在一個(gè)或者多個(gè)列上定義是通過一個(gè)惟一索引強(qiáng)制約束的PRIMARYKEY和UNIQUE約束聲明PRIMARYKEY或UNIQUE約束的結(jié)果只是自動創(chuàng)建了一個(gè)指定列上的惟一索引?？煽招訮RIMARYKEY的各個(gè)列必須聲明為NOTNULL，而UNIQUE的各個(gè)列可以聲明為允許NULL值在惟一索引中，認(rèn)為所有的NULL值是相等的索引的屬性PRIMARYKEY約束所創(chuàng)建的惟一索引默認(rèn)為CLUSTERED，除非表中另外一列已經(jīng)聲明為CLUSTEREDUNIQUE約束所創(chuàng)建的惟一索引默認(rèn)為NONCLUSTERED選擇鍵不要使用float或real數(shù)據(jù)類型的列作為主鍵12.5UNIQUE約束UNIQUE約束示例在Suppliers表的公司名列上創(chuàng)建UNIQUE約束USENorthwindALTERTABLEdbo.Suppliers

ADDCONSTRAINTU_CompanyName

UNIQUENONCLUSTERED(CompanyName)12.5UNIQUE約束第12章約束12.1約束概述12.2設(shè)計(jì)約束12.3主鍵約束12.4外鍵約束12.5UNIQUE約束12.6CHECK約束12.7DEFAULT約束12.8禁用約束12.6CHECK約束限制輸入到指定列的值只能為某些特定值

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

兩種強(qiáng)制域完整性的方法：CHECK約束和規(guī)則CHECK約束定義了一個(gè)表達(dá)式，若數(shù)據(jù)修改語句使得表達(dá)式值為FALSE的話，將拒絕語句執(zhí)行規(guī)則的功能和CHECK約束基本相同，除了語法不同，能力稍弱。規(guī)則是為了向下兼容而保留的規(guī)則可定義一次，然后對多個(gè)列分別綁定；而CHECK約束則需要對每個(gè)列定義。但CHECK約束的功能略強(qiáng)一些（例如引用同行中其他列的值和調(diào)用系統(tǒng)函數(shù)等）CHECK約束示例為Employees表中的BrithDate

增加CHECK約束，使出生日期處于可接受的日期范圍內(nèi)USENorthwind

ALTERTABLEdbo.Employees

ADD

CONSTRAINTCK_birthdate

CHECK(BirthDate>'01-01-1900'ANDBirthDate<getdate())12.6CHECK約束使用規(guī)則規(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ī)則綁定使用規(guī)則例：創(chuàng)建規(guī)則CREATETABLEcust_sample(cust_id

intPRIMARYKEY,

cust_namechar(50));GOCREATERULEid_chkAS@idBETWEEN0and10000;綁定規(guī)則sp_bindrule

id_chk,'cust_sample.cust_id';GOsp_unbindrule'cust_sample.cust_id';例：創(chuàng)建規(guī)則綁定規(guī)則EXECsp_bindrule'score_rule',‘SC.score'使用規(guī)則CREATERULEscore_rule

AS@score>=0AND@score<=100

關(guān)于規(guī)則的注意事項(xiàng)創(chuàng)建的規(guī)則對先前已經(jīng)存在于數(shù)據(jù)庫中的數(shù)據(jù)無效一個(gè)列或者用戶定義數(shù)據(jù)類型只能被一個(gè)規(guī)則綁定規(guī)則表達(dá)式的類型必須和列的數(shù)據(jù)類型兼容規(guī)則定義可以包含任何在WHERE子句中有效的表達(dá)式如果列同時(shí)有默認(rèn)值和規(guī)則與之關(guān)聯(lián)，則默認(rèn)值必須滿足規(guī)則的定義，與規(guī)則沖突的默認(rèn)值不能插入列使用規(guī)則第12章約束12.1約束概述12.2設(shè)計(jì)約束12.3主鍵約束12.4外鍵約束12.5UNIQUE約束12.6CHECK約束12.7DEFAULT約束12.8禁用約束12.7DEFAULT約束如果一個(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約束應(yīng)用DEFAULT約束的注意事項(xiàng)DEFAULT約束創(chuàng)建時(shí)將檢查表中的現(xiàn)存數(shù)據(jù)DEFAULT約束只對INSERT語句有效每列只能定義一個(gè)DEFAULT約束有可能會和CHECK約束沖突為具有PRIMARYKEY或UNIQUE約束的列指定默認(rèn)值是沒有意義的常量值外面可以加或者不加括號，字符或者日期常量必須加上單引號或雙引號12.7DEFAULT約束使用默認(rèn)值默認(rèn)值和規(guī)則是可以綁定到一個(gè)或多個(gè)列或用戶定義數(shù)據(jù)類型的對象，使得可以一次定義，多次使用。默認(rèn)值：若插入數(shù)據(jù)的時(shí)候沒有指定某列的值，則此列綁定的默認(rèn)值將提供一個(gè)默認(rèn)值。

語法：創(chuàng)建：CREATEDEFAULT默認(rèn)值A(chǔ)S常量表達(dá)式 刪除：DROPDEFAULT默認(rèn)值[,...n]

綁定一個(gè)默認(rèn)： sp_bindefault

解除默認(rèn)值綁定：sp_unbindefault使用默認(rèn)值創(chuàng)建默認(rèn)值的注意事項(xiàng)列的默認(rèn)值必須符合綁定到此列上的任何規(guī)則列的默認(rèn)值必須符合此列上的任何CHECK約束不能為已有默認(rèn)綁定的列或用戶定義數(shù)據(jù)類型創(chuàng)建DEFAULT約束createtabletable2(snochar(10),snamevarchar(10),sexchar(2))goaltertabletable2addconstraintDF_Snamedefault'UNKNOWN'forsname/*或者 createdefaultDF_Snameas'UNKNOWN'

sp_bindefaultDF_Sname,'table2.sname‘ */GOaltertabletable2addconstraintDF_SEXdefault'男'forsex/*或者 createdefaultDF_SEXas'男'

sp_bindefaultDF_SEX,'table2.sex‘ */insertintotable2defaultvaluesinsertintotable2values('0621010001','張三',default)第12章約束12.1約束概述12.2設(shè)計(jì)約束12.3主鍵約束12.4外鍵約束12.5UNIQUE約束12.6CHECK約束12.7DEFAULT約束12.8禁用約束12.8禁用約束禁用現(xiàn)有數(shù)據(jù)上的約束檢查在加載新數(shù)據(jù)時(shí)禁用約束檢查禁用現(xiàn)有數(shù)據(jù)上的約束檢查當(dāng)在表上添加約束的時(shí)候，我們可以禁用對已有數(shù)據(jù)的約束檢查 語法：AL