SQL/MX 3.2 Reference Manual (H06.25+, J06.14+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2 Reference Manual—691117-001
2-119
Considerations for CREATE TABLE

An IDENTITY column can be the clustering key or part of the clustering key.

An IDENTITY column can be defined on a HASH or a RANGE partitioned table.

An IDENTITY column can be part of an INDEX.

An IDENTITY column can be part of CHECK and RI constraints.

You can alter the MAXVALUE and INCREMENT BY sequence generator options of
an IDENTITY column using the ALTER TABLE ALTER COLUMN statement. See
Altering the MAXVALUE and INCREMENT BY options on IDENTITY columns on
page 2-27.

INSERT...SELECT operations on tables with an IDENTITY column is supported.

CREATE TABLE LIKE on a source table with an IDENTITY column is supported.
The target table will inherit the source table column and sequence generator
attributes.

When a table with an IDENTITY column is dropped, the internal Sequence
Generator is dropped. Metadata entries for the internal Sequence Generator are
cleared and the SG Table associated with the IDENTITY column is dropped. See
SG Table on page 2-118.
Restrictions for an IDENTITY Column

At most one IDENTITY column can be specified in a table.

The IDENTITY column cannot have a NOT NULL NOT DROPPABLE constraint. If
not specified, the system will add the constraint.

An IDENTITY column definition supports the following data types only: LARGEINT,
unsigned INTEGER, and unsigned SMALLINT data types.

The NO CYCLE option is the only cycle option supported.

Only ascending IDENTITY values are supported. IDENTITY values are called
ascending if the increment value is a non-negative value.

These options can be specified one time only for each table:

START WITH

INCREMENT BY

MAXVALUE | NO MAXVALUE

MINVALUE | NO MINVALUE

NO CYCLE

The START WITH value must be less than or equal to the MAXVALUE and greater
than or equal to the MINVALUE. If the START WITH option is not specified, the
start value is the MINIMUM value.