SQL Server中的約束

摘要

對於SQL Server中的約束，想必大家並不是很陌生。但是約束中真正的內涵是什麼，並不是很多人都很清楚的。本文以詳細的文字來介紹了什麼是約束，以及如何在資料庫編程中應用和使用這些約束，來達到更好的編程效果。（本文部分內容參考了SQL Server線上手冊）

內容

	資料完整性分類
	實體完整性
	值域完整性
	參考完整性
	使用者定義完整性
	PRIMARY KEY約束
	DEFAULT約束
	CHECK約束
	UNIQUE約束
	FOREIGN KEY約束

本文

在資料庫管理系統中，保證資料庫中的資料完整性是非常重要的。所謂資料完整性，就是指儲存在資料庫中資料的一致性和正確性。約束定義關於列中允許值的規則，是強制完整性的標準機制。使用約束優先於使用觸發器、規則和預設值。查詢最佳化工具也使用約束定義產生高效能的查詢執行計畫。

SQL Server聯機叢書中，將資料完整性解釋如下：“儲存在資料庫中的所有資料值均正確的狀態。如果資料庫中儲存有不正確的資料值，則該資料庫稱為已喪失資料完整性。”強制資料完整性可確保資料庫中的資料品質。

例如，如果輸入了 employee_id 值為 123 的職員，那麼該資料庫不應允許其他職員使用同一 ID 值。如果計劃將 employee_rating 列的值範圍設定為從 1 到 5，則資料庫不應接受 6。如果表有一 dept_id 列，該列儲存職員的部門編號，則資料庫應只允許接受公司中的有效部門編號。

資料完整性分類

在SQL Server中，根據資料完整新措施所作用的資料庫物件和範圍不同，可以將資料完整性分為以下幾種。

	實體完整性
	值域完整性
	參考完整性
	使用者定義完整性

SQL Server聯機叢書中指明：“對錶進行計劃有兩個重要步驟：識別欄位的有效值和確定如何強制列中的資料完整性。”

實體完整性

實體完整性簡單的說，就是將表中的每一行看作一個實體。實體完整性要求表的標示符列或主鍵的完整性。可以通過建立唯一索引、PRIMARY KEY約束、UNIQUE約束，以及列的IDENTITY屬性來實施實體完整性。

值域完整性

值域完整性是指給定列的輸入有效性。要求表中指定列的資料具有正確的資料類型、格式和有效資料範圍。強制域有效性的方法有：限制類型（通過資料類型）、格式（通過 CHECK 條件約束和規則）或可能值的範圍。值域完整性通過 FOREIGN KEY 約束、CHECK 條件約束、DEFAULT 定義、NOT NULL 定義和規則來實現。

參考完整性

參考完整性又稱參照完整性。參考完整性維持被參照表和參照表之間的資料一致性，他通過主鍵（PRIMARY KEY）約束和外鍵（FOREIGN KEY）約束來實現。參考完整性確保索引值在所有表中一致。這樣的一致性要求不能引用不存在的值，如果索引值更改了，那麼在整個資料庫中，對該索引值的所有引用要進行一致的更改。在被參照表中，當其主索引值被其他表所參照時，該行不能被刪除也不允許改變。在參照表中，不允許參照不存在的主索引值。

強制參考完整性時，SQL Server 禁止使用者進行下列操作：

	當主表中沒有關聯的記錄時，將記錄添加到相關表中。
	更改主表中的值並導致相關表中的記錄孤立。
	從主表中刪除記錄，但仍存在與該記錄匹配的相關記錄。

例如，對於 pubs 資料庫中的 sales 和 titles 表，參考完整性基於 sales 表中的外鍵 (title_id) 與 titles 表中的主鍵 (title_id) 之間的關係。

使用者定義完整性

使用者定義完整性使您得以定義不屬於其它任何完整性分類的特定商務規則。所有的完整性類型都支援使用者定義完整性。

建立和使用約束的目的是保證資料的完整性，約束是SQL Server強制實行的應用規則，他能夠限制使用者存放到表中資料的格式和可能值。約束作為資料庫定義的一部分在CREATE TABLE語句中聲明，所以又稱做聲明完整性條件約束。約束獨立於表結構，可以在不改變表結構情況下，通過ALTER TABLE語句來添加或者刪除。在刪除一個表時，該表所帶的所有約束定義也被隨之刪除。

PRIMARY KEY約束

在資料庫的每個表中，經常有通過一列或者多個列，唯一的標識表中的每一行。就好像我們平時使用的身份證，能夠唯一的標識每個人一樣。這樣的一列或者多個列，被稱為主鍵，通過主鍵，可以強製表的實體完整性。

