<< Adding constraints | New column with a column-level constraint >>

Dropping constraints

<< Adding constraints | New column with a column-level constraint >>

Derby Reference Manual

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.

Dropping constraints

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).

Modifying columns
The

column-alteration

allows you to alter the named column in the following ways:

· Increasing the width of an existing VARCHAR or VARCHAR FOR BIT DATA

column. CHARACTER VARYING or CHAR VARYING can be used as synonyms for
the VARCHAR keyword.

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.

· Specifying the interval between consecutive values of the identity column.

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.

· Modifying the nullability constraint of a column.

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 constraint.

· Changing the default value for a column.

You can use DEFAULT default-value to change a column default. To disable a
previously set default, use DROP DEFAULT (alternatively, you can specify NULL as
the default-value).

Setting defaults

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

CREATE TABLE statement

Changing the lock granularity for the table

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 Derby allows you to use the LOCKSIZE clause in such a situation without
throwing an exception.) To override row-level locking for the specific table, set locking
for the table to TABLE. If you created the table with table-level locking granularity, you
can change locking back to ROW with the LOCKSIZE clause in the ALTER TABLE
STATEMENT. For information about why this is sometimes useful, see Tuning Derby.