在SQL Server中使用檢查約束來驗證資料

什麼是檢查約束?

檢查約束是一個規則，它確認一個SQL Server表中某條記錄中的資料可接受的欄位值。檢查約束協助執行值域完整性。值域完整性定義了一個資料庫表中欄位的有效值。檢查約束可以驗證一個單獨欄位或一些欄位的值域完整性。你對一個單獨的欄位可以有多個檢查完整性。如果被插入或更新的資料違反了一個檢查約束，那麼資料庫引擎將不允許這個插入或更新的操作發生。

檢查約束包括一個邏輯運算式，用以確認什麼是有效運算式。邏輯運算式可能是一個單獨的運算式比如“Salary < 200000.00”，或多個運算式，比如“RentalDate > GETDATE() and RentalDate < DATEADD(YY,1,GETDATE())”。如果一個邏輯運算式的一個檢查約束返回了FALSE值，那麼這個檢查約束將限制這個表中資料插入或更新。對於邏輯運算式返回的是FALSE以外的值的所有記錄將通過這個檢查約束並允許記錄被更新或插入。為了這個記錄能夠被插入或更新，與給定INSERT或UPDATE語句相關的所有資料都不能進行檢查約束失敗(返回一個FALSE值)。檢查約束可以在欄位層級或表層級被建立。

在一個CREATE TABLE語句上建立檢查約束

建立檢查約束的一個方法是在表建立時進行。這是一個簡單的CREATE TABLE指令碼，它建立了一個單獨的檢查約束：

CREATE TABLE dbo.Payroll ( ID int PRIMARY KEY, PositionID INT, SalaryType nvarchar(10), Salary decimal(9,2) CHECK (Salary < 150000.00) );

這裡我有一個CHECK 子句，它與Salary欄位關聯。這是一個欄位層級的約束。如果你建立一個欄位層級的約束，那麼你在你的檢查約束的邏輯運算式中只能使用這個欄位名稱。這個檢查約束只允許Salary欄位低於$150,000.00。當我的表建立之後，這個CHECK約束也將被建立，並被賦予一個系統產生的約束名稱。如果你想在一個CREATE TABLE操作期間命名你的檢查約束，那麼你可以運行下面的代碼：

CREATE TABLE dbo.Payroll ( ID int PRIMARY KEY, PositionID INT, SalaryType nvarchar(10), Salary decimal(9,2) CONSTRAINT CK_Payroll_Salary CHECK (Salary < 150000.00) );

這裡我命名了我的檢查約束CK_Payroll_Salary。

上面的每個例子都建立了一個單獨的條件欄位檢查約束。一個檢查約束運算式可以有多個條件。下面是一個例子，它顯示了一個有多個條件的檢查約束：

CREATE TABLE dbo.Payroll ( ID int PRIMARY KEY, PositionID INT, SalaryType nvarchar(10), Salary decimal(9,2) CONSTRAINT CK_Payroll_Salary CHECK (Salary > 10.00 and Salary < 150000.00) );

記住，為了讓SQL Server 拒絕一條記錄，這個檢查約束的邏輯運算式的最終結果需要是FALSE。因此，在這個例子中，這個檢查約束驗證了一個Salary大於$10.00並小於$150,000.00。當這個檢查約束中的這些條件中的任何一個為FALSE，那麼在Payroll表中將不會插入或更新一條記錄，並會顯示一個錯誤資訊。

如果你想建立一個表層級的檢查約束，那麼你可以運行下面的代碼：

CREATE TABLE dbo.Payroll ( ID int PRIMARY KEY, PositionID INT, Salary decimal(9,2), SalaryType nvarchar(10), CHECK (Salary > 10.00 and Salary < 150000.00) );

這裡我建立了一個單獨的資料表條件約束，它檢查Salary欄位，但是它不是關聯到欄位，而是關聯到這個表。在這個檢查約束中我可以使用我的表中的任何欄位，只要我想，因為它是一個表檢查約束，但是在我的例子中，我只使用了Salary欄位。注意，這個CHECK子句將使得SQL Server產生一個檢查約束名稱，因為我沒有給這個約束名稱。

在一個現有的表上建立一個檢查約束

有時，在你設計和建立了一個表後，你想對一個表添加一個檢查約束。這可以通過使用ALTER TABLE 語句來完成。下面是這麼做的例子：　

ALTER TABLE dbo.Payroll WITH NOCHECK ADD CONSTRAINT CK_Payroll_SalaryType CHECK (SalaryType in ('Hourly','Monthly','Annual'));

在這裡我建立了一個檢查約束，它將檢查我的Payroll表中的所有記錄在SalaryType欄位中只有“Hourly”、“ Monthly”或“Annual”值。我還用一個名稱命名了我的檢查約束，在這個例子中是“CK_Payroll_SalaryType”。

你可以使用一個單獨的ALTER TABLE語句來一次添加多個檢查約束到你的表中。下面是這麼做的例子：

