SQLServer-約束

1、SQLServer - 約束一、約束的分類(lèi)在SQLServer中，有3種不同類(lèi)型的約束。1、實(shí)體約束實(shí)體約束是關(guān)于行的，比如某一行出現(xiàn)的值就不允許出現(xiàn)在其他行，例如主鍵。2、域約束域約束是關(guān)于列的，對(duì)于所有行，某一列有那些約束，例如CHECK約束。3、參照完整性約束如果某列的值必須與其他列的值匹配，那就意味著需要一個(gè)參照完整性約束，例如外鍵。二、約束命名在學(xué)習(xí)約束之前，首先來(lái)了解下為約束命名需要注意哪些地方。SQLServer在我們不提供名稱(chēng)時(shí)，會(huì)自動(dòng)創(chuàng)建名稱(chēng)，但是由系統(tǒng)自動(dòng)創(chuàng)建的名稱(chēng)并不是特別有用。例如，系統(tǒng)生成的主鍵名稱(chēng)可能是這樣的：PK_Employees_145C0A3F。 

2、PK代表主鍵(primary key)，Employees代表在Employees表中，而剩下的“145C0A3F”部分是為了保證唯一性而隨機(jī)生成的值。只有通過(guò)腳本創(chuàng)建才會(huì)得到這種值，如果是通過(guò)Managerment Studio創(chuàng)建表，那么就直接是PK_Employees。對(duì)于系統(tǒng)自動(dòng)生成的Check約束名稱(chēng)如：CK_Customers_22AA2996。CK代表這是一個(gè)Check約束,Customers代表是在Customers表中，后面的22AA2996還是一個(gè)隨機(jī)數(shù)。如果一個(gè)表中有多個(gè)Check約束，則命名可能如下：CK_Customers_22AA2996CK_Customers_2

3、5869641CK_Customers_267ABA7A如果你需要修改這些約束其中的一個(gè)，那么你很難分辨這些約束到底是哪一個(gè)。因此，為了能夠一眼看上去就知道這個(gè)約束是用來(lái)干什么的，我們應(yīng)該使用一種簡(jiǎn)單明了的短語(yǔ)來(lái)進(jìn)行命名。例如要確保某一列電話號(hào)碼格式正確的約束，我們可以使用命名CK_Customers_PhoneNo這樣的短語(yǔ)來(lái)命名?？傊龅揭韵聨c(diǎn)：1、一致性2、通俗易懂3、滿足以上兩個(gè)條件的情況下簡(jiǎn)化名稱(chēng)。三、鍵約束1、主鍵約束 主鍵是每行的唯一標(biāo)識(shí)符，僅僅通過(guò)它就能準(zhǔn)確定位到一行，其中主鍵列在整個(gè)表中不能有重復(fù)，必須包含唯一的值(不能為NULL)。由于主鍵在關(guān)系數(shù)據(jù)庫(kù)中的

4、重要性，因此它是所有鍵和約束中最重要的。 下面來(lái)說(shuō)說(shuō)主鍵的創(chuàng)建方式1、在創(chuàng)建表的時(shí)候創(chuàng)建主鍵約束。create table customer( customerId int identity not null primary key,-創(chuàng)建主鍵約束 CustomerName nvarchar(30) not null);怎么樣，非常簡(jiǎn)單吧！2、在已存在的表上創(chuàng)建主鍵約束現(xiàn)在假設(shè)已經(jīng)存在了一張表，但是還沒(méi)有主鍵約束：alter table person add constraint PK_Employee_Id-外鍵名稱(chēng) primary key(personId)-personId 字

5、段名alter名稱(chēng)告訴SQLServer如下信息：1、添加了一些內(nèi)容到表中(也可以刪除表中的某些內(nèi)容)2、添加了什么內(nèi)容(一個(gè)約束)3、對(duì)約束的命名(允許以后直接訪問(wèn)約束)4、約束的類(lèi)型(主鍵約束)5、約束應(yīng)用于哪個(gè)列。3、復(fù)合主鍵的創(chuàng)建如果實(shí)在Management Studio中，創(chuàng)建復(fù)合主鍵，只需要按住Ctrl鍵，選中兩個(gè)列，然后設(shè)置為主鍵就OK了，非常簡(jiǎn)單。下面主要講述使用T-SQL創(chuàng)建復(fù)合主鍵的方法：ALTER TABLE 表名 WITH NOCHECK ADD CONSTRAINT PK_表名 PRIMARY KEY NONCLUSTERED ( 字段名1, 字段名2 ) 在多對(duì)多聯(lián)

