Search code examples
sqldb2

How DB2(v10.5.0.5) add auto increment column to an exists table

I'm trying to add an auto increment column in an existing table of DB2.

DB2 version is v10.5.0.5.

Following is my query:

alter table DB2INST1.AAA_BJ_BOND 
ADD COLUMN id INTEGER NOT NULL DEFAULT 0;

ALTER TABLE DB2INST1.AAA_BJ_BOND ALTER COLUMN id 
set generated always as identity (start with 1);

but I got following error:

"com.ibm.db2.jcc.am.SqlSyntaxErrorException: ALTER TABLE "DB2INST1.AAA_BJ_BOND" 
specified attributes for column "ID" that are not compatible with the existing 
column.. SQLCODE=-190, SQLSTATE=42837, DRIVER=4.13.127"

What can I do to solve this problem?

Solution

  • You must drop the column DEFAULT value first. This is mentioned in the description of SQL0190N:

    If SET GENERATED ALWAYS AS (expression) is specified, but the column is already defined with a form of generation (default, identity, or expression) and there is no corresponding DROP in the same statement.

    ALTER TABLE DB2INST1.AAA_BJ_BOND 
ALTER COLUMN id drop default;

ALTER TABLE DB2INST1.AAA_BJ_BOND ALTER COLUMN id 
set generated always as identity (start with 1);