SQLServer - 約束

標籤：

一、約束的分類

　　在SQLServer中，有3種不同類型的約束。

　　1、實體約束

　　　　實體約束是關於行的，比如某一行出現的值就不允許出現在其他行，例如主鍵。

　　2、域約束

　　　　域約束是關於列的，對於所有行，某一列有那些約束，例如CHECK約束。

　　3、參照完整性條件約束

　　　　如果某列的值必須與其他列的值匹配，那就意味著需要一個參照完整性條件約束，例如外鍵。

二、約束命名

　　在學習約束之前，首先來瞭解下為約束命名需要注意哪些地方。

　　SQLServer在我們不提供名稱時，會自動建立名稱，但是由系統自動建立的名稱並不是特別有用。

　　例如，系統產生的主鍵名稱可能是這樣的：PK_Employees_145C0A3F。 

　　PK代表主鍵(primary key)，Employees代表在Employees表中，而剩下的“145C0A3F”部分是為了保證唯一性而隨機產生的值。只有通過指令碼建立才會得到這種值，如果是通過Managerment Studio建立表，那麼就直接是PK_Employees。

　　對於系統自動產生的Check約束名稱如：CK_Customers_22AA2996。CK代表這是一個Check約束,Customers代表是在Customers表中，後面的22AA2996還是一個隨機數。如果一個表中有多個Check約束，則命名可能如下：

　　CK_Customers_22AA2996

　　CK_Customers_25869641

　　CK_Customers_267ABA7A

　　如果你需要修改這些約束其中的一個，那麼你很難分辨這些約束到底是哪一個。

　　因此，為了能夠一眼看上去就知道這個約束是用來幹什麼的，我們應該使用一種簡單明了的短語來進行命名。

　　例如要確保某一列電話號碼格式正確的約束，我們可以使用命名CK_Customers_PhoneNo這樣的短語來命名。

　　總之命名要做到以下幾點：

　　1、一致性

　　2、通俗易懂

　　3、滿足以上兩個條件的情況下簡化名稱。

三、鍵約束1、 主鍵約束

 　　主鍵是每行的唯一識別碼，僅僅通過它就能準確定位到一行，其中主鍵列在整個表中不能有重複，必須包含唯一的值(不能為NULL)。由於主鍵在關聯式資料庫中的重要性，因此它是所有鍵和約束中最重要的。

 　　下面來說說主鍵的建立方式

　　1、在建立表的時候建立主鍵約束。

create table customer(    customerId        int identity    not null        primary key,　　--建立主鍵約束    CustomerName    nvarchar(30)    not null);

　　怎麼樣，非常簡單吧！

　　2、在已存在的表上建立主鍵約束

　　現在假設已經存在了一張表，但是還沒有主鍵約束：

alter table person    add constraint PK_Employee_Id　　--外鍵名稱    primary key(personId)　　--personId 欄位名

　　alter名稱告訴SQLServer如下資訊：

　　1、添加了一些內容到表中(也可以刪除表中的某些內容)

　　2、添加了什麼內容(一個約束)

　　3、對約束的命名(允許以後直接存取約束)

　　4、約束的類型(主鍵約束)

　　5、約束應用於哪個列。

　　3、複合主鍵的建立

　　如果實在Management Studio中，建立複合主鍵，只需要按住Ctrl鍵，選中兩個列，然後設定為主鍵就OK了，非常簡單。下面主要講述使用T-SQL建立複合主鍵的方法：

ALTER TABLE 表名 WITH NOCHECK ADD CONSTRAINT [PK_表名] PRIMARY KEY NONCLUSTERED ( [欄位名1], [欄位名2] ) 

　　在多對多聯絡中，常常會有一張表來描述其他兩張表的關係，就以此讀者和書為例子：

ALTER TABLE ReaderAndBook ADD CONSTRAINT [PK_ReaderAndBook] PRIMARY KEY NONCLUSTERED ( ReaderId, BookId ) 

2、外鍵約束

　　外鍵既能確保資料完整性，也能表現表之間的關係。添加了外鍵之後，插入參考資料表的記錄要麼必須被參考資料表中被引用列的某條記錄匹配，要麼外鍵列的值必須設定為NULL。

　　外鍵和主鍵不一樣，每個表中的外鍵數目不限制唯一性。在每個表中，每一有-~253個外鍵。唯一的限制是一個列只能引用一個外鍵。一個列可以被多個外鍵引用。

