By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,905 Members | 902 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,905 IT Pros & Developers. It's quick & easy.

How can I add an identity column to an existing table?

P: n/a
Creating a table with an identity column works fine
create table test(a integer, id integer generated always as identity )

When I attempt to add an identity column it fails.
create table test2(a integer)

alter table test2 add column id integer generated always as identity

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "IDENTITY" was found following "AS".
Expected
tokens may include: "<left_paren>". SQLSTATE=42601

Feb 2 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
sm******@ingrian.com wrote:
Creating a table with an identity column works fine
create table test(a integer, id integer generated always as identity )

When I attempt to add an identity column it fails.
create table test2(a integer)

alter table test2 add column id integer generated always as identity

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "IDENTITY" was found following "AS".
Expected
tokens may include: "<left_paren>". SQLSTATE=42601

It's a two step process:
alter table test2 add column id integer not null with default 0;
alter table test2 alter column id set generated always as identity;

The reason is that DB2 needs to know what to do with the existing rows.
In the case above all your existing rows get the value 0.
If you wish you can now run:
UPDATE TEST2 SET id = DEFAULT;
This will generate identity values for each row and you can add a unique
index afterwards.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
Feb 2 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.