Adding generated key columns to existing tables seems to be complicated in DB2. The following code adds a new generated primary key id to the table tablename. Granted, the code is ugly, but it works. I was not able to come up with a better solution in time.
ALTER TABLE tablename ADD COLUMN id INTEGER NOT NULL WITH DEFAULT 0
ALTER TABLE tablename ALTER COLUMN id GENERATED BY DEFAULT AS IDENTITY
REORG TABLE tablename
UPDATE tablename SET id = DEFAULT
ALTER TABLE tablename ALTER COLUMN id SET PRIMARY KEY
EDIT: I tested this code with DB2 9.7, but it should work on earlier versions of DB2.
hi,
When i execute the third(ALTER TABLE tablename ALTER COLUMN id SET NOT NULL), i meet erro,like this:
[IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token “NOT” was found following “alter column id set”. Expected tokens may include: “DEFAULT”. SQLSTATE=42601
Statement processed with ERROR.
My DB is DB2 V8.3
BTW,ALTER TABLE tablename ADD COLUMN id INTEGER
UPDATE tablename SET id = 0
make that two become one,like this:
ALTER TABLE tablename ADD COLUMN id INTEGER NOT NULL with default 0;
Thank you for your reply.
Glee
Thanks for the info. Apparently, DB2 8.3 does not support adding NOT NULL constraints to existing columns. However, I have modified my code so that it should work on DB2 8.3 now. Please give it a try.
Thanks, very useful. I suggested your page in:
http://www.dbforums.com/db2/1650358-adding-identity-column-existing-table-2.html
Cheers