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 an associated sequence generator. The SG is the table’s manager for tracking the IDENTITY column’s current, next, and total number of values.

You create an IDENTITY column as part of a CREATE TABLE name DDL statement, or add an IDENTITY column to an existing table with an ALTER TABLE name DDL statement.

Syntax

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

For definition of the syntax component referenced in this statement, see:

• 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.

Sequence Generator Attributes

An SG has several attributes that define its behavior, such as the starting value for its IDENTITY column, or the number of values stored in cache. You can optionally define some SG attributes when you create an IDENTITY column, or use all default values. For more information about the Sequence Generator attributes, see Sequence Generator.

Note: Using an IDENTITY column in any table does not force uniqueness. If your application requires unique values for every row of an IDENTITY column, you must create the column as GENERATED ALWAYS AS IDENTITY, and never permit any use of the CYCLE SG attribute.

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.

• You can add, remove, or change rows of an IDENTITY column, though certain limitations exist on such updates, depending on how you create the IDENTITY column, and whether it is a Primary Key.

• Secondary indexes can be created on an identity column.

• Dropping a table that was created with an IDENTITY column also removes the Sequence Generator.

• 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.

• If more than one client accesses a table with an IDENTITY column defined for unique values this way, each client is assigned contiguous value sets to its SG cache. These sets do not overlap with other client sets. For example, Client1 is assigned values 0001 - 1000, while Client2 has 1001 - 2000, and so on. Thus, as each client adds rows to the table, the IDENTITY values can run as 0001, 1001, 0002, 1002, 1003, and so on, as both clients use their own cache when adding rows. The IDENTITY column values are guaranteed to be unique, but not necessarily contiguous, because each client has its own set of cache values, and adds rows at different speeds and times.

• Sequence generator attributes can be altered using the alter table statement.

• Users require table privileges to create tables with an IDENTITY column. For a description of user privileges, see KVStore Required Privileges in the Security Guide.

• 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.

• Creating MR tables with an IDENTITY column is not supported and the system returns an error message. For more information on MR table creation, see Create MR Tables.

• For example on inserting rows with an identity column see, Inserting Rows with an IDENTITY Column section.

Example 1 - 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 2 - 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.