　　1、建立表的時候建立外鍵

create table orders(    orderId        int identity    not null        primary key,    customerId    int                not null        foreign key references customer(customerId)　　--約束類型-外鍵-參考資料表(列名));

　　2、在已存在的表中添加一個外鍵

　　假設上面的代碼去掉了添加外鍵行，那麼可以書寫代碼如下：

alter table orders    add constraint FK_Orders_CustomerId        --添加約束 名稱        foreign key (customerId)    references customer(customerId)    --外鍵約束，外鍵列名，被引用列名

　　剛添加的約束和之前添加的約束一樣生效，如果某行引用customerId不存在，那麼就不允許把該行添加到Orders表中。

　　3、級聯動作

　　外鍵和其他類型鍵的一個重要區別是：外鍵是雙向的，即不僅是限制子表的值必須存在於父表中，還在每次對父表操作後檢查子行(這樣避免了孤行)。SQLServer的預設行為是在子行存在時“限制”父行被刪除。然而，有時會自動刪除任何依賴的記錄，而不是防止刪除被引用的記錄。同樣在更新記錄時，可能希望依賴的記錄自動引用剛剛更新的記錄。比較少見的情況是，你可能希望將引用行改變為某個已知的狀態。為此，可以選擇將依賴行的值設定為NULL或者那個列的預設值。

　　這種進行自動刪除和自動更新的過程稱為級聯。這種過程，特別是刪除過程，可以經過幾層的以來關係(一條記錄依賴於另一條記錄，而這另一條記錄又依賴其他記錄)。在SQLServer中實現級聯動作需要做的就是修改外鍵文法-只需要在添加前面加上ON子句。例如：

alter table orders    add constraint FK_Orders_CustomerId        --添加約束 名稱        foreign key (customerId)    references customer(customerId)    --外鍵約束，外鍵列名，被引用列名        on update     no action    --預設　　修改時不串聯更新子表        on delete     cascade      --刪除時串聯刪除依賴行

　　當在進行串聯刪除時，如果一個表級聯了另一個表，而另一個表又級聯了其他表，這種級聯會一直下去，不受限制，這其實是級聯的一個危險之處，很容易一個不小心刪掉大量資料。

　　級聯動作除了no action，cascade之外，還有set null和set default。後兩個是在SQLServer2005中引入的，如果要相容到SQLServer2000的話，要避免使用這兩個級聯動作。但是他們的才做是非常簡單的：如果執行更新而改變了一個父行的值，那麼子行的值將被設定為NULL，或者設定為該列的預設值(不管SET NULL還是SET DEFAULT)。

　　4、外鍵其他方面的考慮

　　　　外鍵中的之只有相中可能的選擇：

　　　　1、在列中填充與被參考資料表中的相應列相匹配的值。

　　　　　　通過定義引用列為NOT NULL，可以使外鍵完全是必須的(即使用者添加資料時必須參考資料表中必須有相匹配的一行資料)。

　　　　2、不填充任何值，而使該值為NULL。

　　　　　　允許引用列有NULL值時，使用者可以選擇不提供值-即使在被參考資料表沒有與NULL值匹配的行，還是允許插入。

3、唯一約束

　　唯一約束與主鍵比較相似，共同點在於它們都要求表中指定的列(或者列的組合)上有一個唯一值，區別是唯一約束沒有被看作表中記錄的唯一識別碼(即使你可以按這樣的方式使用也有效)，而且可以有多個唯一約束(而在每個表中只能有一個主鍵)。

　　一旦建立了唯一約束，那麼指定列中的每個值必須是唯一的。如果更新或者插入一條記錄在帶唯一約束的列上有已經存在的值的記錄，SQLServer將拋出錯誤，拒絕這個記錄。

　　和主鍵不同，唯一約束不會自動防止設定一個NULL值，是否允許為NULL由表中相應列的NULL選項的設定決定，但即使確實允許NULL值，一張表中也只能夠插入一個NULL值(如果允許多個，那就不叫唯一了)。

　　在已存在的表上建立唯一約束：

alter table Account    add constraint AK_AccountName    --約束名    unique (Account_Name)    -- 列名

　　AK代表替換鍵(Alternate Key)，唯一約束也叫替換鍵。

　　主鍵和唯一約束的區別：

