DB2 建表約束實戰範本：主鍵、檢查、外鍵與查詢優化完整語法

約束是資料完整性的最後防線。DB2 支援豐富的約束類型：PRIMARY KEY、UNIQUE、CHECK、FOREIGN KEY、NOT NULL，並提供 NOT ENFORCED / ENABLE QUERY OPTIMIZATION 等進階選項平衡效能與完整性。

完整建表示例：員工主檔

CREATE TABLE employee (
  empno     INTEGER      NOT NULL PRIMARY KEY,
  job       VARCHAR(10)  CONSTRAINT chk_job CHECK (job IN ('Engineer','Sales','Manager')),
  age       SMALLINT     NOT NULL,
  salary    DECIMAL(9,2) NOT NULL,
  deptno    CHAR(3)      NOT NULL,
  hiredate  DATE         NOT NULL WITH DEFAULT CURRENT DATE,
  -- 表級複合檢查約束
  CONSTRAINT chk_age_salary CHECK (NOT (age < 30 AND salary > 6000)),
  -- 外鍵參考部門表
  CONSTRAINT fk_dept FOREIGN KEY (deptno) REFERENCES department(deptno)
    ON DELETE RESTRICT ON UPDATE CASCADE
) IN userspace1 INDEX IN idxspace1;

進階約束選項深度解析

NOT ENFORCED + ENABLE QUERY OPTIMIZATION

CREATE TABLE empdata (
  empno   INTEGER NOT NULL,
  sex     CHAR(1) NOT NULL CONSTRAINT sex_ok CHECK (sex IN ('M','F')) 
    NOT ENFORCED ENABLE QUERY OPTIMIZATION,
  salary  INTEGER NOT NULL CONSTRAINT salary_ok CHECK (salary BETWEEN 0 AND 100000)
    NOT ENFORCED ENABLE QUERY OPTIMIZATION
);

優化器如何利用 CHECK 約束

-- 範例：salary CHECK (salary BETWEEN 0 AND 100000)
SELECT * FROM empdata WHERE salary > 200000;
-- 優化器識別條件永無滿足可能，直接回傳空結果集（無掃描）

SELECT * FROM empdata WHERE salary > 50000;
-- 優化器知道上限 100000，可精確估算選擇率

約束命名規範建議

-- 格式：<類型縮寫>_<表名>_<欄位/用途>
PK_EMP_EMPNO              -- Primary Key
UK_EMP_EMAIL              -- Unique Key
FK_EMP_DEPT               -- Foreign Key
CHK_EMP_SALARY_RANGE      -- Check Constraint
CHK_EMP_AGE_SALARY_LOGIC  -- 複合業務邏輯 Check

常見約束組合模式

1. 代碼表標準化

CREATE TABLE status_code (
  code      CHAR(2) NOT NULL PRIMARY KEY,
  descr     VARCHAR(50) NOT NULL,
  active    CHAR(1) NOT NULL DEFAULT 'Y' CHECK (active IN ('Y','N')),
  sort_seq  SMALLINT NOT NULL DEFAULT 0
);

2. 金額欄位防呆

amount    DECIMAL(15,2) NOT NULL CHECK (amount >= 0),
discount  DECIMAL(5,2)  NOT NULL DEFAULT 0 CHECK (discount BETWEEN 0 AND 1)

3. 日期邏輯驗證

start_date DATE NOT NULL,
end_date   DATE CHECK (end_date IS NULL OR end_date >= start_date)

約束管理操作

-- 查看表所有約束
db2 "select constname, type, text from syscat.checks where tabname='EMPLOYEE'"
db2 "select constname, reftabname, deleterule, updaterule from syscat.references where tabname='EMPLOYEE'"

-- 暫時停用約束（大量載入用）
db2 "set integrity for employee off"
-- 載入完成後重新驗證
db2 "set integrity for employee immediate checked"

-- 卸除約束
db2 "alter table employee drop constraint chk_age_salary"

-- 新增約束（現有資料需符合）
db2 "alter table employee add constraint uk_email unique (email)"

效能與維護建議

1. 核心交易表：全 ENFORCED，確保資料正確性
2. 資料倉儲/暫存表：NOT ENFORCED ENABLE QUERY OPTIMIZATION，載入效能優先
3. 定期驗證：對 NOT ENFORCED 約束，排程執行 SET INTEGRITY ... IMMEDIATE CHECKED 發現髒資料
4. 避免過度複雜 CHECK：含子查詢、函數呼叫的 CHECK 會嚴重拖慢 INSERT/UPDATE
5. 外鍵索引：被參考欄位（父表 PK）自動有索引；參考欄位（子表 FK）手動建立索引避免鎖升級與刪除父記錄時全表掃描