Neoview SQL Reference Manual (R2.4 SP2)

Recalibrate to a User-Specified Value Without SELECT
This method does not perform a SELECT on the base table containing the IDENTITY column to
obtain the current maximum value of the IDENTITY column. The user-specified value will not
be incremented using the INCREMENT BY internal sequence generator option or adjusted to
match its numbering scheme.
Rules for Recalibrating to a User-Specified Value Without SELECT
• The column to be recalibrated must exist and be an IDENTITY column in the table.
• The user-specified recalibration value must be included, be a positive number, and must
not be greater than the maximum value allowed for the data type of the IDENTITY column.
In addition, the user-specified recalibration value must not be greater than MAXVALUE
option of the internal sequence generator.
• The default specification type must be GENERATED BY DEFAULT AS IDENTITY.
• The user-specified recalibration value must not be less than the START WITH and
MINVALUE options of the internal sequence generator.
• The user-specified recalibration value will not be synchronized with the INCREMENT BY
numbering scheme. The specified value will be considered a pure override.
Example of ALTER TABLE ALTER COLUMN
For a full example of recalibrating an IDENTITY column, see “Recalibrating the Current Value
of the Internal Sequence Generator Table” (page 83).
Create a table with the IDENTITY column:
CREATE TABLE T1 (surrogate_key LARGEINT
GENERATED ALWAYS AS IDENTITY
(START WITH 99 INCREMENT BY 1
MAXVALUE 100 MINVALUE 50
NO CYCLE) NOT NULL,
b INT UNSIGNED NOT NULL,
PRIMARY KEY(surrogate_key) );
The third insert will fail with error -8934 as shown here:
insert into T1 values(default,1);
--- 1 row(s) inserted.
insert into T1 values(default,2);
--- 1 row(s) inserted.
>>insert into T1 values(default,3);
*** ERROR[8934] The MAXVALUE for the sequence generator has been exceeded.
--- 0 row(s) inserted.
Alter the table to allow new MAXVALUE and INCREMENT BY values:
ALTER TABLE T1 ALTER COLUMN SURROGATE_KEY SET MAXVALUE 900;
ALTER TABLE T1 ALTER COLUMN SURROGATE_KEY SET INCREMENT BY 2;
insert into T1 values(default,3);
--- 1 row(s) inserted.
select * from T1;
SURROGATE_KEY B
-------------------------------- ----
99 1
ALTER TABLE Statement 51