•     主鍵約束不允許出現NULL值。任何索引的索引鍵都不允許包含null值。但唯一約束允許包含NULL值，但唯一約束把兩個NULL值當作重複值，所以施加了唯一約束的每一列只允許包含一個NULL值。
•     建立主鍵時會自動建立叢集索引，除非當前表中已經含有了叢集索引或是建立主鍵時指定了NONCLUSTERED關鍵字。
•     建立唯一約束時會自動建立非叢集索引，除非你指定了CLUSTERED關鍵字並且當前表中還沒有叢集索引。
•     每個表中只能有一個主鍵，但可以由多個唯一約束。

4、CHECK約束　　

　　CHECK約束約束可以和一個列關聯，也可以和一個表關聯，因為它們可以檢查一個列的值相對於另外一個列的值，只要這些列都在同一個表中以及值是在更新或者插入的同一行中。CHECK約束還可以用於檢查列值組合是否滿足某一個標準。

　　可以像使用where子句一樣的規則來定義CHECK約束。CHECK約束條件的樣本如下：

目標	SQL
限制Month列為合適的數字	BETWEEN 1 AND 12
正確的SSN格式	LIKE‘[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]‘
限制為一個快遞公司的特定列表	IN(‘UPS‘,‘Fed Ex‘,EMS‘)
價格必須為正數	UnitPrice >= 0
引用同一行中的另外一列	ShipDate >= OrderDate

　　上面給出的列表只是一小部分，而條件實際上市無限多的。幾乎所有可以放到where子句的條件都可以放到該約束中。而且和其他選擇(規則和觸發器)相比，CHECK約束執行速度更快。

　　在已存在的表中添加一個CHECK約束：

alter table Account    add constraint CN_AccountAge    check     (Account_Age > 18);    -- 插入年齡必須大於18

　　如果此時視圖添加一條不滿足的記錄，將報如下錯誤：

　　insert into Account values (22,‘洪‘,17)

訊息 547，層級 16，狀態 0，第 1 行INSERT 語句與 CHECK 條件約束"CN_AccountAge"衝突。該衝突發生於資料庫"Nx"，表"dbo.Account", column ‘Account_Age‘。語句已終止。

5、DEFAULT約束

　　和所有約束一樣，DEFAULT約束也是表定義的一個組成部分，它定義了當插入的新行對於定義了預設約束的列未提供相應資料時該怎麼辦。可以定義它為一個字面值(例如，設定預設薪水為0，或者設定字串列為"UNKNOWN")，或者某個系統值(getdate())。

　　對於DEFAULT約束，要瞭解以下幾個特性：

　　1、預設值只在insert語句中使用-在update語句和delete語句中被忽略。

　　2、如果在insert語句中提供了任意值，那就不使用預設值。

　　3、如果沒有提供值，那麼總是使用預設值。

　　值得注意的是，update命令的規則由一個例外，如果顯示說明使用預設值就是例外。可以通過使用關鍵字DEFAULT表示更新的值設定為預設值。

　　5.1在建立表時定義DEFAULT約束：

create table person(    person_id int identity not null        primary key,    person_name nvarchar(30) not null        default ‘無名氏‘,    person_age int not null)

　　在執行語句後：

insert into person (person_age) values(24)

　　表中被插入一條記錄如下：

　　

  　5.2在已存在的表上添加DEFAULT約束：

alter table person    add constraint CN_DefaultName    default    ‘無名氏‘ for person_name

6、禁用約束

 　　有時我們想暫時或永久地消除約束。但是SQL Server並沒有提供刪除約束的方法。SQL Server只允許禁用外鍵約束或CHECK約束，而同時保持約束的完整性。

 　　禁用一個資料完整性規則通常是因為已經有無效資料了。這樣的資料通常分為以下兩類：

　　1、在建立約束時已經在資料庫中的資料

　　2、在約束建立以後希望添加的資料

　　SQL Server允許禁用完整性檢查一段時間來對例外的無效資料作處理，然後再重新啟用完整性(不是物理刪除資料完整性約束)。

　　　　注意：不能禁用主鍵約束或者唯一約束

　　6.1、在建立約束時，忽略檢查之前的不滿足資料

　　要添加一個約束，但是有不應用到已存在的資料中，可以再執行Alter Table語句添加約束時使用WITH NOCHECK選項。

　　按照上面建立Check約束的方法，已經Alter Table時，表中本身已經存在不符合的資料，那麼Alter Table操作將被SQL Server拒絕執行。除非已經存在的所有資料都滿足CHECK約束的條件，否則SQL Server不會執行建立約束的命令。要解決這個問題，我們可以添加WITH NOCHECK。

