<< GENERATED BY DEFAULT | generation-clause >>

Maximum and minimum values

<< GENERATED BY DEFAULT | generation-clause >>

Derby Reference Manual

Note that unlike a GENERATED ALWAYS column, a GENERATED BY DEFAULT
column does not guarantee uniqueness. Thus, in the above example, the

and

salut

rows will both have an identity value of "1", because the generated column

starts at "1" and the user-specified value was also "1". To prevent duplication,
especially when loading or importing data, create the table using the START WITH
value which corresponds to the first identity value that the system should assign.
To check for this condition and disallow it, you can use a primary key or unique
constraint on the GENERATED BY DEFAULT identity column.

By default, the initial value of an identity column is 1, and the amount of the increment is
1. You can specify non-default values for both the initial value and the interval amount
when you define the column with the key words START WITH and INCREMENT BY. And
if you specify a negative number for the increment value, Derbydecrements the value
with each insert. If this value is positive, Derby increments the value with each insert. A
value of 0 raises a statement exception.

The maximum and minimum values allowed in identity columns are determined by
the data type of the column. Attempting to insert a value outside the range of values
supported by the data type raises an exception. The following table shows the supported
ranges.

Table 1.

Maximum and minimum values for columns with generated column specs

Data Type

Maximum Value

Minimum Value

SMALLINT

32767
(java.lang.Short.MAX_VALUE)

-32768
(java.lang.Short.MIN_VALUE)

INT

2147483647
(java.lang.Integer.MAX_VALUE)

-2147483648
(java.lang.Integer.MIN_VALUE)

BIGINT

9223372036854775807
(java.lang.Long.MAX_VALUE)

-9223372036854775808
(java.lang.Long.MIN_VALUE)

Automatically generated values in an identity column are unique. Use a primary key or
unique constraint on a column to guarantee uniqueness. Creating an identity column
does not create an index on the column.

The

IDENTITY_VAL_LOCAL

function is a non-deterministic function that returns the most

recently assigned value for an identity column. See

IDENTITY_VAL_LOCAL function

for

more information.

Note: Specify the schema, table, and column name using the same case as those
names are stored in the system tables--that is, all upper case unless you used delimited
identifiers when creating those database objects.

Derby keeps track of the last increment value for a column in a cache. It also stores
the value of what the next increment value will be for the column on disk in the
AUTOINCREMENTVALUE column of the SYS.SYSCOLUMNS system table. Rolling
back a transaction does not undo this value, and thus rolled-back transactions can leave
"gaps" in the values automatically inserted into an identity column. Derby behaves this
way to avoid locking a row in SYS.SYSCOLUMNS for the duration of a transaction and
keeping concurrency high.

When an insert happens within a triggered-SQL-statement, the value inserted by the
triggered-SQL-statement into the identity column is available from ConnectionInfo only
within the trigger code. The trigger code is also able to see the value inserted by the
statement that caused the trigger to fire. However, the statement that caused the trigger
to fire is not able to see the value inserted by the triggered-SQL-statement into the
identity column. Likewise, triggers can be nested (or recursive). An SQL statement can