Identity Column Parameters - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

The identity column parameters are optional and can be specified in any order.

START WITH constant
The lowest integer value in the system-generated numeric series for an identity column. The default is 1.
The value you specify can be any exact negative or positive integer within the range of the data type for the column as long as it is less than MAXVALUE for an incremental series or greater than MINVALUE for a decremental series.
INCREMENT BY constant
The interval on which to increment system-generated numbers.
The value you specify can be a negative integer. The default is 1.
The value can be any integer (except 0) less than or equal to the value of DBS Control parameter IdCol Batch Size.
MINVALUE constant
The minimum value to which a system-generated numeric series can decrement. MINVALUE applies only to system-generated numbers.
You can specify any integer value with an absolute value less than the value specified for START WITH.
The default is the minimum integer number for the data type defined for the column.
When cycling is not enabled, the sum of the specified values for START WITH and INCREMENT BY must be greater than MINVALUE. If they are not, then Vantage generates only one number before the minimum limit is exceeded.
NO MINVALUE
Numbering restarts its cycle from the minimum value that can be expressed for the data type of the column when the MAXVALUE limit is reached.
You can only specify NO MINVALUE when the INCREMENT BY interval is a negative number. The default is the minimum value for the data type specified for the identity column.
The following rules apply to INCREMENT BY and CYCLE specifications for MINVALUE.
  • If INCREMENT BY is a positive number and you also specify CYCLE, then renumbering begins from MINVALUE when MAXVALUE is reached.
  • If INCREMENT BY is a negative number, then MINVALUE, if specified, must be a whole number such that MINVALUE ≤ START WITH.
  • If you do not specify INCREMENT BY, but do specify NO CYCLE, then MINVALUE is not applicable for positive increments.
No warning or error is returned if you specify a MINVALUE with NO CYCLE.
MAXVALUE constant
The maximum value to which a system-generated numeric series can increment. MAXVALUE applies only to system-generated numbers. Its value can be any integer with a value that is greater than the value specified for START WITH.
The default is the maximum number for the data type defined for the column.
When cycling is not enabled, the sum of the specified values for START WITH and INCREMENT BY must be less than MAXVALUE. If it is not, then Vantage generates only one number before the maximum limit is exceeded.
The following rules apply to INCREMENT BY and CYCLE specifications for MAXVALUE.
  • If INCREMENT BY is a positive number, but you do not specify CYCLE, then MAXVALUE, if specified, must be a whole number such that MAXVALUE ³ START WITH.

    MAXVALUE cannot be larger than the maximum value for the data type assigned to the identity column.

  • If INCREMENT BY is a negative number and you also specify CYCLE, then renumbering begins with MAXVALUE when MINVALUE is reached.
  • If you do not specify INCREMENT BY, but do specify NO CYCLE, then MAXVALUE is not applicable for negative increments.

    No warning or error is returned if you specify a MAXVALUE with NO CYCLE.

NO MAXVALUE
Numbering restarts its cycle from the minimum value that can be expressed for the data type of the column when the maximum value for the type is reached.
You can only specify NO MAXVALUE when the INCREMENT BY interval is a positive number. The default is the maximum value for the data type specified for the identity column.
CYCLE
System-generated values can be recycled when their minimum or maximum is reached.
NO CYCLE
This is the default.