Auto incrementing ID value

Question

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?

Answer 1

It is possible with SQL Server. When defining an auto number column you can define the starting number and the increment:

IDENTITY(100000, 1)

Answer 2

Here's how to do it in HSQLDB.

As far as I know, all SQL databases allow you to set a seed value for the auto-increment fields.

Update: Here's a list of identity/auto-increment implementations in the major SQL databases.

Answer 3

I know it's possible with SQL Server, and I imagine it's possible with others.

With SQL Server you can set the ID column seed (starting number) and increment value.

Answer 4

You can do it with databases that use sequences, like Oracle and PostgreSQL. You specify a start value when you create the sequence.

This suggests that you can do it with HSQL as well.

Answer 5

Not sure about HSQL, but in MS SQL yes it's possible. Set the ID to auto increment and set the seed value to 100,000.

Answer 6

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 ALTER COLUMN <column name> RESTART WITH <new value>;