ALTER TABLE Statement

Adds columns and constraints to, and deletes columns and constraints from an existing table.

Syntax

ALTER TABLE base-table-name base-table-alteration-action

Syntax Elements

base-table-name =
{owner-name.}base-table-identifier

owner-name =
The SQL user-id of the creator of the table.

base-table-identifier =
The name of the table to alter.

base-table-alteration-action =
column-alteration-action || table-constraint-alteration-action

column-alteration-action =
    ADD { COLUMN }
        [ column-definition || ( column-definition { , column-definition { ... }} ) ]
||  DROP { COLUMN }
        [ column-identifier || ( column-identifier { , column-identifier { ... }} ) ]
                [ RESTRICT || CASCADE ]

column-definition =
column-identifier data-type
    { column-constraint-definition { column-constraint-definition { ... }}

column-identifier
The name of the column to be altered.

data-type
Refer to supported data types section.

column-constraint-definition =
{ CONSTRAINT constraint-name }
    [ NOT NULL || PRIMARY KEY || UNIQUE || references-definition ]

constraint-name =
Any user-defined name (except a Reserved Word) starting with a letter and comprising up to 49 letters, underscores and/or numbers. Letters are case sensitive.

references-definition =
REFERENCES base-table-name ( column-identifier )

table-constraint-alteration-action =
ADD { CONSTRAINT constraint-name } table-constraint-definition

table-constraint-definition =
    [ PRIMARY KEY || UNIQUE ] (column-identifier { , column-identifier { ... }} )
||   FOREIGN KEY ( column-identifier { , column-identifier { ... }} )
        REFERENCES base-table-name ( column-identifier { , column-identifier { ... }} )

Restrictions

Only the SQL owner of the table can perform this statement. The table owner must have SQL Catalog Maintenance privilege set in the Reality security profile.

The table must have been created by the SQL CREATE TABLE statement.

The table must not contain any data.

No columns that form part of the primary key can be used in indexes.

Once defined, a primary key cannot be redefined.

In a FOREIGN KEY constraint, the number of columns in the candidate key must match the number in the foreign key.

Examples

ALTER TABLE EMP ADD NICKNAME VARCHAR(20)

ALTER TABLE EMP ADD (NICKNAME VARCHAR(20), BIRTHDATE DATE)

ALTER TABLE EMP ADD PRIMARY KEY (EMPNO, ENAME)

ALTER TABLE EMP ADD NICKNAME VARCHAR(20) NOT NULL

ALTER TABLE TEST ADD BVAR REFERENCES FK(FK1)