column_identity_attributes - Advanced SQL Engine - Teradata Database

The identity column is ANSI SQL:2011 compliant.

The QITS column cannot also be defined to be an identity column.

ALWAYS

Specifies that identity column values are always system-generated.

You cannot insert values into, nor can you update, an identity column defined as GENERATED ALWAYS AS IDENTITY.

BY DEFAULT

Example: Creating a Queue Table Defined with an Identity Column

This example creates a queue table defined with an identity column for the queue sequence number, which is named QSN. Because the primary index for the table is simple, defined only on the QITS column, it must be defined as a NUPI:

    CREATE TABLE qtbl_4, QUEUE (
      qits  TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
      qsn   INTEGER GENERATED ALWAYS AS IDENTITY (CYCLE),
      col_3 INTEGER)
    PRIMARY INDEX (qits);

The value of a QSN is expected to be unique under normal circumstances because queue table rows should be consumed at a high enough rate that the queue (which is modeled as one table row per enqueued event) does not grow large enough for the QSN value to be reused by completing a cycle through the identity column values.

Example: Creating a Queue Table with Constraints and a UPI

This example creates a queue table with constraints and a UPI to uniquely identify rows externally.

Note that while the definitively non-unique QITS column, col_1_qits, is a component of the unique primary index for the table, that index is composite, and its other component has the attributes NOT NULL and UNIQUE, ensuring that the composite primary index is unique.

    CREATE SET TABLE qtbl_2, QUEUE, NO FALLBACK (
      col_1_qits TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
      col_2      INTEGER NOT NULL UNIQUE,
      col_3      INTEGER, CONSTRAINT check_1 CHECK (col_3 > 0))
    UNIQUE PRIMARY INDEX primary_1 (col_1_qits, col_2);

Example: Creating a Queue with a UPI and a NUSI

This example creates a queue table with a UPI defined on the queue sequence number identity column, QSN, and a NUSI defined on the queue insertion timestamp, QITS:

    CREATE TABLE qtbl_5, QUEUE (
      qits  TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
      qsn   INTEGER GENERATED ALWAYS AS IDENTITY (NO CYCLE),
      col_3 INTEGER)
    UNIQUE PRIMARY INDEX (qsn)
    INDEX qits;

With a high rate of consumption of queue table rows, you would typically replace the NO CYCLE option with CYCLE. CYCLE has the advantage of enabling the reuse of QSN numbers while not failing when MAXVALUE is exceeded. The UPI on QSN would then guarantee that, at any point, the value of QSN is unique.