DB2中如何修改现有表主键列使用自定义自增函数（保留数据）

我尝试修改现有表的主键列但失败了，需要修正语句中的哪些部分？

现有已创建的Patient表：

CREATE TABLE Patient
(   pat_id char(5) NOT NULL PRIMARY KEY,
    ...
);

表中已有数据：

PAT_ID|PAT_NAME    |PAT_GENDER|PAT_BD|PAT_IC        |PAT_MOBILE |PAT_ADDR                |PAT_ALLERGY|
------+------------+----------+------+--------------+-----------+------------------------+-----------+
P0001 |John Smith  |Male      |A+    |770305021234  |019-3652365|123 Taman Muda, Selangor|none       |
P0002 |Jane Doe    |Female    |B-    |820205191123  |012-3654789|456 Taman Tea, WPKL     |peanuts    |
...
P0009 |Natalie Lim |Female    |A-    |851217145682  |012-6322565|898 Taman Umum, WPKL    |none       |
P0010 |Kelly Tan   |Female    |O+    |020408141234  |019-1212556|880 Taman Raman, WPKL   |none       |

为了生成P+4位递增数字的ID，我创建了以下函数：

CREATE FUNCTION patID_increment ()
RETURNS CHAR(5)
LANGUAGE SQL
BEGIN
    DECLARE new_pat_id CHAR(5);
    DECLARE current_id INT;
    SELECT CAST(MAX(SUBSTR(pat_id, 2)) AS INT) INTO current_id FROM patient;
    SET new_pat_id = 'P'||RIGHT('0000'||CAST(current_id + 1 AS VARCHAR(4)),4) ;
    RETURN new_pat_id;
END

执行SELECT patID_increment() FROM SYSIBM.SYSDUMMY1;能成功返回P0011。

但执行以下修改列的语句时失败：

ALTER TABLE patient
ALTER COLUMN pat_id SET DEFAULT patID_increment()

ALTER TABLE PATIENT  
MODIFY COLUMN pat_id CHAR(5) NOT NULL DEFAULT patID_increment();

得到错误：

SQL Error [42894]: DEFAULT value or IDENTITY attribute value is not valid for column "PAT_ID" in table "DB2ADMIN.PATIENT".  Reason code: "7".. SQLCODE=-574, SQLSTATE=42894, DRIVER=4.26.14
SQL Error [42601]: An unexpected token "MODIFY" was found following "ER TABLE PATIENT ".  Expected tokens may include:  "ADD".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.26.14

要求：不删除表或数据，且该表与其他表存在关联的情况下，如何修改该主键列？

• 语法错误：DB2中修改列属性的正确语法是ALTER COLUMN，你用的MODIFY COLUMN是其他数据库（如MySQL）的语法，这直接导致了SQLCODE=-104错误。
• DEFAULT值限制：DB2不允许将非确定性函数作为列的DEFAULT值。你的patID_increment()函数依赖于表中的现有数据（每次调用都会查询MAX(SUBSTR(pat_id,2))），结果会随表数据变化而改变，属于非确定性函数，这就是SQLCODE=-574（Reason code 7）的原因。

由于主键列关联其他表，不能直接修改列属性来实现自动生成ID，推荐以下两种方案：

方案一：用触发器实现自动赋值 #

创建BEFORE INSERT触发器，在插入新记录时自动调用函数生成ID：

CREATE TRIGGER trg_patient_gen_id
BEFORE INSERT ON Patient
REFERENCING NEW AS new_pat
FOR EACH ROW
WHEN (new_pat.pat_id IS NULL)
BEGIN
    SET new_pat.pat_id = patID_increment();
END

插入数据时，若不指定pat_id，触发器会自动生成符合规则的ID，同时保留原有主键约束和表关联关系。

方案二：序列+函数组合（更稳定） #

如果想让ID生成逻辑更稳定，避免每次都查询表的最大值，可以用序列配合函数：

1. 创建序列（初始值设为现有最大ID的数字部分，比如现有最大是P0010，初始值设为11）：

CREATE SEQUENCE seq_pat_id
START WITH 11
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9999
NO CYCLE;

2. 修改函数，改用序列生成ID：

CREATE OR REPLACE FUNCTION patID_increment ()
RETURNS CHAR(5)
LANGUAGE SQL
BEGIN
    DECLARE new_pat_id CHAR(5);
    SET new_pat_id = 'P'||RIGHT('0000'||CAST(NEXT VALUE FOR seq_pat_id AS VARCHAR(4)),4);
    RETURN new_pat_id;
END

3. 同样创建触发器实现插入时自动赋值（代码同方案一）。

这种方式避免了每次生成ID都查询表数据，性能更好，也符合DB2对确定性的要求（序列的NEXT VALUE单次调用结果是确定的）。

内容的提问来源于stack exchange，提问作者DMC EXC