468,272 Members | 2,039 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,272 developers. It's quick & easy.

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

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
1 17044
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.

Similar topics

5 posts views Thread by grzes | last post: by
4 posts views Thread by shorti | last post: by
2 posts views Thread by bbawa1 | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.