Try OpenEdge Now
skip to main content
SQL Development
OpenEdge SQL Data Definition Language : Using Data Definition Language statements : Working with tables : ALTER TABLE
 
ALTER TABLE
The ALTER TABLE statement lets you:
*Change the name of a table
*Change the name of a column within a table
*Add a column to a table
*Set (ABL) Advanced Business Language table, column and index attributes
*Convert a table to a multi-tenant table
*Add or drop LOB columns to or from a multi-tenant table
*Migrate data of an unpartitioned table to a partitioned table
*Add or drop partitions to or from a partitioned table
*Split a composite or regular (non-composite) partition into one or more partitions
Note: For a complete description of the ALTER TABLE syntax, see OpenEdge Data Management: SQL Reference.
Syntax
ALTER TABLE [owner_name]table_name{ADD column-definition
 |SET progress_table_attribute value |SET { ENCRYPT WITH cipher
      | DECRYPT
      | ENCRYPT REKEY ]    
  |BUFFER_POOL{PRIMARY|ALTERNATE}}
  |ALTER [ COLUMN ]column_name
{ SET DEFAULT value
                                | DROP DEFAULT
                                | SET [NOT] NULL
                                | SET progress_column_attribute value} 
                               | SET ENCRYPT WITH cipher
                                | SET DECRYPT
                                | SET ENCRYPT REKEY
                                | SET BUFFER_POOL{PRIMARY | ALTERNATE}}
|DROP COLUMN column_name
{ CASCADE | RESTRICT }
|ADD [CONSTRAINT constraint_name]
{primary_key_definition 
|foreign_key_definition 
|uniqueness_constraint
 |check_constraint}
[ AREA area_name]
|DROP CONSTRAINT constraint_name[ CASCADE | RESTRICT]
|ALTER INDEX index_name {SET progress_index_attribute value   
                      |SET ENCRYPT WITH cipher
                          |SET DECRYPT
                          |SET ENCRYPT REKEY
                          |SET BUFFER_POOL
{PRIMARY|ALTERNATE}}
|RENAME {table_name TO new_table_name
         |COLUMN column_name TO new_column_name  
        |INDEX index_name TO new_index_name }}
[ SET MULTI_TENANT
    [ FOR TENANT {[owner_name]tenant_name_1|DEFAULT}    
      [ USING TABLE AREA table_area_name]       
   [ USING INDEX AREA index_area_name]      
    [ USING LOB AREA lob_area_name]    ]...    
[ FOR TENANT {[owner_name]tenant_name_2|DEFAULT}
USING NO SPACE ]];
ALTER TABLE ...[
[PARTITION BY {RANGE|LIST}column_name[PRO_DESCRIPTION desc]
[ SUBPARTITION BY {RANGE|LIST}column_name[PRO_DESCRIPTION desc] ,...]
area_spec(partition_attribute [partition_attribute ], ...)]
USING INDEX index_name [ , index_name, ...]]
<all other ALTER TABLE options>
The addition or deletion of columns is a common modification for tables. When a column is added, the OpenEdge RDBMS places the column to the far right of the table. Unless you declare the column to be NOT NULL and assign a default value, the RDBMS will assume the column has a value of NULL for each row in the existing table.
For details on using the ALTER TABLE ADD COLUMN statement to designate objects for buffer pool assignments, including an alternate buffer pool, see OpenEdge Data Management: Database Administration.
For details on using the ALTER TABLE statement to enable transparent data encryption, see OpenEdge Getting Started: Core Business Services - Security and Auditing.
Example: ALTER TABLE statement
The following example shows how the ALTER TABLE statement is used to add a column to a table.
ALTER TABLE SPORTS.Customer
ADD Customer_phone CHAR (10);
The ALTER TABLE statement also can be used to change the name of an existing table. To do so, SQL uses the following syntax:
ALTER TABLE [owner_name.]table_name RENAME TO [owner_name.]table_name ;
Example: Using ALTER TABLE statement to rename table
The following example shows how the ALTER TABLE statement is used to rename an existing table.
ALTER TABLE SPORTS.employee RENAME TO SPORTS.staff;
Example: Altering a multi-tenant table
The following example shows how to change the pub.mtcustomer table to a multi-tenant table with default space allocations:
ALTER TABLE pub.mtcustomer SET MULTI_TENANT;
Example: Altering a multi-tenant table with overriding space allocations
The following example shows how to alter the pub.mtcustomer table to be a multi-tenant table with overriding space allocations:
ALTER TABLE pub.mtcustomer SET MULTI_TENANT
FOR TENANT "Consolidated_Freightways"
USING TABLE AREA "MMM Data area"
USING INDEX AREA "MM CF Fin idx area"
FOR TENANT Mega_Media_Networks
USING LOB AREA "MMM pix area" ;
FOR TENANT DEFAULT USING NO SPACE;
In general, the above example describes how to convert a regular table to a multi-tenant table. When a regular table is converted to a multi-tenant table, the table data is moved to the default partition.
Syntax for repairing CRC mismatch error:
The following special syntax is for Database Administrators who handle problems while migrating database tables from an older release database to a newer release database. In some cases, there may be a CRC mismatch error (when dumping and loading data using BINARY DUMP/LOAD.).
ALTER TABLE table_name SET PRO_FIELD_INFO PRO_SCHEMA_REPAIR
Example: Repairing CRC mismatch error
The following example shows how to repair a CRC mismatch error:
ALTER TABLE Customer SET PRO_FIELD_INFO PRO_SCHEMA_REPAIR;
In the above example, the Customer table belongs to an old database. When you execute the above statement, the inconsistent field information is corrected and the table is updated with the new CRC.
Example: Using ALTER TABLE statement for partitioned tables
The following example shows how to use ALTER TABLE statement for partitioned tables:
CREATE TABLE Pub.tpcustomer ( Custid int,
Custname varchar (50),
join_date date,
salary int
);
CREATE INDEX IDX1 on Pub.tpcustomer (custid);
CREATE INDEX IDX2 on Pub.tpcustomer (custid,salary);
CREATE INDEX IDX3 on Pub.tpcustomer (salary);
The above statement creates a table with two partition aligned indexes: IDX1 and IDX2 and an index IDX3 that is not partition aligned.
The below ALTER TABLE statement converts the above table to a partitioned table by adding one logical data partition and specifies IDX1 as the local composite index. IDX2 and IDX3 are marked as global indexes. If the data in Pub.tpcustomer contains custid greater than 100000, then the statement returns an error.
ALTER TABLE Pub.tpcustomer
PARTITION BY RANGE Custid
USING TABLE AREA "custtab_area"
USING INDEX AREA "custidx_area" (PARTITION p3 VALUES <= (100000))USING INDEX IDX1;
For more information, see OpenEdge Data Management: SQL Reference.