　　我們先建立一個表只有3個欄位的表，Id、姓名、年齡，並在裡面插入一條不滿足要求的資料：

insert into Account values (23,‘洪‘,17)

 　　然後執行添加約束命令：

alter table Account    add constraint CN_AccountAge18    check     (Account_Age > 18);    -- 插入年齡必須大於18

 　　SQL Server報一下錯誤：

訊息 547，層級 16，狀態 0，第 1 行ALTER TABLE 語句與 CHECK 條件約束"CN_AccountAge18"衝突。該衝突發生於資料庫"Nx"，表"dbo.Account", column ‘Account_Age‘。

 　　這時候我們換一種方式去執行：

alter table Account    WITH NOCHECK    add constraint CN_AccountAge18    check     (Account_Age > 18);    -- 插入年齡必須大於18

 　　以上代碼就能夠成功執行，並且只有以後添加的資料具備約束，之前添加的不合格資料記錄依然存在。

 　　6.2臨時禁用已存在的約束

 　　當我們需要從另一資料庫中匯入資料到表中，而表中已建立了約束的時候，可能會存在一些資料和規則不匹配。當然有一個解決方式是先刪除約束，添加需要的資料，然後WITH NOCHECK在添加回去。但是這樣做太麻煩了。我們不需要這麼做。我們可以採用名為NOCHECK的選項來運行ALTER語句，這樣就能夠取消需要的約束。

　　先來看看上節中建立的這個約束：

alter table Account    add constraint CN_AccountAge18    check     (Account_Age > 18);    -- 插入年齡必須大於18

 　　要取消以上約束可以這樣來：

Alter Table Account    NOCHECK    constraint CN_AccountAge18

　　執行命令：

　　insert into Account values (25,‘取消了約束‘,17)

　　執行成功，成功添加了一行資料。

　　留意到又能夠向表中插入格式不匹配的資料了。

　　這裡要說明下，如下知道一個約束是否是啟用還是禁用呢？sp_helpconstraint命令，當我們執行sp_helpconstraint的時候，會有一列status_enabled顯示該約束的啟用狀態：

　　sp_helpconstraint Account

　　

 　　留意到status_enabled列為Disabled說明是禁用的意思。

　　當要啟用約束時，只需要用將語句中的NO CHECK替換為CHECK就可以了：

Alter Table Account    CHECK    constraint CN_AccountAge18

 　　執行之後，約束又啟用了：再來sp_helpconstraint看下：

　　

 　　留意到status_enabled列變成了Enabled。

　　status_enabled的兩種狀態如下：

　　Enabled:啟用；

　　Disabled:禁用；

7、規則和預設值

　　規則和預設值的應用要早於CHECK和DEFAULT約束。他們是較老的SQL Server備用約束的一部分，當然也不是沒有優點。自7.0版本之後，MicroSoft列出規則和預設值只是為了向後相容，而不準備在以後繼續支援這個特性。因此對於產生新代碼時，應該使用約束。

　　規則、預設值與約束的本質區別是：約束是一個表的特徵，本身沒有存在形式，而規則和預設值是表和自身的實際對象，本身存在。約束是在表定義中定義的，而規則和預設值是單獨定義，然後"綁定到"表上。

　　規則和預設值的獨立對象特性使得它們可以在重用時不用重新定義。實際上，規則和預設值不限於被綁定到表上，它們也可以綁定到資料類型上。

　　7.1規則

 　　規則和CHECK約束非常相似。它們之間的唯一區別是規則每次只能作用於一個列。可以將同一規則分別綁定到一個表中的多個列，但是規則分別作用於每個列，根本不會意識到其他列的存在。像QtyShipped

<= QtyOrdered這樣的約束不適用於規則(它引用多個列)，而LIKE([0-9][0-9][0-9])這樣的定義適用於規則。

　　定義規則：

　　下面定義一個規則，這樣就可以首先看到區別所在：

CREATE RULE Age18Rule    AS @Age > 18　　

　　這裡比較的是一個變數，不管被檢查的列是什麼值，這個值將用於替換@Age。因此在這個樣本中，規則所綁定的任何列的值都必須大於18。

　　到目前為止，只是建立了一個規則，但這個規則還沒對任何錶的任何列起作用，要啟用這個規則需要使用一個預存程序：sp_bindrule。

