5.4.2. ALTER TABLE

Used for

altering the structure of a table.

Available in

DSQL, ESQL

Syntax

ALTER TABLE tablename
  <operation> [, <operation> ...]
<operation> ::=
    ADD <col_def>
  | ADD <tconstraint>
  | DROP colname
  | DROP CONSTRAINT constr_name
  | ALTER [COLUMN] colname <col_mod>
<col_def> ::= <regular_col_def> | <computed_col_def>
<regular_col_def> ::=
  colname {<datatype> | domainname}
  [DEFAULT {<literal> | NULL | <context_var>}]
  [NOT NULL]
  [<col_constraint>]
  [COLLATE collation_name]
<computed_col_def> ::=
  colname [<datatype>]
  {COMPUTED [BY] | GENERATED ALWAYS AS} (<expression>)
<col_mod> ::= <regular_col_mod> | <computed_col_mod>
<regular_col_mod> ::=
    TO newname
  | POSITION newpos
  | TYPE {<datatype> | domainname}
  | SET DEFAULT {<literal> | NULL | <context_var>}
  | DROP DEFAULT
<computed_col_mod> ::=
    TO newname
  | POSITION newpos
  | [TYPE <datatype>] {COMPUTED [BY] | GENERATED ALWAYS AS} (<expression>)
<datatype> ::=
    {SMALLINT | INTEGER | BIGINT} [<array_dim>]
  | {FLOAT | DOUBLE PRECISION} [<array_dim>]
  | {DATE | TIME | TIMESTAMP} [<array_dim>]
  | {DECIMAL | NUMERIC} [(precision [, scale])] [<array_dim>]
  | {CHAR | CHARACTER} [VARYING] | VARCHAR} [(size)]
    [<array_dim>] [CHARACTER SET charset_name]
  | {NCHAR | NATIONAL {CHARACTER | CHAR}} [VARYING]
    [(size)] [<array_dim>]
  | BLOB [SUB_TYPE {subtype_num | subtype_name}]
    [SEGMENT SIZE seglen] [CHARACTER SET charset_name]
  | BLOB [(seglen [, subtype_num])]
<array_dim> ::= '[' [m:]n [,[m:]n ...] ']'
<col_constraint> ::=
  [CONSTRAINT constr_name]
    { PRIMARY KEY [<using_index>]
    | UNIQUE      [<using_index>]
    | REFERENCES other_table [(colname)] [<using_index>]
        [ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]
        [ON UPDATE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]
    | CHECK (<check_condition>) }
<tconstraint> ::=
  [CONSTRAINT constr_name]
    { PRIMARY KEY (<col_list>) [<using_index>]
    | UNIQUE      (<col_list>) [<using_index>]
    | FOREIGN KEY (<col_list>)
        REFERENCES other_table [(<col_list>)] [<using_index>]
        [ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]
        [ON UPDATE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]
    | CHECK (<check_condition>) }
<col_list> ::= colname [, colname ...]
<using_index> ::= USING
  [ASC[ENDING] | DESC[ENDING]] INDEX indexname
<check_condition> ::=
    <val> <operator> <val>
  | <val> [NOT] BETWEEN <val> AND <val>
  | <val> [NOT] IN (<val> [, <val> ...] | <select_list>)
  | <val> IS [NOT] NULL
  | <val> IS [NOT] DISTINCT FROM <val>
  | <val> [NOT] CONTAINING <val>
  | <val> [NOT] STARTING [WITH] <val>
  | <val> [NOT] LIKE <val> [ESCAPE <val>]
  | <val> [NOT] SIMILAR TO <val> [ESCAPE <val>]
  | <val> <operator> {ALL | SOME | ANY} (<select_list>)
  | [NOT] EXISTS (<select_expr>)
  | [NOT] SINGULAR (<select_expr>)
  | (<search_condition>)
  | NOT <search_condition>
  | <search_condition> OR <search_condition>
  | <search_condition> AND <search_condition>
<operator> ::=
    <> | != | ^= | ~= | = | < | > | <= | >=
  | !< | ^< | ~< | !> | ^> | ~>
<val> ::=
    colname ['['array_idx [, array_idx ...]']']
  | <literal>
  | <context_var>
  | <expression>
  | NULL
  | NEXT VALUE FOR genname
  | GEN_ID(genname, <val>)
  | CAST(<val> AS <datatype>)
  | (<select_one>)
  | func([<val> [, <val> ...]])

The ALTER TABLE statement changes the structure of an existing table. With one ALTER TABLE statement it is possible to perform multiple operations, adding/dropping columns and constraints and also altering column specifications.

Multiple operations in an ALTER TABLE statement are separated with commas.

Version Count Increments

Some changes in the structure of a table increment the metadata change counter (“version count”) assigned to every table. The number of metadata changes is limited to 255 for each table. Once the counter reaches the 255 limit, you will not be able to make any further changes to the structure of the table without resetting the counter.

To reset the metadata change counter

You should back up and restore the database using the gbak utility.

The ADD Clause

With the ADD clause you can add a new column or a new table constraint. The syntax for defining the column and the syntax of defining the table constraint correspond with those described for CREATE TABLE statement.

Effect on Version Count

• Each time a new column is added, the metadata change counter is increased by one