6、系中，常常會(huì)有一張表來(lái)描述其他兩張表的關(guān)系，就以此讀者和書(shū)為例子：ALTER TABLE ReaderAndBook ADD CONSTRAINT PK_ReaderAndBook PRIMARY KEY NONCLUSTERED ( ReaderId, BookId ) 2、外鍵約束外鍵既能確保數(shù)據(jù)完整性，也能表現(xiàn)表之間的關(guān)系。添加了外鍵之后，插入引用表的記錄要么必須被引用表中被引用列的某條記錄匹配，要么外鍵列的值必須設(shè)置為NULL。外鍵和主鍵不一樣，每個(gè)表中的外鍵數(shù)目不限制唯一性。在每個(gè)表中，每一有-253個(gè)外鍵。唯一的限制是一個(gè)列只能引用一個(gè)外鍵。一個(gè)列可以被多個(gè)外鍵引用。1、創(chuàng)建表的時(shí)

7、候創(chuàng)建外鍵create table orders( orderId int identity not null primary key, customerId int not null foreign key references customer(customerId)-約束類(lèi)型-外鍵-引用表(列名);2、在已存在的表中添加一個(gè)外鍵假設(shè)上面的代碼去掉了添加外鍵行，那么可以書(shū)寫(xiě)代碼如下：alter table orders add constraint FK_Orders_CustomerId -添加約束 名稱(chēng) foreign key (customerId) references custo

8、mer(customerId) -外鍵約束，外鍵列名，被引用列名剛添加的約束和之前添加的約束一樣生效，如果某行引用customerId不存在，那么就不允許把該行添加到Orders表中。3、級(jí)聯(lián)動(dòng)作外鍵和其他類(lèi)型鍵的一個(gè)重要區(qū)別是：外鍵是雙向的，即不僅是限制子表的值必須存在于父表中，還在每次對(duì)父表操作后檢查子行(這樣避免了孤行)。SQLServer的默認(rèn)行為是在子行存在時(shí)“限制”父行被刪除。然而，有時(shí)會(huì)自動(dòng)刪除任何依賴的記錄，而不是防止刪除被引用的記錄。同樣在更新記錄時(shí)，可能希望依賴的記錄自動(dòng)引用剛剛更新的記錄。比較少見(jiàn)的情況是，你可能希望將引用行改變?yōu)槟硞€(gè)已知的狀態(tài)。為此，可以選擇將依賴行的值

9、設(shè)置為NULL或者那個(gè)列的默認(rèn)值。這種進(jìn)行自動(dòng)刪除和自動(dòng)更新的過(guò)程稱(chēng)為級(jí)聯(lián)。這種過(guò)程，特別是刪除過(guò)程，可以經(jīng)過(guò)幾層的以來(lái)關(guān)系(一條記錄依賴于另一條記錄，而這另一條記錄又依賴其他記錄)。在SQLServer中實(shí)現(xiàn)級(jí)聯(lián)動(dòng)作需要做的就是修改外鍵語(yǔ)法-只需要在添加前面加上ON子句。例如：alter table orders add constraint FK_Orders_CustomerId -添加約束 名稱(chēng) foreign key (customerId) references customer(customerId) -外鍵約束，外鍵列名，被引用列名 on update no action -默

