The ALTER TABLE statement allows you to:
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:
The system will automatically
generate values for the identity column. But now you need to manually insert
some data into the identity column:
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 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.
The syntax for the
Just as in CREATE TABLE, if the column definition includes a unique or primary key constraint, the column cannot contain null values, so the NOT NULL attribute must also be specified (SQLSTATE 42831).
ALTER TABLE ADD CONSTRAINT adds a table-level constraint to an existing table. Any supported table-level constraint type can be added via ALTER TABLE. The following limitations exist on adding a constraint to an existing table:
ALTER TABLE ADD UNIQUE or PRIMARY KEY provide a shorthand method of defining a primary key composed of a single column. If PRIMARY KEY is specified in the definition of column C, the effect is the same as if the PRIMARY KEY(C) clause were specified as a separate clause. The column cannot contain null values, so the NOT NULL attribute must also be specified.
For information on the syntax of constraints, see
ALTER TABLE DROP COLUMN allows you to drop a column from a table.
The keyword COLUMN is optional.
The keywords CASCADE and RESTRICT are also optional. If you specify neither CASCADE nor RESTRICT, the default is CASCADE.
If you specify RESTRICT, then the column drop will be rejected if it would cause a dependent schema object to become invalid.
If you specify CASCADE, then the column drop should additionally drop other schema objects which have become invalid.
The schema objects which can cause a DROP COLUMN RESTRICT to be rejected include: views, triggers, primary key constraints, foreign key constraints, unique key constraints, check constraints, and column privileges. If one of these types of objects depends on the column being dropped, DROP COLUMN RESTRICT will reject the statement.
You may not drop the last (only) column in a table.
DROP COLUMN is not allowed if sqlAuthorization is true (see DERBY-1909).
CASCADE/RESTRICT doesn't consider whether the column being dropped is used in any indexes. When a column is dropped, it is removed from any indexes which contain it. If that column was the only column in the index, the entire index is dropped.
ALTER TABLE DROP CONSTRAINT drops a constraint on an existing table. To drop an unnamed constraint, you must specify the generated constraint name stored in SYS.SYSCONSTRAINTS as a delimited identifier.
Dropping a primary key, unique, or foreign key constraint drops the physical index that enforces the constraint (also known as a backing index).
The
To increase the width of a column of these types, specify the data type and new size after the column name.
You are not allowed to decrease the width or to change the data type. You are not allowed to increase the width of a column that is part of a primary or unique key referenced by a foreign key constraint or that is part of a foreign key constraint.
To set an interval between consecutive values of the identity column, specify the integer-constant. You must previously define the column with the IDENTITY attribute (SQLSTATE 42837). If there are existing rows in the table, the values in the column for which the SET INCREMENT default was added do not change.
You can add the NOT NULL constraint to an existing column. To do so there must not be existing NULL values for the column in the table.
You can remove the NOT NULL constraint from an existing column. To do so the column must not be used in a PRIMARY KEY or UNIQUE constraint.
You can specify a default value for a new column. A default value is the value that is inserted into a column if no other value is specified. If not explicitly specified, the default value of a column is NULL. If you add a default to a new column, existing rows in the table gain the default value in the new column.
For more information
about defaults, see
The
LOCKSIZE clause allows you to override row-level locking for the specific
table, if your system uses the default setting of row-level locking. (If your
system is set for table-level locking, you cannot change the locking granularity
to row-level locking, although
An ALTER TABLE statement causes all statements that are dependent on the table being altered to be recompiled before their next execution. ALTER TABLE is not allowed if there are any open cursors that reference the table being altered.