每一個表中只有一個PRIMARY KEY約束，更簡單的說，他是通過建立唯一索引保證指定列的實體完整性。在使用PRIMARY KEY約束時，該列的空值屬性必須定義為NOT NULL，也就是說擁有主鍵的那一列，不可為空。

由於PRIMARY KEY約束確保唯一資料，所以經常用來定義識別欄位。識別欄位就是表中已經指派了識別屬性的列。識別屬性產生唯一數字。

有些剛剛開始接觸SQL Server編程的技術愛好者通常會有這樣的疑問，為什麼非要在一個表裡面建立主鍵？其實答案是很明顯的，建立主鍵不僅可以保證表內資料的完整性，而且在為表建立主鍵的同時，Microsoft SQL Server能夠通過為主鍵建立唯一索引強制資料的唯一性。

當在查詢中使用主鍵時，也就是利用主鍵所在的列作為關鍵字進行查詢，該所因還可以用來對資料進行快速存取。如果在PRIMARY KEY約束中未指定索引類型時，預設情況下所建立的索引為簇索引。該索引只能通過刪除PRIMARY KEY約束或其相關表的方法來刪除，而不能使用DROP INDEX語句刪除。當PRIMARY KEY約束由另一張表的FOREIGN KEY約束引用時，不能刪除PRIMARY KEY約束；要刪除它，必須先刪除FOREIGN KEY約束。

通常建立一列約束時，我們稱之為列級PRIMARY KEY約束，應用於多列時，稱之為表級PRIMARY KEY約束。

列級PRIMARY KEY約束的定義格式為：

[CONSTRAINT constraint_name] 　PRIMARY KEY [CLUSTERED | NONCLUSTERED] 　[WITH [FILLFACTOR = fillfactor]] 　[ON {filegroup | DEFAULT}]

表級PRIMARY KEY約束定義風格為：

[CONSTRAINT constraint_name] 　PRIMARY KEY [CLUSTERED | NONCLUSTERED] 　　{(column[,…n])} 　[WITH [FILLFACTOR = fillfactor]] 　[ON {filegroup | DEFAULT}]

在上面的PRIMARY KEY約束定義中，WITH子句設定為PRIMARY KEY約束所建立索引的頁面填充度，ON子句指出儲存索引的資料庫檔案組名稱。將索引檔案和表資料檔案分寸到資料庫中位於不同硬碟的資料檔案中，有利於減輕單個硬碟的負載。

DEFAULT約束

使用DEFAULT約束，如果使用者在插入新行是沒有顯示為列提供資料，系統會將預設支賦給該列。例如，在一個表的payterms列中，可以讓資料庫伺服器在使用者沒有輸入時填上”???”或者”fill in later”。預設值約束所提供的預設值約束所提供的預設值可以為常量、函數、系統零進函數、空值(NULL)等等。零進函數包括CURRENT_TIMESTAMP、SYSTEM_USER、CURRENT_USER、USER和SESSION_USER等。預設值約束的定義格式為：

[CONSREAINT constraint_name] 　DEFAULT constant_expression

其中，constraint_name參數指出所建立的預設值約束名稱。Constant_expression運算式為列提供預設值。在使用預設約束是，還應該注意以下兩點：

1.	每列只能有一個預設約束。
2.	約束運算式不能參照表中的其他列和其他表、視圖或預存程序。

CHECK約束

CHECK約束的主要作用是限制輸入到一列或多列中的可能值，從而保證SQL Server資料庫中資料的值域完整性。例如，可以在建立使用者使用庫時，強制使用者的密碼在10位以上。每個標允許建立多個CHECK約束。在CHECK約束中可以包含搜尋條件，但不能包含子查詢。

同樣，我們可以為表中的每個列建立約束，每個列可以擁有多個CHECK約束，但是如果使用CREATE TABLE語句，只能為每個列建立一個CHECK約束。如果CHECK約束被應用於多列時，他必須被定義為表級CHECK約束。

在運算式中，可以輸入搜尋條件，條件中可以包括AND或者OR一類的串連詞。列級CHECK約束只能參照被約束列，而表級CHECK約束則只能參照表中列，它不能參照其他表中列。

例如，我們使用下面的語句在TB_CHECK_CONSTRAINT表中新加入一列ZIP_CODE及其相應的CHECK約束：

ALTER Table TB_CHECK_CONSTRAINT ADD 　ZIP_CODE char(6) null 　CONSTRAINT CH_ZIP_CODE check 　　(ZIP_CODE like ‘[0-9] [0-9] [0-9] [0-9] [0-9] [0-9]’)

