SQL/MX 3.2.1 Reference Manual (H06.26+, J06.15+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-005
2-132
Considerations for CREATE TABLE
You can declare IDENTITY columns in the CREATE TABLE statement. IDENTITY
columns can be used as surrogate keys. They can also be used to uniquely identify
records with the same key.
Difference Between GENERATED ALWAYS AS IDENTITY and GENERATED BY
DEFAULT AS IDENTITY
You can use GENERATED BY DEFAULT AS IDENTITY to allow both user-supplied
and system-generated column values for the IDENTITY column. The GENERATED
ALWAYS AS IDENTITY option provides system-generated unique values only. It does
not allow user-supplied IDENTITY column values.
Generating the System-Generated Value for an IDENTITY Column
NonStop SQL/MX generates the next value for an IDENTITY column in these ways:
•
You can specify DEFAULT as a value for the IDENTITY column in the INSERT
statement, provided it is not the only column.
For example: INSERT INTO tbl1 (DEFAULT, 10); assuming the first column
is defined as an IDENTITY column.
•
An INSERT statement specifying the columns to be inserted, but leaving out the
IDENTITY column.
For example: INSERT INTO tbl1(b) values (10); assuming that tbl1 has
an IDENTITY column and the IDENTITY column has been omitted from the
column list.
Generating Unique Values for an IDENTITY Column
The sequence generator feature provides a method for generating unique values for an
IDENTITY column. NonStop SQL/MX guarantees to generate unique values if the
IDENTITY column is defined as GENERATED ALWAYS AS IDENTITY with the NO
CYCLE option.
Available Values for an IDENTITY Column
The IDENTITY column can be defined as signed LARGEINT, unsigned
INTEGER,and unsigned SMALLINT. Each data type has a natural maximum value.
The settings chosen for START WITH, MINVALUE, MAXVALUE, and INCREMENT BY
create a valid range of available numbers before a maximum is reached. The settings
MAXVALUE and INCREMENT BY can be altered using ALTER TABLE ALTER
COLUMN to change an artificially low range specified during the CREATE TABLE
process. For more information, see Altering the MAXVALUE and INCREMENT BY
options on IDENTITY columns on page 2-34.
Duplicate Values for an IDENTITY Column
Duplicates can be generated for an IDENTITY column if the IDENTITY column is
defined as GENERATED BY DEFAULT AS IDENTITY and you specify a duplicate
value for the column.