Using the IDENTITY Column

Declare a column as identity to have Oracle NoSQL Database automatically assign values to it, where the values are generated from a sequence generator. See Sequence Generator.

Syntax

identity_definition ::=
GENERATED (ALWAYS | (BY DEFAULT [ON NULL])) AS IDENTITY
["(" sequence_generator_attributes+ ")"]

Semantics

An INTEGER, LONG, or NUMBER column in a table can be defined as an identity column. The system can automatically generate values for the identity column using a sequence generator. See Sequence Generator section. A value for an identity column is generated during an INSERT, UPSERT, or UPDATE statement.

An identity column can be defined either as GENERATED ALWAYS or GENERATED BY DEFAULT.

GENERATED ALWAYS

The system always generates a value for the identity column. An exception is raised if the user supplies a value for the identity column.

GENERATED BY DEFAULT

The system generates a value for the identity column only if the user does not supply a value for it. If ON NULL is specified for GENERATED BY DEFAULT, the system will generate a value when the user supplies a NULL value or the value evaluates to a NULL.

Identity Column Characteristics

  • There can be only one identity column per table.
  • The identity column of a table can be part of the primary key or the shard key.
  • Secondary indexes can be created on an identity column.
  • The set of values that may be assigned to an identity column is defined by its data type and the attributes of the sequence generator attached to it. The values are always integer numbers. Both negative and positive INTEGER are possible. If you want only positive values, then set the START WITH attribute to 1 and specify a positive INCREMENT BY attribute. When you specify CYCLE, numbers will be regenerated from the MINVALUE. In this case, if you want positive values you must also set MINVALUE to be a positive number.
  • The system generates unique values for an identity column that is defined as GENERATED ALWAYS and has the sequence generator attribute NO CYCLE set. Otherwise, duplicate identity values can occur in the following scenarios:
    • The identity column is defined as GENERATED BY DEFAULT and the user supplies a value during an insert or update statement that already exists in the table for the identity column.
    • The CYCLE option is set for an identity column that is defined as GENERATED BY DEFAULT or GENERATED ALWAYS and the sequence generator reaches the end of the cycle and then recycles through the sequence generator to generate values that were generated in the previous cycle.
    • If the identity column properties are altered using the alter table statement so that during an insert or update operation the user can supply a value that already exists.
  • Sequence generator attributes can be altered using the alter table statement.
  • Holes in the sequence can occur when:
    • The application caches identity values and shuts down or crashes before using all of the cached values for inserting rows.
    • Identity values are assigned during a transaction that is rolled back.
  • For example on inserting rows with an identity column see, Inserting Rows with an IDENTITY Column section.

Example 5-9 Identity Column using GENERATED ALWAYS

CREATE TABLE T1 (
    id INTEGER GENERATED ALWAYS AS IDENTITY
    (START WITH 2 INCREMENT BY 2 MAXVALUE 200 NO CYCLE),
    name STRING,
    PRIMARY KEY (id)
);

In the above example, the INTEGER column id is defined as a GENERATED ALWAYS AS IDENTITY column and is the primary key for table T. The system will start to generate values 2 through 200 incrementing by 2. So values for the id column will be 2,4,6,8,…200. Since the NO CYCLE option is defined, the system will raise an exception after the number 200 is generated saying it has reached the end of the sequence generator.

Example 5-10 Identity Column using GENERATED BY DEFAULT

CREATE TABLE T2 (
    id LONG GENERATED BY DEFAULT AS IDENTITY
    (START WITH 1 INCREMENT BY 1 CYCLE CACHE 200),
    account_id INTEGER,
    name STRING,
    PRIMARY KEY (account_id)
);

In the above example, the creation of a table with an identity column on id column is shown. The id column is of type LONG, is defined as GENERATED BY DEFAULT, and it is not a primary key column. This example also demonstrates how to specify a CYCLE and CACHE sequence generator attributes. The system will only generate a value during INSERT/UPSERT/UPDATE if the user did not supply a value. It starts off generating values 1, 2, 3,... up to the maximum value of the LONG datatype, and once it exhausts all the sequence generator values, it will cycle through and re-start from the MINVALUE value of the sequence generator, which in this case, is the minimum value of the LONG datatype. The CACHE value of 200 means that every time a client uses up the values in the cache and asks for the next value, the system will give it 200 values to fill up the cache. In this example, the system will give values 1 through 200 when a client asks for a value for the first time. Another client operating on the same table may get values 201-300, so on and so forth.