同樣，我們可以使用CHECK或NOCHECK來開啟或者關閉某個約束。例如，下面的語句將關閉上面建立的CH_ZIP_CODE約束：

ALTER Table TB_CHECK_CONSTRAINT 　NOCHECK CONSTRAINT CH_ZIP_CODE

如果希望使用編輯器來建立約束關係，需要在資料庫圖表中，右擊包含約束的表，然後從捷徑功能表中選擇“約束”命令。或者可以將包含約束的表開啟資料表設計工具，在資料表設計工具中右擊，然後選擇“約束”命令。

UNIQUE約束

該約束應用於表中的非主鍵列，UNIQUE約束保證一列或者多列的試題完整性，確保這些獵不會輸入重複的值。例如，表中UserName列為主鍵，但是其中還包括社會安全號碼碼列，由於所有社會安全號碼碼不可能出現重複，所以可以在此列上建立UNIQUE約束，確保不會輸入重複的社會安全號碼碼。

它與PRIMARY KEY約束的不同之處在於，UNIQUE約束可以建立在多個列之上，而PRIMARY KEY約束在一個表中只能有一個。

建立UNIQUE約束，可以使用如下辦法：

1.	在資料庫圖表中右擊將包含約束的表，然後從捷徑功能表中選擇"屬性"命令。
	-或-	為將包含約束的表開啟資料表設計工具，在資料表設計工具中右擊，然後從捷徑功能表中選擇"屬性"命令。
2.	選擇"索引/鍵"選項卡。
3.	選擇"建立"命令。系統分配的名稱出現在"索引名"框中。
4.	在"列名"下展開列的列表，選擇要將約束附加到的列。若要將約束附加到多個列，在後續行中選擇其它的列。
5.	選擇"建立 UNIQUE"複選框。
6.	選擇"約束"選項。

當儲存表或關係圖時，唯一約束即建立在資料庫中。

當希望刪除UNIQUE索引時，可以使用如下步驟：

1.	在資料庫圖表中，右擊包含約束列的表，然後從捷徑功能表中選擇"索引/鍵"命令。
	-或-	為包含約束的表開啟資料表設計工具，在資料表設計工具中右擊，然後從捷徑功能表中選擇"索引/鍵"命令。
2.	從"選定的索引"列表中選擇唯一約束。
3.	選擇"刪除"按鈕。

同樣，對於一列的UNIQUE約束，我們稱之為列級UNIQUE約束，對於多列的UNIQUE約束，我們稱之為表級UNIQUE約束。下面給出列級UNIQUE約束的定義格式：

[CONSTRAINT constraint_name] 　UNIQUE [CLUSTERED | NONCLUSTERED] 　　[WITH [FILLFACTOR = fillfactor]] 　　[ON {filegroup | DEFAULT}]

使用UNIQUE約束的過程中，還需要注意，如果要對允許空值的列強制唯一性。可以允許空值的列附加UNIQUE約束，而只能將主鍵的約束附加到不允許空值的列。但UNIQUE約束不允許表中受約束列有一行以上的值同時為空白。

例如，下面語句為TB_UNIQUE_CONSTRAINT表添加UNIQUE約束：

ALTER Table TB_UNIQUE_CONSTRAINT 　ADD 　　CONSTRAINT UN_PHONE UNIQUE (username, phone)

FOREIGN KEY約束

FOREIGN KEY約束為表中的一列或者多列資料提供資料完整性參照。通常是與PRIMARY KEY約束或者UNIQUE約束同時使用的。

例如，在BookStores表中的author_id列以及title_id列分別參照了Authors表中的author_id列以及Titles表的title_id列。在向BookStores表中插入新行或修改其中的資料時，這兩列的資料值必須在Authors表和Titles表中已經存在，否則將不能執行插入或者修改操作。

在使用FOREIGN KEY約束是，需要注意以下幾點：

1.	一個表最多隻能參照253個不同的資料表，每個表也最多隻能有253個FOREIGN KEY約束。
2.	FOREIGN KEY約束不能應用於暫存資料表。
3.	在實施FOREIGN KEY約束時，使用者必須至少擁有被參照表中參照列的SELECT或者REFERENCES許可權。
4.	FOREIGN KEY約束同時也可以參照自身表中的其他列。
5.	FOREIGN KEY約束，只能參照本身資料庫中的某個表，而不能參照其他資料庫中的表。跨資料庫的參照只能通過觸發器來實現。