10、認(rèn)修改時(shí)不級(jí)聯(lián)更新子表 on delete cascade -刪除時(shí)級(jí)聯(lián)刪除依賴行當(dāng)在進(jìn)行級(jí)聯(lián)刪除時(shí)，如果一個(gè)表級(jí)聯(lián)了另一個(gè)表，而另一個(gè)表又級(jí)聯(lián)了其他表，這種級(jí)聯(lián)會(huì)一直下去，不受限制，這其實(shí)是級(jí)聯(lián)的一個(gè)危險(xiǎn)之處，很容易一個(gè)不小心刪掉大量數(shù)據(jù)。級(jí)聯(lián)動(dòng)作除了no action，cascade之外，還有set null和set default。后兩個(gè)是在SQLServer2005中引入的，如果要兼容到SQLServer2000的話，要避免使用這兩個(gè)級(jí)聯(lián)動(dòng)作。但是他們的才做是非常簡(jiǎn)單的：如果執(zhí)行更新而改變了一個(gè)父行的值，那么子行的值將被設(shè)置為NULL，或者設(shè)置為該列的默認(rèn)值(不管SET NULL還是S

11、ET DEFAULT)。4、外鍵其他方面的考慮外鍵中的之只有相中可能的選擇：1、在列中填充與被引用表中的相應(yīng)列相匹配的值。通過(guò)定義引用列為NOT NULL，可以使外鍵完全是必須的(即用戶添加數(shù)據(jù)時(shí)必須引用表中必須有相匹配的一行數(shù)據(jù))。2、不填充任何值，而使該值為NULL。允許引用列有NULL值時(shí)，用戶可以選擇不提供值-即使在被引用表沒(méi)有與NULL值匹配的行，還是允許插入。3、唯一約束唯一約束與主鍵比較相似，共同點(diǎn)在于它們都要求表中指定的列(或者列的組合)上有一個(gè)唯一值，區(qū)別是唯一約束沒(méi)有被看作表中記錄的唯一標(biāo)識(shí)符(即使你可以按這樣的方式使用也有效)，而且可以有多個(gè)唯一約束(而在每個(gè)表中只能有一

12、個(gè)主鍵)。一旦建立了唯一約束，那么指定列中的每個(gè)值必須是唯一的。如果更新或者插入一條記錄在帶唯一約束的列上有已經(jīng)存在的值的記錄，SQLServer將拋出錯(cuò)誤，拒絕這個(gè)記錄。和主鍵不同，唯一約束不會(huì)自動(dòng)防止設(shè)置一個(gè)NULL值，是否允許為NULL由表中相應(yīng)列的NULL選項(xiàng)的設(shè)置決定，但即使確實(shí)允許NULL值，一張表中也只能夠插入一個(gè)NULL值(如果允許多個(gè)，那就不叫唯一了)。在已存在的表上創(chuàng)建唯一約束：alter table Account add constraint AK_AccountName -約束名 unique (Account_Name) - 列名AK代表替換鍵(Alternate

13、Key)，唯一約束也叫替換鍵。主鍵和唯一約束的區(qū)別：·     主鍵約束不允許出現(xiàn)NULL值。任何索引的索引鍵都不允許包含null值。但唯一約束允許包含NULL值，但唯一約束把兩個(gè)NULL值當(dāng)作重復(fù)值，所以施加了唯一約束的每一列只允許包含一個(gè)NULL值。·     創(chuàng)建主鍵時(shí)會(huì)自動(dòng)創(chuàng)建聚集索引，除非當(dāng)前表中已經(jīng)含有了聚集索引或是創(chuàng)建主鍵時(shí)指定了NONCLUSTERED關(guān)鍵字。·     創(chuàng)建唯一約束時(shí)會(huì)自動(dòng)創(chuàng)建非聚集索引，除非你指定了CLUSTERED關(guān)鍵字并且當(dāng)前表中還沒(méi)有

14、聚集索引。·     每個(gè)表中只能有一個(gè)主鍵，但可以由多個(gè)唯一約束。4、CHECK約束CHECK約束約束可以和一個(gè)列關(guān)聯(lián)，也可以和一個(gè)表關(guān)聯(lián)，因?yàn)樗鼈兛梢詸z查一個(gè)列的值相對(duì)于另外一個(gè)列的值，只要這些列都在同一個(gè)表中以及值是在更新或者插入的同一行中。CHECK約束還可以用于檢查列值組合是否滿足某一個(gè)標(biāo)準(zhǔn)?？梢韵袷褂脀here子句一樣的規(guī)則來(lái)定義CHECK約束。CHECK約束條件的示例如下：目標(biāo)SQL限制Month列為合適的數(shù)字BETWEEN 1 AND 12正確的SSN格式LIKE'0-90-90-9-0-90-9-0-90-90-90-9'

