Derby alter existing column of empty table to be identity

Question

I'm trying the following:

psAddPK = conn.prepareStatement("ALTER TABLE users ALTER usr    
                      GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)");        

psAddPK.execute(); 

but get

java.sql.SQLSyntaxErrorException: Syntax error: Encountered "GENERATED"

on create usr is defined NOT NULL

Answer 1

You cannot Alter the column to re-define it as an Identity, you must Create it as an Identity column from the start. Or you could delete the column and then re-add it as an Identity.

Here's the Derby Alter table spec

The ALTER TABLE statement allows you to:

• add a column to a table
• add a constraint to a table drop a column from a table
• drop an existing constraint from a table
• increase the width of a VARCHAR or VARCHAR FOR BIT DATA column
• override row-level locking for the table (or drop the override)
• change the increment value and start value of the identity column
• change the nullability constraint for a column
• change the default value for a column

Syntax:

ALTER TABLE table-name
{
    ADD COLUMN column-definition |
    ADD CONSTRAINT clause |
    DROP [ COLUMN ] column-name [ CASCADE | RESTRICT ]
    DROP { PRIMARY KEY | FOREIGN KEY constraint-name | UNIQUE 
     constraint-name | CHECK constraint-name | CONSTRAINT constraint-name }
    ALTER [ COLUMN ] column-alteration |
    LOCKSIZE { ROW | TABLE }
}

column-alteration

column-name SET DATA TYPE VARCHAR(integer) |
column-name SET DATA TYPE VARCHAR FOR BIT DATA(integer) |
column-name SET INCREMENT BY integer-constant |
column-name RESTART WITH integer-constant |
column-name [ NOT ] NULL |
column-name [ WITH | SET ] DEFAULT default-value |
column-name DROP DEFAULT

column-definition

simple-column-name [ data-type ]
[ column-level-constraint ]*
[ [ WITH ] DEFAULT default-constant-expression
  | generation-clause
]