SQL/MX 3.1 Reference Manual (H06.23+, J06.12+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.1 Reference Manual—663850-001
2-120
Considerations for CREATE TABLE
•
The INCREMENT BY option cannot be 0 (zero), less than 0 (zero), and cannot be
greater than the maximum value of the data type of the IDENTITY column.
•
If the MAXVALUE option is not specified, or if NO MAXVALUE is specified, the
maximum value is the maximum value of the data type of the IDENTITY column.
The MAXVALUE option value must be greater than the value of the MINVALUE
and a valid numeric value for the IDENTITY column data type.
•
If the MINVALUE option is not specified, or if NO MINVALUE is specified, then the
minimum value is the minimum value of the data type of the IDENTITY column. In
the case of the IDENTITY column being of data type LARGEINT, the minimum
default value will be zero, not -9223372036854775808.
•
Mixed DML and DDL operations performed under the same user transaction are
not supported for an INSERT operation that contains an IDENTITY column. TMF
Error 73 can occur because the DDL and DML operations share the same user
transaction on a table lock on the SG Table.
•
For a table with only one column, which is an IDENTITY column, the tuple list of an
INSERT statement cannot consist of only DEFAULT values. You must specify the
input values, otherwise error 3431 will be raised. For example, this statement
issues error 3431:
INSERT INTO t1 VALUES (DEFAULT), (DEFAULT);
•
You cannot add an IDENTITY column by using the ALTER TABLE statement.
•
Expressions involving the keyword DEFAULT are not allowed as IDENTITY column
values. You must specify the keyword DEFAULT or supply a valid value. Error 3411
will be raised if an expression is specified for an IDENTITY column value. For
example, this statement raises error 3411 indicating that an expression,
DEFAULT+15 is used as a value for IDENTITY column, assuming that the first
column is an IDENTITY column.
INSERT INTO t1 VALUES (DEFAULT+15, 45);
•
UPDATE operations on IDENTITY columns defined as GENERATED ALWAYS AS
IDENTITY are not allowed.
•
For IDENTITY columns defined as type LARGEINT, the maximum value is
9223372036854775806, one less than the LARGEINT maximum.
•
For an IDENTITY column, the tuple list cannot have mixed user and DEFAULT
values specified. You must specify values for all tuples in the tuple list or specify
DEFAULT for all tuples in the tuple list. For example, error 3414 is raised in the
following case, assuming that the first column in the table t_id_s is an IDENTITY
column. Notice that the third row contains a user specified value of '50' for the
IDENTITY column and the other rows specify DEFAULT.
INSERT INTO t_id_s values (DEFAULT,’1’,1),
(DEFAULT,’2’,2),
(50,’3’,3),
(DEFAULT,’4’,4)
(DEFAULT,’5’,5);