CHECK 約束可在資料表欄位上強制限制資料範圍或格式,是資料完整性的重要防線。本文涵蓋建立語法、ALTER TABLE 新增、啟用/停用、效能優化實測、查詢與刪除約束的完整操作指南。
CREATE TABLE table_name ( column1 datatype NULL/NOT NULL, ... CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE] );
DISABLE 可建立但不啟用約束。
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。
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' 失敗。
ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE];
ALTER TABLE tb_supplier
ADD CONSTRAINT check_tb_supplier CHECK (supplier_name IN ('IBM','LENOVO','Microsoft'));
插入 'IBM' 成功,'DELL' 失敗。
-- 啟用 ALTER TABLE table_name ENABLE CONSTRAINT constraint_name; -- 停用 ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;
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;
查詢優化器在生成執行計劃前會進行「代數樹優化」,若偵測到條件永遠為 FALSE(如 1=2),直接回傳空結果而不掃描資料表。CHECK 約束提供的中繼資料同樣可被優化器利用:
WHERE col > 100 配合 CHECK (col BETWEEN 1 AND 50) → 優化器直接判定無資料,省去 IO。測試腳本:
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 約束,不僅保障資料品質,更能協助優化器產生更高效計劃。
SELECT constraint_name, constraint_type, table_name, search_condition, status FROM user_constraints WHERE constraint_name = 'CHECK_TB_SUPPLIER_ID';
ALTER TABLE tb_supplier DROP CONSTRAINT check_tb_supplier_id;
CHECK 約束是低成本、高效益的資料品質工具,善用它還能獲得查詢優化器的「免費加速」。建議在設計階段即納入合理的 CHECK 約束,並定期檢視約束狀態確保生效。