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)
- 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
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 ] 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 |
column-name RESTART WITH integer-constant |
column-name [ NOT ] NULL |
column-name [ WITH ] DEFAULT default-value
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.
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.
- 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 or UNIQUE constraint.
- Changing the default value for a column.
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;
-- Remove the NOT NULL constraint from the MANAGER column
ALTER TABLE Employees ALTER COLUMN Manager NULL;
-- Add the NOT NULL constraint to the SSN column
ALTER TABLE Employees ALTER COLUMN ssn NOT NULL;
-- Change the default value for the SALARY column
ALTER TABLE Employees ALTER COLUMN Salary DEFAULT 1000.0
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.