ALTER TABLE dbo.Payroll WITH NOCHECK ADD CONSTRAINT CK_Payroll_SalaryType CHECK (SalaryType in ('Hourly','Monthly','Annual')), CONSTRAINT CK_Payroll_Salary CHECK (Salary > 10.00 and Salary < 150000.00);

在這裡我已經使用一個單獨的ADD CONSTRAINT子句添加了SalaryType和Salary約束。

建立多個欄位約束

你沒有必要建立只能檢查一個單獨欄位的值的約束。你可以建立一次檢查多個欄位中的值的約束。例如，如果我想建立一個檢查上面所建立的Salary和SalaryType約束的單獨約束，那麼可以使用下面的代碼：

ALTER TABLE dbo.Payroll WITH NOCHECK ADD CONSTRAINT CK_Payroll_Salary_N_SalaryType CHECK (SalaryType in ('Hourly','Monthly','Annual') and Salary > 10.00 and Salary < 150000.00);

這個單獨約束所做的事情和上面兩個約束一樣。記住，當你這麼做時，要瞭解是SalaryType 、Salary 還是兩個欄位都違反了你的檢查約束就更很困難了。.

前一個例子的另一個方法是在不只一個的欄位中使用這個值，從而確定某一指定欄位值是否是有效。例如，假設我想確保當我輸入一個“Hourly” SalaryType時，我希望Salary小於$100.00，或輸入“Monthly” SalaryType時，Salary不超過$10,000，而當輸入一個“Annual” SalaryType時任何Salary數值都可以。要實現這個約束，我使用下面的ADD CONSTRAINT子句：

ALTER TABLE dbo.Payroll WITH NOCHECK ADD CONSTRAINT CK_Payroll_SalaryType_Based_On_Salary CHECK ((SalaryType = 'Hourly' and Salary < 100.00) or (SalaryType = 'Monthly' and Salary < 10000.00) or (SalaryType = 'Annual'));

這裡，我將多個欄位條件一起使用並使用一個“or”條件來分隔它們，所以我的檢查約束可以驗證每個不同的SalaryType的Salary數量。

瞭解當值為Null時會發生什麼

回憶下在本篇文章的“什麼是檢查約束”章節中所說的關於記錄是怎樣只在檢查約束得出結果FALSE時才認為檢查約束沒有通過。因此，欄位中的NULL值可能允許你輸入資料到你的資料庫中，而這並不滿足你的需求。 假設我在我的payroll表上只有CK_Paryroll_SalaryType 檢查約束。這裡需要回憶的就是這個檢查約束：

ALTER TABLE dbo.Payroll WITH NOCHECK ADD CONSTRAINT CK_Payroll_SalaryType CHECK (SalaryType in ('Hourly','Monthly','Annual'));

現在你運行下面的INSERT語句：

INSERT INTO dbo.Payroll values (1, 1, 'Hourly',25.00);

INSERT INTO dbo.Payroll values (2, 2, NULL, 25.00);

INSERT INTO dbo.Payroll values (3, 3, 'Horly',25.00);

會發生什麼?會只有第一個INSERT語句起作用嗎?第二個和第三個INSERT語句會怎樣?它們都違反CK_Payroll_SalaryType嗎?結果是只有第三個INSERT語句失敗了。它失敗是由於SalaryType輸入錯誤，它不是“Hourly”、“ Monthly”或“Annual”。為什麼第二個INSERT沒有得出false呢?顯然，“NULL”不是SalaryTypes的有效值。第二個INSERT語句起作用的原因是在第二個INSERT語句運行時，CK_Payroll_SalaryType約束不是FALSE。因此，資料庫引擎插入了這條記錄。那麼為什麼會出現這種情況呢?這是因為NULL值用在比較操作中時，它被當作UNKNOWN。因為UNKNOWN不是FALSE，所以沒有違反檢查約束。因此，當你編寫你的檢查約束時，你需要對需要拒絕包含NULL值的地方很謹慎。另一個編寫上面的約束從而使得拒絕SalaryType值為NULL的方法是如下編寫你的約束：

ALTER TABLE dbo.Payroll WITH NOCHECK ADD CONSTRAINT CK_Payroll_SalaryType CHECK ((SalaryType in ('Hourly','Monthly','Annual')) and SalaryType is not NULL);

另一個選擇是使SalaryType成為一個非NULL欄位。如果你這麼做就不會違反檢查約束，但是反過來你會得到一個錯誤資訊顯示你不能插入一個NULL值到你的表中。

通過檢查約束進行資料驗證

通過使用檢查約束，你可以確保你的資料庫只包含通過了約束的資料。這使得你可以讓資料庫引擎控制你的資料驗證。這麼做將使得你的應用程式不需要在每個你希望插入一條記錄或更新一條記錄到一個表中的地方都寫資料驗證規則的代碼。檢查約束是執行資料驗證的一個簡潔方法。