2.3 ALTER TABLE

---

Description

Alters the schema of an existing table by adding new columns or modifying existing columns of the specified table.

Syntax

ALTER TABLE [ owner_name. ] table_name

{ ADD [ new_column_name column_type [ NULL | NOT NULL ]

| (new_column_name column_type [ NULL | NOT NULL ], ...) ]

| MODIFY (column_name [ new_column_type ] [NULL | NOT NULL], ...)

| DROP [ COLUMN ] { column_name | ( column_name , ...) }

| ADD [ CONSTRAINT new_constraint_name ] table_constraint

| DROP CONSTRAINT constraint_name

} ;

table_constraint ::

PRIMARY KEY ( column [, ... ] )

| UNIQUE ( column [, ... ] )

| FOREIGN KEY ( column [, ... ] )

REFERENCES [ owner_name. ] table_name [ ( column [, ... ] ) ]

| CHECK ( search_condition )

Notes

Column additions and modifications have the following restrictions:
A NOT NULL column can be added to a table only if the table does not contain any rows.
The type can be modified or the length of the column can be decreased only if all the rows contain null values for the column being modified.
An existing column can be made NOT NULL only if none of the rows contain a null value for that column.
When a new column is added to an existing table, it is added after the existing columns for the table.
Views that automatically refer to all the columns of a table (such as SELECT * FROM ...) need to be dropped and added to select any columns that have been added to the table after the view has been created.
If you add a FOREIGN KEY constraint to an existing table, the table and column specified in the REFERENCES clause must exist before the constraint will work as expected. However, SQL does not check for the existence of the table and column specified in the REFERENCES clause. It is up to you to make sure they exist.
When you drop a column, the following database objects dependent on that column are also automatically dropped:
Indexes that specify the column as one of the components
Update triggers that specify the column
Unique, foreign key, and check constraints that refer to the column
UPDATE and REFERENCES privileges that refer to the column

However, views that include the column are not automatically dropped.

ALTER TABLE DROP COLUMN statements cannot:
Specify a column created as a primary key, if columns in other tables refer to the column in a referential constraint
Name all the columns in a table

Examples

To add columns to the tmp table:

ISQL> ALTER TABLE tmp

ADD mname CHAR(8) ;

ISQL> ALTER TABLE tmp

ADD (mname CHAR(8), nname CHAR(8)) ;

To add table-level constraints, consider the table tmp consisting of integer fields, fld, fld1, fld2, and fld3.

To create a unique key on fld:

ISQL> ALTER TABLE tmp ADD UNIQUE(fld) ;

To create a primary key on fld1:

ISQL> ALTER TABLE tmp ADD primary key(fld1) ;

To create a foreign key constraint named fri_cns on fld2:

ISQL> ALTER TABLE tmp ADD constraint fri_cns foreign key(fld2) ;

To create a check constraint, chk_cns on fld3:

ISQL> ALTER TABLE tmp ADD constraint chk_cns (fld2 > 10) ;

Authorization

The user executing this statement must have any of the following privileges:

DBA privilege.
Ownership of the table.
ALTER privilege on the table.

SQL Compliance	SQL-92, ODBC Core SQL grammar
Environment	Embedded SQL, interactive SQL, ODBC applications
Related Statements	CREATE TABLE, DROP TABLE