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