db2alter

(DB2) How to alter a existing table ID to use increment function?


I tried to modify an existing table primary key but fail. What should I correct in my statement?

For example, I have a existing patient table

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

and some data is inserted into the table

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       |

Here the question, since the id is a combination of char"P" follow but 4 digit increment, so i created a function as below:

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

Yes, the function successfully execute and return "P0011" when I execute statement SELECT patID_increment() FROM SYSIBM.SYSDUMMY1;

But I fail to alter existing pat_id column, I had tried:

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();

but end up giving me error,

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

How can I alter the primary key column without remove the data or the table in db2? (The table having connection with other table)


Solution

  • Use sequence + trigger instead.
    MAX calculation for next value is very inefficient.

    CREATE TABLE Patient
    (   pat_id char(5) NOT NULL PRIMARY KEY
      , pat_name varchar (50)
    );
    
    INSERT INTO PATIENT VALUES ('P0001', 'John Smith'), ('P0010', 'Jane Doe');
    
    CREATE SEQUENCE PATIENT_SEQ AS DEC (4) START WITH 11;
    
    CREATE TRIGGER PATIENT_BIR
    BEFORE INSERT ON PATIENT
    REFERENCING NEW AS N
    FOR EACH ROW
    SET PAT_ID = 'P' || DIGITS (NEXTVAL FOR PATIENT_SEQ);
    
    INSERT INTO PATIENT (PAT_NAME)
    VALUES
      ('Natalie Lim')
    , ('Kelly Tan');
    
    SELECT * FROM PATIENT;
    
    PAT_ID PAT_NAME
    P0001 John Smith
    P0010 Jane Doe
    P0011 Natalie Lim
    P0012 Kelly Tan

    fiddle