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-39
Examples of ALTER TABLE
colC INT UNSIGNED,
primary key(colA) );
insert into T004 (colB,colC) values(1,1);
--- 1 row(s) inserted.
-- colA will have value '700';
select * from T004;
COLA COLB COLC
----------------- ---------- ----------
700 1 1
--- 1 row(s) selected.
-- Recalibrate the internal sequence generator
-- using the user-specified value with SELECT. This
-- will succeed as it is less than the maximum value
-- and greater than the MINVALUE and START WITH values.
-- The recalibrate will reset the CURRENT_VALUE in the SG Table
-- to 710.
alter table t004 alter column colA recalibrate to 710;
--- SQL operation complete.
-- The next number generated for the IDENTITY column colA
-- will be 710, the new recalibrated CURRENT_VALUE
insert into T004 (colB, colC) values(2,2);
--- 1 row(s) inserted.
-- Notice the value 710 for colA.
select * from T004;
COLA COLB COLC
--------- -------- ----------
700 1 1
710 2 2
--- 2 row(s) selected.
-- The recalibrate performs a SELECT on table T004 to obtain
MAX(colA).
-- Since MAX(colA) is greater than the recalibrate value of 702,
ERROR[1599]
-- is raised.
alter table T004 alter column colA recalibrate to 702;
*** ERROR[1599] The recalibration value is less than or equal to
the current maximum value, 710, of the IDENTITY column, COLA, for
the table, CAT.SCH.T004.
--- SQL operation failed with errors.