萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server CHECK 約束完整教學:建立、啟用、效能優化與管理

SQL Server CHECK 約束完整教學:建立、啟用、效能優化與管理

CHECK 約束可在資料表欄位上強制限制資料範圍或格式,是資料完整性的重要防線。本文涵蓋建立語法、ALTER TABLE 新增、啟用/停用、效能優化實測、查詢與刪除約束的完整操作指南。

0. 什麼是 CHECK 約束?

  • 限制欄位可接受的值域或條件。
  • 不可定義於 VIEW;不可包含子查詢;欄位必須屬於該資料表。

1. 建立資料表時定義 CHECK 約束

1.1 語法

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

DISABLE 可建立但不啟用約束。

1.2 範例 1:數值範圍驗證

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

測試:INSERT ... VALUES(200,...) 成功;VALUES(1,...) 失敗,報錯 ORA-02290: check constraint violated。

1.3 範例 2:強制大寫

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

插入 'LENOVO' 成功,'iPhone' 失敗。

2. ALTER TABLE 新增 CHECK 約束

2.1 語法

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

2.2 範例:限制供應商名單

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

插入 'IBM' 成功,'DELL' 失敗。

3. 啟用 / 停用 CHECK 約束

-- 啟用
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
-- 停用
ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;

3.1 建立時停用、後續啟用範例

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

3.2 CHECK 約束提升查詢效能(核心亮點)

查詢優化器在生成執行計劃前會進行「代數樹優化」,若偵測到條件永遠為 FALSE(如 1=2),直接回傳空結果而不掃描資料表。CHECK 約束提供的中繼資料同樣可被優化器利用:

  • 單表篩選:WHERE col > 100 配合 CHECK (col BETWEEN 1 AND 50) → 優化器直接判定無資料,省去 IO。
  • 分區檢視表:將不同年份資料存放於不同表,並各加 CHECK 約束限制年份範圍。查詢特定年份時,優化器自動排除不相關分區表(Partition Elimination),大幅降低掃描成本。

測試腳本:

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;
-- 查詢 2007 年 → 僅掃描 Test2007
-- 查詢 2010 年 → 常數掃描,零 IO

結論:簡單單欄、無函數的 CHECK 約束,不僅保障資料品質,更能協助優化器產生更高效計劃。

4. 查詢約束詳細資訊

SELECT constraint_name, constraint_type, table_name, search_condition, status
FROM user_constraints
WHERE constraint_name = 'CHECK_TB_SUPPLIER_ID';

5. 刪除 CHECK 約束

ALTER TABLE tb_supplier DROP CONSTRAINT check_tb_supplier_id;

小結

CHECK 約束是低成本、高效益的資料品質工具,善用它還能獲得查詢優化器的「免費加速」。建議在設計階段即納入合理的 CHECK 約束,並定期檢視約束狀態確保生效。

copyright © 萬盛學電腦網 all rights reserved