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 ] [DEFAULT default_value]

     | (new_column_name column_type [ NULL | NOT NULL ][DEFAULT default_value], … )

     | MODIFY (column_name [ new_column_type ] [NULL | NOT NULL][DEFAULT  default_value] , … )

     | 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, c-treeACE 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
• 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.

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