CODEWITHSUNDEEP
sqldb2
skyline
skyline

Reputation: 43

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?

Upvotes: 2

Views: 3093

Answers (2)

skyline
skyline

Reputation: 43

Now I have successfully added auto-increasing ID to the table through the following three steps:

ALTER TABLE DB2INST1.AAA_SEAT ADD COLUMN ID INTEGER NOT NULL DEFAULT 0;

ALTER TABLE DB2INST1.AAA_SEAT ALTER COLUMN ID DROP DEFAULT;

ALTER TABLE DB2INST1.AAA_SEAT ALTER COLUMN ID SET GENERATED ALWAYS AS IDENTITY (START WITH 1);

Upvotes: 2

Mark Barinstein
Mark Barinstein

Reputation: 12314

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

Upvotes: 4

Related Questions