• Adding a new table constraint does not increase the metadata change counter

The DROP Clause

The DROP *<column name>* clause deletes the specified column from the table. An attempt to drop a column will fail if anything references it. Consider the following items as sources of potential dependencies:

• column or table constraints

• indexes

• stored procedures and triggers

• views

Effect on Version Count

• Each time a column is dropped, the table’s metadata change counter is increased by one.

The DROP CONSTRAINT Clause

The DROP CONSTRAINT clause deletes the specified column-level or table-level constraint.

A PRIMARY KEY or UNIQUE key constraint cannot be deleted if it is referenced by a FOREIGN KEY constraint in another table. It will be necessary to drop that FOREIGN KEY constraint before attempting to drop the PRIMARY KEY or UNIQUE key constraint it references.

Effect on Version Count

• Deleting a column constraint or a table constraint does not increase the metadata change counter.

The ALTER [COLUMN] Clause

With the ALTER [COLUMN] clause, attributes of existing columns can be modified without the need to drop and re-add the column. Permitted modifications are:

• change the name (does not affect the metadata change counter)

• change the data type (increases the metadata change counter by one)

• change the column position in the column list of the table (does not affect the metadata change counter)

• delete the default column value (does not affect the metadata change counter)

• set a default column value or change the existing default (does not affect the metadata change counter)

• change the type and expression for a computed column (does not affect the metadata change counter)

Renaming a Column: the TO Keyword

The TO keyword with a new identifier renames an existing column. The table must not have an existing column that has the same identifier.

It will not be possible to change the name of a column that is included in any constraint: PRIMARY KEY, UNIQUE key, FOREIGN KEY, column constraint or the CHECK constraint of the table.

Renaming a column will also be disallowed if the column is used in any trigger, stored procedure or view.

Changing the Data Type of a Column: the TYPE Keyword

The keyword TYPE changes the data type of an existing column to another, allowable type. A type change that might result in data loss will be disallowed. As an example, the number of characters in the new type for a CHAR or VARCHAR column cannot be smaller than the existing specification for it.

If the column was declared as an array, no change to its type or its number of dimensions is permitted.

The data type of a column that is involved in a foreign key, primary key or unique constraint cannot be changed at all.

Changing the Position of a Column: the POSITION Keyword

The POSITION keyword changes the position of an existing column in the notional “left-to-right” layout of the record.

Numbering of column positions starts at 1.

• If a position less than 1 is specified, an error message will be returned

• If a position number is greater than the number of columns in the table, its new position will be adjusted silently to match the number of columns.

The DROP DEFAULT and SET DEFAULT Clauses

The optional DROP DEFAULT clause deletes the default value for the column if it was put there previously by a CREATE TABLE or ALTER TABLE statement.

• If the column is based on a domain with a default value, the default value will revert to the domain default

• An execution error will be raised if an attempt is made to delete the default value of a column which has no default value or whose default value is domain-based

The optional SET DEFAULT clause sets a default value for the column. If the column already has a default value, it will be replaced with the new one. The default value applied to a column always overrides one inherited from a domain.

The COMPUTED [BY] or GENERATED ALWAYS AS Clauses

The data type and expression underlying a computed column can be modified using a COMPUTED [BY] or GENERATED ALWAYS AS clause in the ALTER TABLE ALTER [COLUMN] statement. Converting a regular column to a computed one and vice versa are not permitted.

Attributes that Cannot Be Altered

The following alterations are not supported:

• Enabling or disabling the NOT NULL constraint on a column

• Changing the default collation for a character type column

Only the table owner and administrators have the authority to use ALTER TABLE.

Examples Using ALTER TABLE

1. Adding the CAPITAL column to the COUNTRY table.

   ALTER TABLE COUNTRY
     ADD CAPITAL VARCHAR(25);

2. Adding the CAPITAL column with the UNIQUE constraint and deleting the CURRENCY column.

   ALTER TABLE COUNTRY
     ADD CAPITAL VARCHAR(25) NOT NULL UNIQUE,
     DROP CURRENCY;

3. Adding the CHK_SALARY check constraint and a foreign key to the JOB table.

   ALTER TABLE JOB
     ADD CONSTRAINT CHK_SALARY CHECK (MIN_SALARY < MAX_SALARY),
     ADD FOREIGN KEY (JOB_COUNTRY) REFERENCES COUNTRY (COUNTRY);

4. Setting default value for the MODEL field, changing the type of the ITEMID column and renaming the MODELNAME column.

   ALTER TABLE STOCK
     ALTER COLUMN MODEL SET DEFAULT 1,
     ALTER COLUMN ITEMID TYPE BIGINT,
     ALTER COLUMN MODELNAME TO NAME;

5. Changing the computed columns NEW_SALARY and SALARY_CHANGE.

   ALTER TABLE SALARY_HISTORY
     ALTER NEW_SALARY GENERATED ALWAYS AS
       (OLD_SALARY + OLD_SALARY * PERCENT_CHANGE / 100),
     ALTER SALARY_CHANGE COMPUTED BY
       (OLD_SALARY * PERCENT_CHANGE / 100);

See also

CREATE TABLE, DROP TABLE, CREATE DOMAIN