　　將規則Age18綁定到表person的person_age列：

　　EXEC sp_bindrule ‘Age18Rule‘,‘person.person_age‘;

　　此時，如果我們執行不滿足規則的插入操作：

insert into person values (‘綁定規則‘,17)

 　　將返回如下報錯資訊：

訊息 513，層級 16，狀態 0，第 1 行列的插入或更新與先前的 CREATE RULE 語句所指定的規則發生衝突。該語句已終止。衝突發生於資料庫 ‘Nx‘，表 ‘dbo.person‘，列 ‘person_age‘。語句已終止。

　　很明顯，規則已經生效。

　　要特別注意的是，在綁定之前，規則與任何錶，任何列都沒有關係，因此在綁定的時候，第二個參數要加.指定表名與列名(tablename.column)。

　　解除綁定規則：

　　當我們需要在一個列上解除綁定規則的時候，只要執行sp_unbindrule

　　刪除剛才綁定的規則：

EXEC sp_unbindrule ‘person.person_age‘;

　　這時候，執行剛才的插入操作，就不會報錯了。

　　刪除規則：

　　如果希望將規則從資料庫中徹底刪除，那麼可以在表中使用非常熟悉的DROP文法。

DROP RULE <rule name>

　　如刪除剛才建立的那條規則：

DROP RULE Age18Rule

　　7.2預設值

　　預設值類似於DEFAULT。實際上預設值-DEFAULT約束的關係與規則-CHECK約束的關係差不多。區別在於它們被追加到表中的方式和對使用者自訂資料類型的預設值(是對象，而不是約束)支援。

　　定義預設值的文法和定義規則類似：

　　CREATE DEFAULT <default_name>　　AS <default value>

　　建立預設值：

　　因此，假設要為Age定義一個值為0的預設值:

CREATE DEFAULT AgeDefaultAS 0

　　綁定預設值：

　　同樣，如果不綁定到一個對象上，則預設值是不起作用的。要綁定的話，使用預存程序sp_bindefault。

　　EXEC sp_bindefault ‘AgeDefault‘,‘person.person_age‘;

　　要從表中解決預設值的綁定，使用sp_unbindefault:

　　sp_unbindefault ‘person.person_age‘;

　　刪除預設值：

　　如果要從資料庫中徹底刪除一個預設值，則可以使用DROP文法，與刪除規則相同：

　　DROP DEFAULT AgeDefault

　　7.3確定哪個表和資料類型使用給定的規則或預設值

　　如果希望刪除或者修改規則或預設值。那麼您可以先看看哪些表和資料類型在使用它們。SQL Server還是採用系統預存程序解決這個問題。這個預存程序是sp_depends。其文法如下所示：

　　EXEC sp_depends <object name>

　　sp_depends提供了依賴於你所查詢對象的所有對象列表。

8、觸發器

　　觸發器也能夠用於實現資料完整性，這個內容比較多，建立一篇文章敘述。

9、如何選擇

　　經過以上的學習，對於資料完整性，你會發現有很多種可以選擇，那麼如何挑選合適的約束呢？

限制	優點	缺點
約束	快速 可以引用其他列 在命令執行前發生 遵循ANSI標準	必須對每個表重新定義 不能引用其他表 不能綁定到資料類型
規則	獨立的對象 可重用 可以綁定到資料類型 命令執行前發生	稍慢 不能跨列使用 不能引用其他表 實際上只用於向後相容
預設值	非常靈活 可以引用其他列或其他表 可以通過.NET引用SQL Server之外的其他資訊	在命令執行之後發生 系統開銷很大

　　如果要實現更健壯的邏輯模型以及廣泛使用使用者自訂資料類型，則一般使用規則和預設值。在這種情況下規則和預設值可以提供很多功能，容易管理，而不用太多的編程開銷。

　　只有在不能選擇約束時使用觸發器。和約束一樣，他們被附加到表中，而且必須對建立的每個表重新定義。好的方面是觸發器幾乎可以做資料完整性方面的任何操作。實際上再沒有出現外鍵時，他們常被用作外鍵的替代品。

　　而在其他情況下，應將約束作為資料完整性解決方案的選擇。它們執行速度快，而且不難建立。他們的缺點是功能有限(除了外鍵約束，都不能引用其他表)，而且對於通用約束邏輯來說，需要一次次地重新定義。

SQLServer - 約束