Neoview SQL Reference Manual (R2.4)

ManualsBrandsHP ManualsSoftwareHP Neoview Release 2.4 Software

CREATE TABLE t2 LIKE t1 AS SELECT a,b FROM t1;

SELECT MAX(a) from t2;

(EXPR)

--------

Table t2 is created with an internal sequence generator table START WITH value of 0. The

current value in the internal sequence generator is 0. To recalibrate the current value of the

internal sequence generator table, use the ALTER TABLE ALTER COLUMN command.

CREATE TABLE LIKE AS SELECT will fail with an error if the IDENTITY column from the target

table is a GENERATED ALWAYS AS IDENTITY column type. Only user-supplied values are

valid for the AS SELECT feature of CREATE TABLE LIKE.

Recalibrating the Current Value of the Internal Sequence Generator Table

Recalibrating the current value of the internal sequence generator may be necessary after a

CREATE TABLE AS or CREATE TABLE LIKE command. The INSERT... SELECT from the table

creation will have provided user-supplied values for the target table IDENTITY column. The

internal sequence generator associated with the target table IDENTITY column would remain

at its initial default value based on the START WITH property. This could lead to possible

duplicates occurring in subsequent row insertions.

To recalibrate the current value of the internal sequence generator table, use the ALTER TABLE

ALTER COLUMN SET INCREMENT BY command. The INCREMENT BY value is temporarily

set to a value greater than the maximum value currently in the IDENTITY column. An insert is

performed updating the current value of the internal sequence generator to this new maximum.

The INCREMENT BY property is then immediately reset to its desired value. For details on the

syntax of ALTER TABLE, see the “ALTER TABLE Statement” (page 45).

Temporarily altering the internal sequence generator INCREMENT BY property will reset the

property of the internal sequence generator table, but it will not alter the current value in the

internal sequence generator table. A single insert into the table containing the IDENTITY column

will cause an update to the internal sequence generator table. The insert will use the new

INCREMENT BY property and increment the internal sequence generator current value to the

desired larger number.

A second ALTER TABLE ALTER COLUMN SET INCREMENT BY command should then be

immediately performed to reset the INCREMENT BY property to its desired setting. Subsequent

inserts will then succeed as the current value of the internal sequence generator table will generate

unique values for the IDENTITY column.

Following is an example of table t2 that has been created using CREATE TABLE LIKE AS t1.

Table t1 had an IDENTITY column named 'a' with a START WITH property of 0. Table t2 has

been created like table t1. Column 'a' is also an IDENTITY column defined with a START WITH

property of 0. This could lead to duplicate errors being generated until the current value of the

internal sequence generator table is larger than the maximum value of the IDENTITY column

in table t2. To alleviate the problem, the internal sequence generator table will be recalibrated

using the following commands:

SELECT MAX(a) FROM t2;

(EXPR)

--------

ALTER TABLE t2 ALTER COLUMN a SET INCREMENT BY 3; -- One larger than the maximum value for 'a'

INSERT INTO t2 VALUES(DEFAULT,4); -- The current value is still 0.

*** ERROR[8102] The operation is prevented by a unique constraint.

-- In this case, the insert fails with a duplicate error, but

-- the current value of the internal sequence generator has been updated.

-- Alter the table again to reset the INCREMENT BY property to its desired value.

CREATE TABLE Statement 81