Neoview SQL Reference Manual (R2.4)

• The INCREMENT BY option shall not be 0 (zero) or less than 0 (zero).
• The INCREMENT BY option shall not 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.
• Only the INCREMENT BY or MAXVALUE options can be altered for an IDENTITY column.
• The MAXVALUE option must be greater than the current value of the internal sequence
generator current value for the IDENTITY column.
• Only one IDENTITY column sequence generator option can be altered at a time.
• 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 a valid setting.
• When the INCREMENT BY option is altered, only the property of the internal sequence
generator is altered. The current value in the internal sequence generator table is not altered.
The current value will have already been incremented using the increment 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 will then be applied,
creating a new current value. For more information on the current value of the internal
sequence generator table, see “Considerations for CREATE TABLE LIKE” (page 79).
Example of ALTER TABLE
• This example adds a column:
ALTER TABLE persnl.project
ADD COLUMN projlead
NUMERIC (4) UNSIGNED
Example of ALTER TABLE ALTER COLUMN
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.
50 SQL Statements