SQL Server中的約束(constraints)詳解

一、約束的分類

在SQL Server中，有3種不同型別的約束。

• 實體約束
  實體約束是關於行的，比如某一行出現的值就不允許出現在其他行，例如主鍵。
• 域約束
  域約束是關於列的，對於所有行，某一列有那些約束，例如CHECK約束。
• 參照完整性約束
  如果某列的值必須與其他列的值匹配，那就意味著需要一個參照完整性約束，例如外來鍵。

二、約束命名

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

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

例如，系統生成的主鍵名稱可能是這樣的：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這樣的短語來命名。

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

• 一致性
• 通俗易懂
• 滿足以上兩個條件的情況下簡化名稱。

三、主鍵約束

  主鍵是每行的唯一識別符號，僅僅通過它就能準確定位到一行，其中主鍵列在整個表中不能有重複，必須包含唯一的值(不能為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名稱告訴SQL Server如下資訊：

• 新增了一些內容到表中(也可以刪除表中的某些內容)
• 新增了什麼內容(一個約束)
• 對約束的命名(允許以後直接存取約束)
• 約束的型別(主鍵約束)
• 約束應用於哪個列。

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 )

四、外來鍵約束

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

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

4.1、建立表的時候建立外來鍵

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

4.2、在已存在的表中新增一個外來鍵

假設上面的程式碼去掉了新增外來鍵行，那麼可以書寫程式碼如下：

alter table orders
    add constraint FK_Orders_CustomerId        --新增約束 名稱
    foreign key (customerId)   references customer(customerId)    --外來鍵約束，外來鍵列名，被參照列名

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

4.3、級聯動作

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

這種進行自動刪除和自動更新的過程稱為級聯。這種過程，特別是刪除過程，可以經過幾層的依賴關係(一條記錄依賴於另一條記錄，而這另一條記錄又依賴其他記錄)。

在SQL Server中實現級聯動作需要做的就是修改外來鍵語法-只需要在新增前面加上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。後兩個是在SQL Server2005中引入的，如果要相容到SQL Server2000的話，要避免使用這兩個級聯動作。但是他們的才做是非常簡單的：如果執行更新而改變了一個父行的值，那麼子行的值將被設定為NULL，或者設定為該列的預設值(不管SET NULL還是SET DEFAULT)。

五、唯一約束

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

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

和主鍵不同，唯一約束不會自動防止設定一個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關鍵字並且當前表中還沒有聚集索引。
•     每個表中只能有一個主鍵，但可以由多個唯一約束。

六、CHECK約束

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

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

• 限制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'。
語句已終止。

七、DEFAULT約束

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

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

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

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

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

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

7.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)

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

7.2在已存在的表上新增DEFAULT約束：

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

八、禁用約束

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

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

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

2、在約束建立以後希望新增的資料

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

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

8.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

  以上程式碼就能夠成功執行，並且只有以後新增的資料具備約束，之前新增的不符合條件的資料記錄依然存在。

8.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:禁用；

九、規則和預設值（已淘汰）

規則和預設值的應用要早於CHECK和DEFAULT約束。他們是較老的SQL Server備用約束的一部分，當然也不是沒有優點。

自7.0版本之後，MicroSoft指出規則和預設值只是為了向後相容，而不準備在以後繼續支援這個特性。因此對於生成新程式碼時，應該使用約束。

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

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

9.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

9.2、預設值

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

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

CREATE DEFAULT <default_name>
AS <default value>

建立預設值：

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

CREATE DEFAULT AgeDefault
AS 0

繫結預設值：

同樣，如果不繫結到一個物件上，則預設值是不起作用的。要繫結的話，使用儲存過程sp_bindefault。

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

要從表中解決預設值的繫結，使用sp_unbindefault:

sp_unbindefault 'person.person_age';

刪除預設值：

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

DROP DEFAULT AgeDefault

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

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

EXEC sp_depends <object name>

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

十、系統檢視

--CHECK約束，資料來源sys.objects.type='C',
--相容性檢視SYSCONSTRAINTS
select *, ( select c.name from syscolumns c where c.colid = t.parent_column_id and c.id = object_id('Mould')) as 列名
from   sys.check_constraints t
where  parent_object_id = object_id('Mould');

--預設約束，資料來源sys.objects.type=D,
--相容性檢視SYSCONSTRAINTS
select *, ( select c.name from syscolumns c where c.colid = t.parent_column_id and c.id = object_id('Mould')) as 列名
from   sys.default_constraints t
where  parent_object_id = object_id('Mould');

--主鍵或唯一約束,資料來源sys.objects.type PK 和UQ,
--相容性檢視SYSCONSTRAINTS
select * from sys.key_constraints t where parent_object_id = object_id('Mould');

--外來鍵，資料來源sys.object.type=F,
--相容性檢視SYSREFERENCES
select * from sys.foreign_keys t where parent_object_id = object_id('Mould');

--根據表名和列名查詢列上的約束
select sysobjects.name, sysobjects.xtype, sysobjects.id
from   sysobjects
       join sysconstraints on sysobjects.id = sysconstraints.constid
where  sysobjects.parent_obj = object_id('Mould') and sysconstraints.colid in ( select colid from syscolumns where id = object_id('Mould')  AND name like  '%' );

到此這篇關於SQL Server約束(constraints)的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支援it145.com。