15、;限制為一個(gè)快遞公司的特定列表IN('UPS','Fed Ex',EMS')價(jià)格必須為正數(shù)UnitPrice >= 0引用同一行中的另外一列ShipDate >= OrderDate上面給出的列表只是一小部分，而條件實(shí)際上市無(wú)限多的。幾乎所有可以放到where子句的條件都可以放到該約束中。而且和其他選擇(規(guī)則和觸發(fā)器)相比，CHECK約束執(zhí)行速度更快。在已存在的表中添加一個(gè)CHECK約束：alter table Account add constraint CN_AccountAge check (Account_Age > 18);

16、- 插入年齡必須大于18如果此時(shí)視圖添加一條不滿足的記錄，將報(bào)如下錯(cuò)誤：insert into Account values (22,'洪',17)消息 547，級(jí)別 16，狀態(tài) 0，第 1 行INSERT 語(yǔ)句與 CHECK 約束"CN_AccountAge"沖突。該沖突發(fā)生于數(shù)據(jù)庫(kù)"Nx"，表"dbo.Account", column 'Account_Age'。語(yǔ)句已終止。5、DEFAULT約束和所有約束一樣，DEFAULT約束也是表定義的一個(gè)組成部分，它定義了當(dāng)插入的新行對(duì)于定義了默認(rèn)約束的列未

17、提供相應(yīng)數(shù)據(jù)時(shí)該怎么辦?？梢远x它為一個(gè)字面值(例如，設(shè)置默認(rèn)薪水為0，或者設(shè)置字符串列為"UNKNOWN")，或者某個(gè)系統(tǒng)值(getdate()。對(duì)于DEFAULT約束，要了解以下幾個(gè)特性：1、默認(rèn)值只在insert語(yǔ)句中使用-在update語(yǔ)句和delete語(yǔ)句中被忽略。2、如果在insert語(yǔ)句中提供了任意值，那就不使用默認(rèn)值。3、如果沒(méi)有提供值，那么總是使用默認(rèn)值。值得注意的是，update命令的規(guī)則由一個(gè)例外，如果顯示說(shuō)明使用默認(rèn)值就是例外。可以通過(guò)使用關(guān)鍵字DEFAULT表示更新的值設(shè)置為默認(rèn)值。5.1在創(chuàng)建表時(shí)定義DEFAULT約束：create table

18、person( person_id int identity not null primary key, person_name nvarchar(30) not null default '無(wú)名氏', person_age int not null)在執(zhí)行語(yǔ)句后：insert into person (person_age) values(24)表中被插入一條記錄如下：  5.2在已存在的表上添加DEFAULT約束：alter table person add constraint CN_DefaultName default '無(wú)名氏' for p

19、erson_name6、禁用約束 有時(shí)我們想暫時(shí)或永久地消除約束。但是SQL Server并沒(méi)有提供刪除約束的方法。SQL Server只允許禁用外鍵約束或CHECK約束，而同時(shí)保持約束的完整性。 禁用一個(gè)數(shù)據(jù)完整性規(guī)則通常是因?yàn)橐呀?jīng)有無(wú)效數(shù)據(jù)了。這樣的數(shù)據(jù)通常分為以下兩類(lèi)：1、在創(chuàng)建約束時(shí)已經(jīng)在數(shù)據(jù)庫(kù)中的數(shù)據(jù)2、在約束創(chuàng)建以后希望添加的數(shù)據(jù)SQL Server允許禁用完整性檢查一段時(shí)間來(lái)對(duì)例外的無(wú)效數(shù)據(jù)作處理，然后再重新啟用完整性(不是物理刪除數(shù)據(jù)完整性約束)。注意：不能禁用主鍵約束或者唯一約束6.1、在創(chuàng)建約束時(shí)，忽略檢查之前的不滿足數(shù)據(jù)要添加一個(gè)約束，但是有不應(yīng)用到已

