<< ALTER TABLE statement | Adding constraints >>

column-alteration

<< ALTER TABLE statement | Adding constraints >>

Derby Reference Manual

The syntax of DataType is described in

. The DataType can be omitted only

if you specify a generation-clause. If you omit the DataType, the type of the generated
column is the type of the generation-clause. If you specify both a DataType and a
generation-clause, the type of the generation-clause must be assignable to DataType.

For details on DefaultConstantExpression, see

Column default

column-alteration

In the column-alteration, SET INCREMENT BY integer-constant, specifies the interval
between consecutive values of the identity column. The next value to be generated for
the identity column will be determined from the last assigned value with the increment
applied. The column must already be defined with the IDENTITY attribute.

RESTART WITH integer-constant specifies the next value to be generated for the
identity column. RESTART WITH is useful for a table that has an identity column that
was defined as GENERATED BY DEFAULT and that has a unique key defined on that
identity column. Because GENERATED BY DEFAULT allows both manual inserts and
system generated values, it is possible that manually inserted values can conflict with
system generated values. To work around such conflicts, use the RESTART WITH
syntax to specify the next value that will be generated for the identity column. Consider
the following example, which involves a combination of automatically generated data and
manually inserted data:

CREATE TABLE tauto(i INT GENERATED BY DEFAULT AS IDENTITY, k INT)
CREATE UNIQUE INDEX tautoInd ON tauto(i)
INSERT INTO tauto(k) values 1,2

The system will automatically generate values for the identity column. But now you need
to manually insert some data into the identity column:

INSERT INTO tauto VALUES (3,3)
INSERT INTO tauto VALUES (4,4)
INSERT INTO tauto VALUES (5,5)

The identity column has used values 1 through 5 at this point. If you now want the
system to generate a value, the system will generate a 3, which will result in a unique
key exception because the value 3 has already been manually inserted. To compensate
for the manual inserts, issue an ALTER TABLE statement for the identity column with
RESTART WITH 6:

ALTER TABLE tauto ALTER COLUMN i RESTART WITH 6

ALTER TABLE does not affect any view that references the table being altered. This
includes views that have an "*" in their SELECT list. You must drop and re-create those
views if you wish them to return the new columns.

Derby raises an error if you try to change the DataType of a generated column to a type
which is not assignable from the type of the generation-clause. Derby also raises an error
if you try to add a DEFAULT clause to a generated column.

Adding columns