Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

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_definition

| ADD (new_column_definition [, new_column_definition] ...)

| MODIFY (column_name [ new_column_type ]

[ default_value ] [ NULL | NOT NULL ]

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

| ADD [ CONSTRAINT new_constraint_name ] table_constraint

| RENAME CONSTRAINT constraint_name TO new_constraint_name

| RENAME COLUMN column_name TO new_column_name

| RENAME TO new_table_name

};

new_column_definition ::

new_column_name column_type

[ [ NULL | NOT NULL ]

[ default_value

| IDENTITY [(seed, increment)] ] ]

default_value::

DEFAULT { literal | USER | NULL | UID | SYSDATE | SYSTIME | SYSTIMESTAMP }

table_constraint ::

PRIMARY KEY ( column [, ... ] )

| UNIQUE ( column [, ... ] )

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

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

| CHECK ( search_condition )

[ REORG ]

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, FairCom DB 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 or rename a column, the following database objects dependent on that column are also automatically dropped or updated:
    • 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, columns referenced in check constraints, views, procedures, triggers, and UDFs cannot be modified from an ALTER TABLE event and need to be simultaneously addressed by a database administrator.

  • 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
  • When new columns are added IDENTITY can be specified for one of the columns only if the table does not already contain an identity column. A DEFAULT value cannot be specified for and IDENTITY column.
  • Existing rows are not updated to the default value, even if the data was initially missing. Only new inserted rows as assigned the new default value.
  • (FairCom DB SQL V10.3 and later) Any table altered in the structure using ALTER TABLE or truncated using TRUNCATE will have the $DELFLD$ set to 4 bytes despite the size in the original table.
  • RENAME CONSTRAINT is available in V11 and later.
  • REORG is available in V11.5 and later. See below.

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

REORG STORAGE_ATTRIBUTES

In V11.5 and later, the REORG option forces table record and index reorganization. It can change the definitions by applying the storage attributes specified.

ALTER TABLE [tbl] REORG STORAGE_ATTRIBUTES '[attribs]'

The specified storage attributes affect the organization of the table and the use of Hot Alter Table (see below). The following storage attributes are valid (anything else is ignored):

  • hotalter
  • nohotalter
  • huge
  • nohuge
  • rowid_fld
  • norowid_fld
  • recbyt_idx
  • norecbyt_idx

The REORG option is helpful in the rare cases when the index becomes out-of-sync. It is recommended when the Hot Alter Table feature (the default for SQL tables) has been used on a table many times, perform record reorganization to bring all records to the current status.

Hot Alter Table

FairCom's Hot Alter Table feature can dramatically increase efficiency and minimize downtime when rolling out changes to your application data schema. Hot Alter Table allows you to provide "rolling upgrades" to your product deployed in the field. Schema changes no longer cause your customer to go through a lengthy file migration process, which previously could require many hours of downtime. Instead, Hot Alter Table upgrades records upon read or write.

The SQL API supports the Hot Alter Table feature:

  1. CREATE TABLE uses storage attributes to explicitly determine if Hot Alter Table is supported or not. If not explicitly set, the default is to disable it. The syntax is:

    create table.... storage_attributes 'nohotalter' to not allow hot alter
    create table.... storage_attributes 'hotalter' to allow hot alter

  2. A SQL_OPTION has been added to ctsrvr.cfg to change the CREATE TABLE default to enable 'hotalter' (if not specifically disabled using the STORAGE_ATTRIBUTE 'nohotalter') on table creation:

    SQL_OPTION HOT_ALTER_TABLE

    Calls to Alter Table will automatically always attempt to use Hot Alter Table for tables created with the support turned on.

  3. After a table is created that supports Hot Alter Table, a SQL Alter Table will perform Hot Alter Table if possible, and if not possible it will perform regular Alter Table.

    For example, adding a field to a table does use Hot Alter Table:

    ALTER TABLE mytable ADD (newfield VARCHAR(100));

  4. The following ALTER TABLE syntax has been added:

    ALTER TABLE [tbl] REORG - Forces table record and index reorganization with no definition changes.

TOCIndex