<< Interaction with the dependency system | column-alteration >>

ALTER TABLE statement

<< Interaction with the dependency system | column-alteration >>

Derby Reference Manual

ij> CREATE INDEX i1 ON mytable(mycol);
0 rows inserted/updated/deleted
-- p1 is temporarily invalidated because of new index
ij> execute p1;
1 row inserted/updated/deleted
-- Derby automatically recompiles p1 and executes it
ij> DROP TABLE mytable;
0 rows inserted/updated/deleted
-- Derby permits you to drop table
-- because result set of p1 is closed
-- however, the statement p1 is temporarily invalidated
ij> CREATE TABLE mytable (mycol INT);
0 rows inserted/updated/deleted
ij> INSERT INTO mytable VALUES (1), (2), (3);
3 rows inserted/updated/deleted
ij> execute p1;
1 row inserted/updated/deleted
-- Because p1 is invalid, Derby tries to recompile it
-- before executing.
-- It is successful and executes.
ij> DROP TABLE mytable;
0 rows inserted/updated/deleted
-- statement p1 is now invalid,
-- and this time the attempt to recompile it
-- upon execution will fail
ij> execute p1;
ERROR 42X05: Table/View 'MYTABLE' does not exist.

ALTER TABLE statement

The ALTER TABLE statement allows you to:

· add a column to a table
· add a constraint to a table
· drop a column from a table
· drop an existing constraint from a table
· increase the width of a VARCHAR or VARCHAR FOR BIT DATA column
· override row-level locking for the table (or drop the override)
· change the increment value and start value of the identity column
· change the nullability constraint for a column
· change the default value for a column

Syntax

ALTER TABLE

table-Name

{
ADD COLUMN

column-definition

ADD

CONSTRAINT clause

DROP [ COLUMN ] column-name [ CASCADE | RESTRICT ]
DROP { PRIMARY KEY | FOREIGN KEY constraint-name | UNIQUE
constraint-name | CHECK constraint-name | CONSTRAINT constraint-name }
ALTER [ COLUMN ]

column-alteration

LOCKSIZE { ROW | TABLE }
}

column-definition

Simple-column-Name

[ DataType ]

[

Column-level-constraint

[ [ WITH ] DEFAULT DefaultConstantExpression
|

generation-clause

]

The syntax for the column-definition for a new column is a subset of the syntax for a
column in a

CREATE TABLE statement