Progress
SQL-89
Guide and Reference


Altering Tables

The ALTER TABLE statement lets you add new columns to a table, delete columns from a table, or change the format and labels associated with an existing column. The ALTER TABLE statement has the following syntax.

SYNTAX
ALTER TABLE table-name
  {
     { ADD COLUMN column-name data-type
             [ FORMAT string ]
             [ LABEL string ]
             [ COLUMN-LABEL string ]
             [ DEFAULT initial-value ]
             [ [ NOT ] CASE-SENSITIVE ]    }
   |  { DROP COLUMN column-name }
   |  { ALTER COLUMN column-name 
             [ FORMAT string ]
             [ LABEL string ]
             [ COLUMN-LABEL string ]
             [ DEFAULT initial-value ]
             [ [ NOT ] CASE-SENSITIVE ]    }
  } 

You can enter the CASE-SENSITIVE, FORMAT, LABEL, DEFAULT, and COLUMN-LABEL keywords in any order. You cannot alter tables in non-Progress databases.

The following example adds a new column to a table named cust_table.

ALTER TABLE Cust_Table 
  ADD COLUMN Sales_Rep CHARACTER (3)
    LABEL ’Sales Rep’. 

When you add a new column to an existing table, Progress inserts null or default values in all existing rows for the new column. Therefore, you cannot use the NOT NULL clause in the ALTER TABLE statement unless you specify a default. You can, however, replace the column’s null values by using an UPDATE statement following the ALTER TABLE statement. In addition, you cannot use the UNIQUE qualifier if the table already contains data rows, because all rows receive the same value for the new column.

You can also specify display formats and labels for column data by using the Progress syntax for display formats and labels. For information on the FORMAT, LABEL, and COLUMN-LABEL format phrases, see the Format Phrase reference entry in the Progress Language Reference.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095