Identity function in HSQLDB

[saurav sarkar]

Hi All,

I am using HSQLDB for my application.
I have already created a table with a primary key.
Now i want to modify it and want to make the primary key column also an Auto incrementing one.
I saw in HSQLDB it can be achieved by the IDENTIY() function.

Could please some one please show me the correct SQL syntax for modifying the DDL for the table?.


Thanks and Regards,
Saurav

[Angelika Angley]

    
create table Person(
   number   int            not null,
   name     varchar(100)   null,
   id       int            GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) not null,
   constraint PID primary key (id)
);

[saurav sarkar]

Thanks a lot Angelika for the reply.
One more thought on the same lines, could i alter the table definition
because i have already created the table i want to add the identity function
to that.

[Angelika Angley]

You can try this one:

ALTER TABLE <tablename> ALTER COLUMN <columnname> SET GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1)};   

See more here:
http://hsqldb.org/doc/guide/ch09.html#alter_table-section