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-34
Considerations for ALTER TABLE
Altering the MAXVALUE and INCREMENT BY options on
IDENTITY columns
The only options that can be altered for an IDENTITY column are:
•
INCREMENT BY
•
MAXVALUE
These rules apply:
•
Only one IDENTITY column sequence generator option can be altered at a time.
•
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.
•
The INCREMENT BY or MAXVALUE options can be used only on an IDENTITY
column.
•
When the INCREMENT BY option is altered, only the attributes of the internal
Sequence Generator are altered. The CURRENT_VALUE of the internal Sequence
Generator is not altered. The current value is incremented using the INCREMENT
BY value applied during previous INSERT operations. The next INSERT after the
ALTER TABLE ALTER COLUMN command obtains the CURRENT_VALUE. The
new INCREMENT BY value set by the ALTER TABLE ALTER COLUMN SET
command will then be applied, creating a new current value.
•
The MAXVALUE option cannot be 0 (zero), less than 0 (zero) or greater than the
maximum value of the data type of the IDENTITY column.
•
The MAXVALUE option must be greater than the CURRENT_VALUE of the internal
Sequence Generator.
•
The MAXVALUE option value cannot be less than the INCREMENT BY option
value.
•
A valid numeric value must be specified for the MAXVALUE option. NO
MAXVALUE is not allowed. If you use NO MAXVALUE on the ALTER TABLE
ALTER COLUMN specification, an error will be raised:
>>alter table T115T002 alter column id_key set NO MAXVALUE;
*** ERROR[1595] The MAXVALUE option for the IDENTITY column ID_KEY
must be a valid numeric value. NO MAXVALUE is not allowed.
--- SQL operation failed with errors.
•
Similarity check for an INSERT query that involves a system-generated IDENTITY
column value will fail; the query must be recompiled.
Altering the MAXVALUE attribute on IDENTITY columns
When the MAXVALUE maximum is reached for the IDENTITY column, error
ERROR[8934] is raised that says the maximum has been exceeded. Subsequent
INSERTs fail with ERROR[8934], but the CURRENT_VALUE of the internal Sequence