Dotjum@點部落

在系統運作時，總會與其他系統作合作，有時會直接開適當的權限給其他系統能直接新增資料到資料表中，
一般來說會將ER中資料表結構欄位相關資訊讓其他系統瞭解，既使在文件上有說明相關邏輯與
欄位與欄位間的關係，但不管是在 UI 運作時檢查 或是在 程式邏輯中運作新增資料庫欄位的檢查，
總有可能發生塞入不符合定義的資料邏輯的資料進入，
這邊分享一個簡單的實做，來透過SQL資料表(Table)中的Check與Unique來替資料欄位作正確的檢查。

在這個實做中，會先定義一個資料表(Table)

建立資料表的程式碼如下

CREATE TABLE [dbo].[DemoCheckAndUnique](
	[NO] [int] IDENTITY(1,1) NOT NULL,
	[Title] [nvarchar](100) NOT NULL,
	[StartDate] [datetime] NOT NULL,
	[EndDate] [datetime] NOT NULL,
	[Rank] [int] NOT NULL,
	[TestUNIQUE] [int] NOT NULL,
 CONSTRAINT [PK_DemoCheckAndUnique] PRIMARY KEY CLUSTERED 
(
	[NO] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

在這各資料表中，定義的運作邏輯規則：
1.EndDate 一定要大於 StartDate
2.StartDate與EndDate 都要是 2008 年開始的日期時間
3.Rank 必須是 1 - 10
4.TestUnique 是非重複的資料
雖然寫出來定義，在能夠掌握的程式下，UI與程式碼檢查都有作，但還是難確定自己與合作的廠商，
在程式最後要新增或更新資料表時，會塞入不符合邏輯的資料，導致日後不符合邏輯的運作，
導致產生系統的錯誤。

所以在這邊，期待別人的系統能遵守規則，不如設定檢查規則在資料表中比較踏實，
而設定的方法也非常簡單，如果你擅長使用 SQL Server Management Studio 的 UI
則步驟是 選擇資料表 - 條件約束 - 右鍵新增 - 輸入檢查條件運算式 - 命名該條件約束

如果你習慣是下指令的方式

ALTER TABLE [DemoCheckAndUnique]
ADD CONSTRAINT checkRank CHECK ([Rank] > 1 and [Rank] < 10)
ADD CONSTRAINT checkDate CHECK (StartDate <  EndDate );

建立好條件約束後，進行資料的測試，先新增一筆正確的資料

INSERT INTO [DemoCheckAndUnique]
(Title,StartDate,EndDate,[Rank],TestUNIQUE)
VALUES
('正確資料','2008/3/1','2008/8/1',7,5)
執行的結果畫面如下
 

這一筆符合條件約束式的正確的資料，當然一定能順利的新增進去。

再來測試不符合定義條件約束式的資料

INSERT INTO [DemoCheckAndUnique]
(Title,StartDate,EndDate,[Rank],TestUNIQUE)
VALUES('日期範圍錯誤的資料','2008/8/1','2008/1/1',7,5)

INSERT INTO [DemoCheckAndUnique]
(Title,StartDate,EndDate,[Rank],TestUNIQUE)
VALUES('日期初始範圍錯誤','2007/8/1','2008/1/1',7,5)

INSERT INTO [DemoCheckAndUnique]
(Title,StartDate,EndDate,[Rank],TestUNIQUE)
VALUES('Rank範圍錯誤的資料','2008/1/1','2008/8/1',11,5)
UPDATE [DemoCheckAndUnique]
SET [Rank] =13 WHERE [No] = 9

執行的結果畫面如下


這些不符合邏輯的資料，就會在資料表(Table)的新增及更新的時，執行檢查的條件，
來避免不符合邏輯的資料，能夠進入到資料表中。

而另一種狀況是有一個不是主鍵型態的欄位，但他必須非重複的資料，
這個時候可以透過 UNIQUE 限制的方式，設定這個欄位是唯一的欄位，
就能避免重複的資料進入資料表時。

ALTER TABLE [DemoCheckAndUnique]
ADD CONSTRAINT  UNIQUEColumn UNIQUE(TestUNIQUE) ;

透過這個實做，希望能夠讓大家瞭解到，不管在前面的UI或程式碼的檢查，
總會有可能發生不符合我們定義邏輯的資料，會進入到資料表內，
而透過在資料表中加上 Check 與 Unique，就能保護資料的邏輯性，避免當不符合邏輯資料進入資料表時，
需花更多的時間，來抓出不符合邏輯的資料。

補充：
1.Rank 為 SQL關鍵字，所以使用[]將關鍵字包起來。
2.一個區間的數字，可以使用 Between 就不需要用 AND  來作大於小於比對。

參考資料：
CHECK 條件約束
使用條件約束