Identity column is supported on both the Block File System and Object File System. However, you can only run queries related to an identity column table on the primary cluster.
The following rules and restrictions apply to specifying identity columns as part of a table definition.
- Only one identity column can be specified per table.
- An identity column need not be the first column defined for a table.
- An identity column need not be a primary index column.
For example, a NUPI table can also have an identity column.
- You cannot specify an identity column for a nonpartitioned NoPI table.
- You can specify an identity column for a column-partitioned table.
- An identity column cannot be part of any of the following types of index:
- Composite primary index
- Composite secondary index
- Join index
- Value-ordered index
- You cannot perform atomic upsert operations on tables with an identity column as their primary index.
- An identity column must be defined with an exact numeric data type. For the ANSI SQL:2011 definition of an exact numeric data type, see Numeric Data Types .Even when the data type for an identity column is specified as BIGINT, the largest identity column value the system generates is restricted to a size that can be accommodated by a DECIMAL(18,0), NUMBER(18,0), or NUMERIC(18,0) data type.The following are examples of valid numeric data types for identity columns.
- BYTEINT
- DECIMAL(n,0)
- INTEGER
- NUMERIC(n,0)
- NUMBER(n,0)
- SMALLINT
- BIGINT
The following table lists the MAXVALUE and MINVALUE defaults for identity column data types.
Data Type Default MAXVALUE Default MINVALUE DECIMAL(1,0) NUMERIC(1,0)
9 -9 DECIMAL(2,0) NUMERIC(2,0)
99 -99 ... ... ... DECIMAL(18,0) NUMERIC(18,0)
NUMBER(18,0)
The largest identity column value the system generates is restricted to a size that can be accommodated by any of the following data types.- DECIMAL(18,0)
- NUMBER(18,0)
- NUMERIC(18,0)
999,999,999,999,999,999 -999,999,999,999,999,999 BYTEINT 127 -127 SMALLINT 32,767 -32,767 INTEGER 2,147,483,647 -2,147,483,647 BIGINT Even when the data type for an identity column is specified as BIGINT, the largest identity column value the system generates is restricted to a size that can be accommodated by a DECIMAL(18,0), NUMBER(18,0), or NUMERIC(18,0) data type.
9,223,372,036,854,775,807 -9,223,372,036,854,775,807
The following are examples of invalid data types for identity columns.
- Nonnumeric types, including DateTime, INTERVAL, Period, Geospatial, BLOB, and CLOB types.
- DECIMAL(n,m) where m is not 0
- DOUBLE PRECISION
- FLOAT
- NUMERIC(n,m) where m is not 0
- NUMBER (approximate type)
- REAL
Table Type Identity Column Description MULTISET GENERATED ALWAYS Cannot have duplicate rows. SET - GENERATED ALWAYS
- GENERATED BY DEFAULT
MULTISET GENERATED BY DEFAULT Can have duplicate rows. - If you insert values into a GENERATED BY DEFAULT IDENTITY column SET table, the inserted row (but not the inserted value for the identity column) must be unique or the system does not accept it.
- If you insert values into a GENERATED BY DEFAULT IDENTITY column in a MULTISET table, and those values must be unique, then you must explicitly constrain the column to be unique by specifying that the column is a UPI, a USI, a PRIMARY KEY, or UNIQUE. Because Vantage treats nulls as equal, you can only insert 1 null into a column constrained to be UNIQUE. Therefore, defining any such column to be both UNIQUE and NOT NULL is typically preferable.
Identity Column Action for User-Defined Insert Values GENERATED ALWAYS Replaces user-defined insert values with system-generated identity values. GENERATED BY DEFAULT Accepts user-defined insert values unless otherwise constrained.
A column specified to be GENERATED BY DEFAULT AS IDENTITY with NO CYCLE only generates a unique column value for inserted rows that contain a null identity column.
To make sure the uniqueness of GENERATED BY DEFAULT column values:
- You must specify NO CYCLE.
- Any user-specified values you specify must be outside the range of any system-generated values.
- You must enforce the uniqueness of the user-specified values yourself.
- You cannot specify any of the following column attributes for an identity column.
- DEFAULT
- BETWEEN
- COMPRESS
- CHECK constraints
- FOREIGN KEY … REFERENCES constraints