您现在的位置：程式碼之家 >> 數據庫知識 > >> DB2 資料庫 >> DB2 綜合知識

DB2 身分欄位 (Auto-increment) 實作指南：GENERATED AS IDENTITY 完整語法

編輯：DB2 綜合知識

前言

DB2 使用 GENERATED ALWAYS AS IDENTITY 或 GENERATED BY DEFAULT AS IDENTITY 實作自動遞增欄位 (對應 Oracle Sequence/Trigger、SQL Server IDENTITY、MySQL AUTO_INCREMENT)。本文詳細說明語法選項、建表範例、ALTER TABLE 新增身分欄位、計算欄位 (Generated Column) 進階用法。

一、基本語法

column_name DATA_TYPE NOT NULL
    GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
    ( START WITH start_value
      INCREMENT BY increment_value
      MINVALUE min_value | NO MINVALUE
      MAXVALUE max_value | NO MAXVALUE
      CYCLE | NO CYCLE
      CACHE cache_size | NO CACHE
      ORDER | NO ORDER )

選項	說明
`ALWAYS`	系統強制產生值，不可在 INSERT/UPDATE 中指定該欄位值 (嘗試指定會報錯)。
`BY DEFAULT`	系統預設產生值，但允許 INSERT/UPDATE 明確指定值 (適合資料遷移保留舊鍵值)。
`START WITH`	起始值 (預設 1)。
`INCREMENT BY`	遞增幅度 (預設 1)，可為負數 (遞減)。
`MINVALUE/MAXVALUE`	最小/最大值；到達邊界視 `CYCLE` 決定是否循環。
`CACHE n`	預先配置 n 個值於記憶體，提升高併發插入效能 (預設 20)；資料庫異常關閉會導致缺口。
`ORDER`	保證跨分區/成員產生值的全域遞增順序 (效能較差)。

二、建表範例

2.1 主鍵身分欄 (ALWAYS)

CREATE TABLE nomination (
    nomination_id BIGINT NOT NULL PRIMARY KEY
        GENERATED ALWAYS AS IDENTITY,
    nominee     CHAR(6) NOT NULL,
    nominator   CHAR(6) NOT NULL,
    reason      VARCHAR(250),
    nom_date    DATE NOT NULL,
    category_id INTEGER NOT NULL,
    CHECK (nominee <> nominator) NOT ENFORCED ENABLE QUERY OPTIMIZATION,
    FOREIGN KEY category_exists (category_id)
        REFERENCES category (category_id) ON DELETE RESTRICT
);

插入時不可指定 nomination_id：

INSERT INTO nomination (nominee, nominator, reason, nom_date, category_id)
VALUES ('A001', 'B002', 'Good job', CURRENT DATE, 10);

2.2 客製化序列參數 (Category 表)

CREATE TABLE category (
    category_id INTEGER PRIMARY KEY
        GENERATED ALWAYS AS IDENTITY
        (START WITH 1 INCREMENT BY 1 MINVALUE 0 MAXVALUE 999999999 NO CYCLE CACHE 5 NO ORDER),
    category_name VARCHAR(50) NOT NULL,
    eligibility   VARCHAR(250)
);

三、ALTER TABLE 新增身分欄位

既有表新增自動遞增主鍵：

ALTER TABLE category
    ADD COLUMN upper_cat_name VARCHAR(50)
        GENERATED ALWAYS AS (UPPER(category_name));

此為 計算欄位 (Generated Column)，非身分欄位，但展示 GENERATED ALWAYS AS (expression) 語法。

四、計算欄位 (Generated Columns) 進階

CREATE TABLE orders (
    order_id    INTEGER GENERATED ALWAYS AS IDENTITY,
    price       DECIMAL(10,2),
    qty         INTEGER,
    total_price DECIMAL(12,2) GENERATED ALWAYS AS (price * qty),
    tax         DECIMAL(10,2) GENERATED ALWAYS AS (price * qty * 0.05),
    created_ts  TIMESTAMP GENERATED ALWAYS AS (CURRENT TIMESTAMP)
);

計算欄位值由系統自動依運算式衍生，不可手動修改。
可建立索引：CREATE INDEX idx_orders_total ON orders(total_price);
支援 deterministic UDF 做運算式。

五、身分欄位管理指令

-- 查看身分欄位屬性
SELECT COLNAME, IDENTITY, GENERATED, START, INCREMENT, MINVALUE, MAXVALUE, CYCLE, CACHE, ORDER
FROM SYSCAT.COLUMNS
WHERE TABSCHEMA = CURRENT SCHEMA AND TABNAME = 'NOMINATION';

-- 重設身分值 (下一個值)
ALTER TABLE nomination ALTER COLUMN nomination_id RESTART WITH 1000;

-- 臨時允許指定身分值 (資料遷移)
ALTER TABLE nomination ALTER COLUMN nomination_id SET GENERATED BY DEFAULT;
-- 遷移完成後改回
ALTER TABLE nomination ALTER COLUMN nomination_id SET GENERATED ALWAYS;