Search code examples
sqlprimary-keyidentityhsqldb

Auto incrementing ID value

I have an HSQLDB database with a generated ID and I want the auto-incrementing values to always be above 100,000. Is this possible with HSQLDB? Is this possible with any database?

Solution

  • According to the HSQL Documentation:

    Since 1.7.2, the SQL standard syntax is used by default, which allows the initial value to be specified. The supported form is( INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH n, [INCREMENT BY m])PRIMARY KEY, ...). Support has also been added for BIGINT identity columns. As a result, an IDENTITY column is simply an INTEGER or BIGINT column with its default value generated by a sequence generator.

    ...

    The next IDENTITY value to be used can be set with the

    ALTER TABLE <table name> ALTER COLUMN <column name> RESTART WITH <new value>;