20、存在的數(shù)據(jù)中，可以再執(zhí)行Alter Table語(yǔ)句添加約束時(shí)使用WITH NOCHECK選項(xiàng)。按照上面創(chuàng)建Check約束的方法，已經(jīng)Alter Table時(shí)，表中本身已經(jīng)存在不符合的數(shù)據(jù)，那么Alter Table操作將被SQL Server拒絕執(zhí)行。除非已經(jīng)存在的所有數(shù)據(jù)都滿足CHECK約束的條件，否則SQL Server不會(huì)執(zhí)行創(chuàng)建約束的命令。要解決這個(gè)問(wèn)題，我們可以添加WITH NOCHECK。我們先新建一個(gè)表只有3個(gè)字段的表，Id、姓名、年齡，并在里面插入一條不滿足要求的數(shù)據(jù)：insert into Account values (23,'洪',17) 然后執(zhí)

21、行添加約束命令：alter table Account add constraint CN_AccountAge18 check (Account_Age > 18); - 插入年齡必須大于18 SQL Server報(bào)一下錯(cuò)誤：消息 547，級(jí)別 16，狀態(tài) 0，第 1 行ALTER TABLE 語(yǔ)句與 CHECK 約束"CN_AccountAge18"沖突。該沖突發(fā)生于數(shù)據(jù)庫(kù)"Nx"，表"dbo.Account", column 'Account_Age'。 這時(shí)候我們換一種方式去執(zhí)行：al

22、ter table Account WITH NOCHECK add constraint CN_AccountAge18 check (Account_Age > 18); - 插入年齡必須大于18 以上代碼就能夠成功執(zhí)行，并且只有以后添加的數(shù)據(jù)具備約束，之前添加的不符合條件的數(shù)據(jù)記錄依然存在。 6.2臨時(shí)禁用已存在的約束 當(dāng)我們需要從另一數(shù)據(jù)庫(kù)中導(dǎo)入數(shù)據(jù)到表中，而表中已建立了約束的時(shí)候，可能會(huì)存在一些數(shù)據(jù)和規(guī)則不匹配。當(dāng)然有一個(gè)解決方式是先刪除約束，添加需要的數(shù)據(jù)，然后WITH NOCHECK在添加回去。但是這樣做太麻煩了。我們不需要這么做。我們可以采用

23、名為NOCHECK的選項(xiàng)來(lái)運(yùn)行ALTER語(yǔ)句，這樣就能夠取消需要的約束。先來(lái)看看上節(jié)中創(chuàng)建的這個(gè)約束：alter table Account add constraint CN_AccountAge18 check (Account_Age > 18); - 插入年齡必須大于18 要取消以上約束可以這樣來(lái)：Alter Table Account NOCHECK constraint CN_AccountAge18執(zhí)行命令：insert into Account values (25,'取消了約束',17)執(zhí)行成功，成功添加了一行數(shù)據(jù)。留意到又能夠向表中插入格式不

24、匹配的數(shù)據(jù)了。這里要說(shuō)明下，如下知道一個(gè)約束是否是啟用還是禁用呢？sp_helpconstraint命令，當(dāng)我們執(zhí)行sp_helpconstraint的時(shí)候，會(huì)有一列status_enabled顯示該約束的啟用狀態(tài)：sp_helpconstraint Account 留意到status_enabled列為Disabled說(shuō)明是禁用的意思。當(dāng)要啟用約束時(shí)，只需要用將語(yǔ)句中的NO CHECK替換為CHECK就可以了：Alter Table Account CHECK constraint CN_AccountAge18 執(zhí)行之后，約束又啟用了：再來(lái)sp_helpconstrai

25、nt看下： 留意到status_enabled列變成了Enabled。status_enabled的兩種狀態(tài)如下：Enabled:啟用；Disabled:禁用；7、規(guī)則和默認(rèn)值規(guī)則和默認(rèn)值的應(yīng)用要早于CHECK和DEFAULT約束。他們是較老的SQL Server備用約束的一部分，當(dāng)然也不是沒(méi)有優(yōu)點(diǎn)。自7.0版本之后，MicroSoft列出規(guī)則和默認(rèn)值只是為了向后兼容，而不準(zhǔn)備在以后繼續(xù)支持這個(gè)特性。因此對(duì)于生成新代碼時(shí)，應(yīng)該使用約束。規(guī)則、默認(rèn)值與約束的本質(zhì)區(qū)別是：約束是一個(gè)表的特征，本身沒(méi)有存在形式，而規(guī)則和默認(rèn)值是表和自身的實(shí)際對(duì)象，本身存在。約束是在表定義中定義的，而規(guī)則和默

