Oracle CHECK 檢查約束完整語法與實戰範例

Oracle CHECK 檢查約束完整語法與實戰範例

CHECK 約束在資料寫入前驗證條件運算式，**結果必須為 TRUE** 才允許寫入。支援單欄位多條件、多欄位交叉驗證、與 NOT NULL 共存。本文系統整理語法、建立、修改、刪除與實戰範例。

CHECK 約束四大特性

1. 運算式必須引用表中至少一個欄位，結果為布林值
2. 單一欄位可定義多個 CHECK 約束
3. 可與 NOT NULL 共存 (先檢查 NOT NULL，再檢查 CHECK)
4. 可定義在欄位級或表級

語法總覽

建表時定義 (欄位級)

CREATE TABLE table_name (
  col1 data_type,
  col2 data_type,
  ...
  [CONSTRAINT constraint_name] CHECK (condition_involving_multiple_columns)
);

事後新增 (ALTER TABLE)

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

實戰操作範例

範例 1：單欄位範圍驗證

-- 新增第二個條件：年齡 < 100
ALTER TABLE p3 ADD CONSTRAINT check2_p3age CHECK (page < 100);

INSERT INTO p3 VALUES (4, 'David', 100);  -- 失敗 ORA-02290 (CHECK2_P3AGE)
INSERT INTO p3 VALUES (4, 'David', 99);   -- 成功

範例 3：多欄位交叉驗證 (表級)

ALTER TABLE p3 DROP CONSTRAINT check2_p3age;

CHECK 約束限制與注意事項

進階：虛擬欄位實現複雜驗證 (12c+)

SELECT CONSTRAINT_NAME, TABLE_NAME, SEARCH_CONDITION, STATUS
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'C'
  AND CONSTRAINT_NAME NOT LIKE 'SYS_%';  -- 過濾 NOT NULL 產生的系統約束

-- 更精確：僅顯示使用者定義的 CHECK
SELECT UC.CONSTRAINT_NAME, UC.TABLE_NAME, UC.SEARCH_CONDITION
FROM USER_CONSTRAINTS UC
JOIN USER_CONS_COLUMNS UCC ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
WHERE UC.CONSTRAINT_TYPE = 'C'
  AND UC.GENERATED = 'USER NAME';

最佳實踐建議

1. 業務規則下沉：資料完整性規則 (年齡範圍、金額正數、日期邏輯) 儘量在資料庫層用 CHECK 保護
2. 命名規範：CHK_TABLE_COL_RULE (如 CHK_EMP_AGE_RANGE)
3. 效能考量：CHECK 幾乎零開銷，但複雜運算式會增加 INSERT/UPDATE CPU
4. 補充而非替代：應用層仍需前端驗證提供友善錯誤訊息，資料庫 CHECK 為最後防線
5. 避免過度設計：頻繁變更的業務規則放應用層，資料庫層只守護核心不變式