AUTO_INCREMENT and IDENTITY Sequences

Column constraints AUTO_INCREMENT and IDENTITY are synonyms that associate a column with a sequence. This sequence automatically increments the column value as new rows are added.

You define an AUTO_INCREMENT/IDENTITY column in a table as follows:

CREATE TABLE table-name...
  (column-name {AUTO_INCREMENT | IDENTITY} [(args)], ...)

where args is 1 to 3 optional arguments that let you control sequence behavior (see Arguments below).

AUTO_INCREMENT/IDENTITY sequences are owned by the table in which they are defined, and do not exist outside that table. Unlike named sequences, you cannot manage an AUTO_INCREMENT/IDENTITY sequence with ALTER SEQUENCE. For example, you cannot change the schema of an AUTO_INCREMENT/IDENTITY sequence independently of its table. If you move the table to another schema, the sequence automatically moves with it.

You can obtain the last value generated for an AUTO_INCREMENT/IDENTITY sequence by calling Vertica meta-function LAST_INSERT_ID.

Arguments

AUTO_INCREMENT/IDENTITY constraints can take between 0 and three arguments. These arguments let you specify the column's start value, how much it increments or decrements, and how many unique numbers each node caches per session.

You specify these arguments as follows:

# arguments Description
None

The following default settings apply:

  • The starting value is 1.
  • Values increment by at least 1.
  • Each node caches 250,000 unique numbers per session for this sequence.
1

Specifies how many unique numbers each node can cache per session, as follows:

  • >1 specifies how many unique numbers each node caches per session.
  • 0 or 1 specifies to disable caching. 
IDENTITY(cache)

Default: 250,000
2 or 3

Set as follows:

{AUTO_INCREMENT | IDENTITY} (start, increment[, cache)] )

  • start: The first value that is set for this column.

    Default: 1

  • increment: A positive or negative integer that specifies the minimum amount to increment or decrement the column value from its value in the previous row.

    Default: 1

    Setting this argument to a value of X guarantees that column values always increment by at least X. However, column values can sometimes increment by more than X unless you also set the cache value to 0 or 1 (no cache).

  • cache: One of the following:

    • >1 specifies how many unique numbers each node can cache per session for this sequence.
    • 0 or 1 specifies to disable caching.

    Default: 250,000

    For details, see Sequence Caching.

Restrictions

The following restrictions apply to AUTO_INCREMENT/IDENTITY columns:

  • A table can contain only one AUTO_INCREMENT/IDENTITY column.

    A table with an AUTO_INCREMENT/IDENTITY column can also contain one or more columns that are set to named sequences.

  • AUTO_INCREMENT/IDENTITY values are never rolled back, even if a transaction that tries to insert a value into a table is not committed.
  • You cannot change the value of an AUTO_INCREMENT/IDENTITY column.

Examples

The following example shows how to use the IDENTITY column-constraint to create a table with an ID column. The ID column has an initial value of 1. It is incremented by 1 every time a row is inserted.

  1. Create table Premium_Customer:
    2. => CREATE TABLE Premium_Customer(
     ID IDENTITY(1,1), 
     lname VARCHAR(25),
     fname VARCHAR(25), 
     store_membership_card INTEGER
);
=> INSERT INTO Premium_Customer (lname, fname, store_membership_card ) 
     VALUES ('Gupta', 'Saleem', 475987);

    The IDENTITY column has a seed of 1, which specifies the value for the first row loaded into the table, and an increment of 1, which specifies the value that is added to the IDENTITY value of the previous row.

  2. Confirm the row you added and see the ID value: 
    => SELECT * FROM Premium_Customer;
 ID | lname | fname  | store_membership_card
----+-------+--------+-----------------------
  1 | Gupta | Saleem |                475987
(1 row)
  3. Add another row: 
    => INSERT INTO Premium_Customer (lname, fname, store_membership_card)
   VALUES ('Lee', 'Chen', 598742);
  4. Call the Vertica function LAST_INSERT_ID. The function returns value 2 because you previously inserted a new customer (Chen Lee), and this value is incremented each time a row is inserted: 
    => SELECT LAST_INSERT_ID();
 last_insert_id
----------------
               2
(1 row)
  5. View all the ID values in the Premium_Customer table: 
    => SELECT * FROM Premium_Customer;
 ID | lname | fname  | store_membership_card
----+-------+--------+-----------------------
  1 | Gupta | Saleem |                475987
  2 | Lee   | Chen   |                598742
(2 rows)

The next three examples illustrate the three valid ways to use IDENTITY arguments.These examples are valid for the AUTO_INCREMENT argument also.

The first example uses a cache of 100, and the defaults for start value (1) and increment value (1): 

=> CREATE TABLE t1(x IDENTITY(100), y INT);

The next example specifies the start and increment values as 1, and defaults to a cache value of 250,000:

=> CREATE TABLE t2(y IDENTITY(1,1), x INT);

The third example specifies start and increment values of 1, and a cache value of 100:

=> CREATE TABLE t3(z IDENTITY(1,1,100), zx INT);