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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

smauldin@ingrian.com - 02 Feb 2006 03:50 GMT

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

Reply to this Message

Serge Rielau - 02 Feb 2006 13:30 GMT

> Creating a table with an identity column works fine
> create table test(a integer, id integer generated always as identity )
[quoted text clipped - 11 lines]
> 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

Signature

Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

Reply to this Message