SQL Server CHECK 約束完整教學：建立、啟停、效能優化與進階應用

CHECK 約束是資料完整性的最後一道防線，能在資料寫入時強制驗證欄位值是否符合業務規則。本文系統涵蓋 語法建立、ALTER TABLE 管理、啟用/停用、效能優化原理、分區視圖應用、中繼資料查詢，並釐清 CHECK 約束與驗證觸發器的差異。

一、核心概念與限制

• 定義於資料表欄位，不可用於 View。
• 僅能參考同一資料表的欄位，不可包含子查詢。
• 單一資料表可建立多個 CHECK 約束，邏輯以 AND 結合。
• 違反約束時拋出錯誤 Msg 547, Level 16，交易回滾。

二、建立語法與實戰範例

1. CREATE TABLE 時內建約束

CREATE TABLE table_name (
    column1 datatype NULL/NOT NULL,
    column2 datatype NULL/NOT NULL,
    CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE]
);

DISABLE 關鍵字：建立時暫不生效，後續需 ENABLE。

範例 1：數值範圍驗證 (供應商編號 100~9999)

CREATE TABLE tb_supplier (
    supplier_id INT,
    supplier_name VARCHAR(50),
    contact_name VARCHAR(60),
    CONSTRAINT chk_supplier_id CHECK (supplier_id BETWEEN 100 AND 9999)
);

驗證：

INSERT INTO tb_supplier VALUES (200, 'dlt', 'stk');      -- 成功
INSERT INTO tb_supplier VALUES (1, 'david', 'stk');       -- 失敗：Msg 547

範例 2：強制欄位大寫 (產品名稱)

CREATE TABLE tb_products (
    product_id INT NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    supplier_id INT NOT NULL,
    CONSTRAINT chk_product_name_upper CHECK (product_name = UPPER(product_name))
);

驗證： INSERT ... VALUES (2, 'LENOVO', 2) 成功；VALUES (1, 'iPhone', 1) 失敗。

2. ALTER TABLE 事後新增約束

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE];

範例： 限制供應商名稱僅能為 'IBM'、'LENOVO'、'Microsoft'

ALTER TABLE tb_supplier
ADD CONSTRAINT chk_supplier_name CHECK (supplier_name IN ('IBM','LENOVO','Microsoft'));

三、約束啟用與停用控制

四、CHECK 約束的隱藏效能優化威力

多數開發者僅視 CHECK 為資料驗證工具，實則查詢最佳化器會利用約束資訊簡化執行計劃。

案例 1：常數邏輯推導

SELECT * FROM tb_supplier WHERE supplier_id = 50;  -- 約束為 BETWEEN 100 AND 9999

最佳化器識別 50 不滿足約束，直接回傳空結果，**完全略過表掃描**。

案例 2：分區視圖消除 (Partition View Elimination)

建立年度分區表 + CHECK 約束限制日期範圍：

CREATE TABLE Test2007 (ProductReviewID INT IDENTITY, ReviewDate DATETIME NOT NULL);
ALTER TABLE Test2007 WITH CHECK ADD CONSTRAINT CK_Test2007 CHECK (ReviewDate >= '2007-01-01' AND ReviewDate < '2008-01-01');

CREATE TABLE Test2008 (ProductReviewID INT IDENTITY, ReviewDate DATETIME NOT NULL);
ALTER TABLE Test2008 WITH CHECK ADD CONSTRAINT CK_Test2008 CHECK (ReviewDate >= '2008-01-01' AND ReviewDate < '2009-01-01');

CREATE VIEW testPartitionView AS SELECT * FROM Test2007 UNION ALL SELECT * FROM Test2008;

查詢不同年度：

SELECT * FROM testPartitionView WHERE ReviewDate = '2007-01-01';  -- 僅掃描 Test2007
SELECT * FROM testPartitionView WHERE ReviewDate = '2008-01-01';  -- 僅掃描 Test2008
SELECT * FROM testPartitionView WHERE ReviewDate = '2010-01-01';  -- 常數掃描，零 IO

結論： 當 CHECK 約束為單欄位、無函數、簡單範圍時，最佳化器能精準推導，實現分區剪枝與常數摺疊，大幅提升查詢效能。

五、中繼資料查詢與移除

查看約束詳細資訊

SELECT
    constraint_name, constraint_type, table_name,
    search_condition, status
FROM user_constraints  -- 或 all_constraints / dba_constraints
WHERE constraint_name = 'CHK_SUPPLIER_ID';

移除約束

ALTER TABLE tb_supplier DROP CONSTRAINT chk_supplier_id;

六、最佳實踐總結

1. 業務規則下沉： 能用 CHECK 解決的驗證，盡量不用應用層或觸發器。
2. 命名規範： 採用 CHK_表名_欄位_規則 格式 (如 CHK_Order_Amount_Positive)。
3. 大量載入策略： 匯入前 DISABLE 約束，載入後 ENABLE 並驗證現有資料 (WITH CHECK)。
4. 效能紅利： 設計分區表/視圖時，務必配合 CHECK 約束定義邊界，啟用分區消除。
5. 避免複雜表達式： 包含函數、多欄位關聯的 CHECK 約束最佳化器難以利用，改用觸發器或應用層驗證。

善用 CHECK 約束，不僅能守護資料品質，更能讓查詢最佳化器「讀懂」您的資料分布，產出更優執行計劃。