26、認(rèn)值是單獨(dú)定義，然后"綁定到"表上。規(guī)則和默認(rèn)值的獨(dú)立對(duì)象特性使得它們可以在重用時(shí)不用重新定義。實(shí)際上，規(guī)則和默認(rèn)值不限于被綁定到表上，它們也可以綁定到數(shù)據(jù)類(lèi)型上。7.1規(guī)則 規(guī)則和CHECK約束非常相似。它們之間的唯一區(qū)別是規(guī)則每次只能作用于一個(gè)列?？梢詫⑼灰?guī)則分別綁定到一個(gè)表中的多個(gè)列，但是規(guī)則分別作用于每個(gè)列，根本不會(huì)意識(shí)到其他列的存在。像QtyShipped<= QtyOrdered這樣的約束不適用于規(guī)則(它引用多個(gè)列)，而LIKE(0-90-90-9)這樣的定義適用于規(guī)則。定義規(guī)則：下面定義一個(gè)規(guī)則，這樣就可以首先看到區(qū)別所在：CREATE RU

27、LE Age18Rule AS Age > 18這里比較的是一個(gè)變量，不管被檢查的列是什么值，這個(gè)值將用于替換Age。因此在這個(gè)示例中，規(guī)則所綁定的任何列的值都必須大于18。到目前為止，只是創(chuàng)建了一個(gè)規(guī)則，但這個(gè)規(guī)則還沒(méi)對(duì)任何表的任何列起作用，要激活這個(gè)規(guī)則需要使用一個(gè)存儲(chǔ)過(guò)程：sp_bindrule。將規(guī)則Age18綁定到表person的person_age列：EXEC sp_bindrule 'Age18Rule','person.person_age'此時(shí)，如果我們執(zhí)行不滿足規(guī)則的插入操作：insert into person values (

28、9;綁定規(guī)則',17) 將返回如下報(bào)錯(cuò)信息：消息 513，級(jí)別 16，狀態(tài) 0，第 1 行列的插入或更新與先前的 CREATE RULE 語(yǔ)句所指定的規(guī)則發(fā)生沖突。該語(yǔ)句已終止。沖突發(fā)生于數(shù)據(jù)庫(kù) 'Nx'，表 'dbo.person'，列 'person_age'。語(yǔ)句已終止。很明顯，規(guī)則已經(jīng)生效。要特別注意的是，在綁定之前，規(guī)則與任何表，任何列都沒(méi)有關(guān)系，因此在綁定的時(shí)候，第二個(gè)參數(shù)要加.指定表名與列名(tablename.column)。解除綁定規(guī)則：當(dāng)我們需要在一個(gè)列上解除綁定規(guī)則的時(shí)候，只要執(zhí)行sp_unbindrule

29、刪除剛才綁定的規(guī)則：EXEC sp_unbindrule 'person.person_age'這時(shí)候，執(zhí)行剛才的插入操作，就不會(huì)報(bào)錯(cuò)了。刪除規(guī)則：如果希望將規(guī)則從數(shù)據(jù)庫(kù)中徹底刪除，那么可以在表中使用非常熟悉的DROP語(yǔ)法。DROP RULE <rule name>如刪除剛才創(chuàng)建的那條規(guī)則：DROP RULE Age18Rule7.2默認(rèn)值默認(rèn)值類(lèi)似于DEFAULT。實(shí)際上默認(rèn)值-DEFAULT約束的關(guān)系與規(guī)則-CHECK約束的關(guān)系差不多。區(qū)別在于它們被追加到表中的方式和對(duì)用戶自定義數(shù)據(jù)類(lèi)型的默認(rèn)值(是對(duì)象，而不是約束)支持。定義默認(rèn)值的語(yǔ)法和定義規(guī)則類(lèi)似：CREATE DEFAULT <default_name>AS <default value>創(chuàng)建默認(rèn)值：因此，假設(shè)要為Age定義一個(gè)值為0的默認(rèn)值:CREATE D