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-27
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 is altered. The CURRENT_VALUE in the SG Table is not
altered. The current value will have already been incremented using the
INCREMENT BY value applied for the previous INSERT operation. The next
INSERT after the ALTER TABLE ALTER COLUMN command will obtain 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 SG
Table.
•
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