The ALTER TABLE statement allows you to:
- add a column to a table
- add a constraint to a table
- drop an existing constraint from a table
- increase the width of a VARCHAR, CHAR VARYING, and CHARACTER VARYING column
- override row-level locking for the table (or drop the override)
SyntaxALTER TABLE table-Name
{
ADD COLUMN column-definition |
ADD CONSTRAINT clause |
DROP { PRIMARY KEY | FOREIGN KEY constraint-name | UNIQUE
constraint-name | CHECK constraint-name | CONSTRAINT constraint-name }
ALTER column-alteration |
LOCKSIZE { ROW | TABLE }
}
column-definitionSimple-column-Name DataType
[ Column-level-constraint ]*
[ [ WITH ] DEFAULT {ConstantExpression | NULL } ]
column-alterationcolumn-Name SET DATA TYPE VARCHAR(integer) |
column-name SET INCREMENT BY integer-constant
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.
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.
Adding columnsThe syntax for the column-definition for a new column is the same as for a column in a CREATE TABLE statement.
This means that a column constraint can be placed on the new column within
the ALTER TABLE ADD COLUMN statement. However, a column with a NOT NULL constraint
can be added to an existing table if you give
a default value; otherwise, an exception is thrown when the ALTER TABLE statement
is executed.
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).
If
a table has an UPDATE trigger without an explicit column list, adding a column
to that table in effect adds that column to the implicit update column list
upon which the trigger is defined, and all references to transition variables
are invalidated so that they pick up the new column.
Adding constraintsALTER 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:
- When adding a foreign key or check constraint to an existing table, checks the table to make
sure existing rows satisfy the constraint. If any row is invalid, throws
a statement exception and the constraint is not added.
- All columns included in a primary key must contain non null data and be
unique.
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 .
Use the syntax for table-level constraint when adding a constraint with the
ADD TABLE ADD CONSTRAINT syntax.
Dropping constraintsALTER 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 columnsThe column-alteration allows
you to alter the named column in the following ways:
- Increasing the length of an existing VARCHAR 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.
Setting defaultsYou 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 .
Changing the lock granularity for the tableThe
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 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 .
Examples-- Add a new column with a column-level constraint
-- to an existing table
-- An exception will be thrown if the table
-- contains any rows
-- since the newcol will be initialized to NULL
-- in all existing rows in the table
ALTER TABLE CITIES ADD COLUMN REGION VARCHAR(26)
CONSTRAINT NEW_CONSTRAINT CHECK (REGION IS NOT NULL);
-- Add a new unique constraint to an existing table
-- An exception will be thrown if duplicate keys are found
ALTER TABLE SAMP.DEPARTMENT
ADD CONSTRAINT NEW_UNIQUE UNIQUE (DEPTNO);
-- add a new foreign key constraint to the
-- Cities table. Each row in Cities is checked
-- to make sure it satisfied the constraints.
-- if any rows don't satisfy the constraint, the
-- constraint is not added
ALTER TABLE CITIES ADD CONSTRAINT COUNTRY_FK
Foreign Key (COUNTRY) REFERENCES COUNTRIES (COUNTRY);
-- Add a primary key constraint to a table
-- First, create a new table
CREATE TABLE ACTIVITIES (CITY_ID INT NOT NULL,
SEASON CHAR(2), ACTIVITY VARCHAR(32) NOT NULL);
-- You will not be able to add this constraint if the
-- columns you are including in the primary key have
-- null data or duplicate values.
ALTER TABLE Activities ADD PRIMARY KEY (city_id, activity);
-- Drop a primary key constraint from the CITIES table
ALTER TABLE Cities DROP CONSTRAINT Cities_PK;
-- Drop a foreign key constraint from the CITIES table
ALTER TABLE Cities DROP CONSTRAINT COUNTRIES_FK;
-- add a DEPTNO column with a default value of 1
ALTER TABLE SAMP.EMP_ACT ADD COLUMN DEPTNO INT DEFAULT 1;
-- increase the width of a VARCHAR column
ALTER TABLE SAMP.EMP_PHOTO ALTER PHOTO_FORMAT SET DATA TYPE VARCHAR(30);
-- change the lock granularity of a table
ALTER TABLE SAMP.SALES LOCKSIZE TABLE;
ResultsAn 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.