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

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.

To enable transparent data encryption using the ALTER TABLE statement, see Learn about Security and Auditing.

Altering column width and minimum column size

OE SQL command behavior when columns are of array type ( either array or vararray):

Example

Consider the following tables.

create table t1( arr varchar(10) array[3]);

Here, the extent is 3. So, min_sql_width = (3 *2 ) -1 = 5 .

So, the ALTER command alter table t1 alter column arr set pro_sql_width 3 will fail as the new value 3 is less than the minimum required value. It will fail with the error code 210157 and message Invalid value for attribute "PRO_SQL_WIDTH" specified for column arr. (19164) .

Also, when a user provides the new_width in the alter command, the OE SQL engine rounds up the new width to the nearest number divisible by extent.

If new_width is 3, it will remain as 3.

If new_width is 4 , it will be rounded up to 6.

So, the ALTER command alter table t1 alter column arr set pro_sql_width 4 will pass, even though the new value is less than minimum required value, because it is rounded up to 6.

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 SQL Reference.