<< column-definition | Maximum and minimum values >>

GENERATED BY DEFAULT

<< column-definition | Maximum and minimum values >>

Derby Reference Manual

· If you specify CURRENT SCHEMA or CURRENT SQLID, the column must be a

character column whose length is at least 128.

· If the column is an integer type, the default value must be an integer literal.
· If the column is a decimal type, the scale and precision of the default value must be

within those of the column.

generated-column-spec:

[ GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
[ ( START WITH IntegerConstant
[ ,INCREMENT BY IntegerConstant] ) ] ] ]

Identity column attributes

A table can have at most one identity column.

For SMALLINT, INT, and BIGINT columns with identity attributes, Derby automatically
assigns increasing integer values to the column. Identity column attributes behave like
other defaults in that when an insert statement does not specify a value for the column,
Derby automatically provides the value. However, the value is not a constant; Derby
automatically increments the default value at insertion time.

The IDENTITY keyword can only be specified if the data type associated with the column
is one of the following exact integer types.

· SMALLINT
· INT
· BIGINT

There are two kinds of identity columns in Derby: those which are GENERATED
ALWAYS and those which are GENERATED BY DEFAULT.
GENERATED ALWAYS

An identity column that is GENERATED ALWAYS will increment the default value
on every insertion and will store the incremented value into the column. Unlike other
defaults, you cannot insert a value directly into or update an identity column that
is GENERATED ALWAYS. Instead, either specify the DEFAULT keyword when
inserting into the identity column, or leave the identity column out of the insertion
column list altogether. For example:

create table greetings
(i int generated always as identity, ch char(50));
insert into greetings values (DEFAULT, 'hello');
insert into greetings(ch) values ('bonjour');

Automatically generated values in a GENERATED ALWAYS identity column are
unique. Creating an identity column does not create an index on the column.

GENERATED BY DEFAULT

An identity column that is GENERATED BY DEFAULT will only increment and use
the default value on insertions when no explicit value is given. Unlike GENERATED
ALWAYS columns, you can specify a particular value in an insertion statement to be
used instead of the generated default value.

To use the generated default, either specify the DEFAULT keyword when inserting
into the identity column, or just leave the identity column out of the insertion column
list. To specify a value, included it in the insertion statement. For example:

create table greetings
(i int generated by default as identity, ch char(50));
-- specify value "1":
insert into greetings values (1, 'hi');
-- use generated default
insert into greetings values (DEFAULT, 'salut');
-- use generated default
insert into greetings(ch) values ('bonjour');