The ALTER TABLE statement can be used to:

  • Change the name of a table
  • Change the name of a column within a table
  • Add a column to a table
  • Change the width of a column
  • 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
  • Mark partitions as read-only or read-write partitions
  • Mark a table as a partitioned table without partition schema definition and then, define partition policies for it

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 PRO_SQL_WIDTH new_column_width 
       | 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_definition ] 
    USING INDEX index_name [ , index_name, ...]]
    <all other ALTER TABLE options>;

ALTER TABLE table_name 
   SET READ_ONLY | READ_WRITE FOR PARTITION
       partition_name_list

Parameters

owner_name
Specifies the name of the schema that qualifies the table.
table_name
Specifies the name of the table.
column_definition
Defines a column. The syntax for column_definition is given below:
column_namedata_type[ COLLATE case_insensitive|case_sensitive]
 [ DEFAULT { literal | NULL | SYSDATE | SYSTIME | SYSTIMESTAMP }]
 [column_constraint[column_constraint , ... ]]
 [progress_column_attribute_keyword value], ...]
column_namedata_type

Names a column and associates a data type to it. The name of the column must be different from other column names already defined in the table. The data_type must be supported by OpenEdge.

When a table contains more than one column, a comma separator must be used after each column_definition, except for the final column_definition.

COLLATE
Indicates the case sensitivity of the column. The default value for the COLLATE element is case_sensitive. A collation name can be upto 32 characters long and can contain special characters like @ or -. If the collation name contains special characters, the name should be entered in quotation marks similar to a SQL delimited identifier. For example sample-one@123. For more information on collations, refer to the Collations and collation tables section in Chapter 3 of Internationalize ABL Applications.
case_insensitive
Indicates that the column is case_insensitive. The word case_insensitive itself cannot be used as a valid input. The value for the case_insensitive clause can only be _I, I, or the default database collation with the suffix _I (For example: COLLATE_I, COLLATE I, or COLLATE BASIC_I).
case_sensitive
Indicates that the column is case_sensitive. The word case_sensitive itself cannot be used as a valid input. The value for the case_sensitive clause can only be _S, S, or the default database collation with the suffix _S (For example: COLLATE_S, COLLATE S, or COLLATE BASIC_S).
DEFAULT

Specifies an explicit default value for a column. The column takes on the default value if an INSERT statement does not include a value for the column. If a column definition omits the DEFAULT clause, NULL value gets assigned.

The DEFAULT clause accepts the arguments shown in the table below:

Argument Description
literal An integer, numeric, or a string constant.
NULL A null value.
SYSDATE Displays the current date. Valid only for columns defined with the DATE data type. SYSDATE is equivalent to the Progress default keyword TODAY. The argument represents a DATE value.
SYSTIME Displays the current time. The argument represents a TIME value.
SYSTIMESTAMP Displays the current date and time. The argument represents a TIMESTAMP value.
column_constraint

Specifies a constraint that is applied when you insert or update a value in the associated column.

progress_column_attribute_keyword value

ABL column attribute keyword and value.

The syntax used to define a LOB column is shown below:

{ LVARCHAR | CLOB | LVARBINARY | BLOB }
  [( length )] 
  [ AREA area_name] 
  [ ENCRYPT WITH cipher]
  [BUFFER_POOL{PRIMARY|ALTERNATE}]
progress_table_attribute_keyword value

ABL table attribute keyword and value.

PRO_SQL_WIDTH new_column_width
Specifies a width for the column of a table.
ENCRYPT WITH cipher

Allows the table to be encrypted by designating an appropriate cipher.

BUFFER_POOL {PRIMARY|ALTERNATE}

Allows the table to be assigned to a primary or alternate buffer pool.

MULTI_TENANT

Defines the table as a multi-tenant table. The MULTI_TENANT phrase can occur only after the column_definition and the progress_table_attribute_keyword.

FOR TENANT tenant_name

Specifies the name of a tenant. The FOR TENANT phrase can be repeated for as many existing tenants as required.

TABLE AREA area_name

Is used to over-ride the default storage areas associated with the particular tenant. This area is used for database space allocation during the ALTER TABLE statement execution. Specifying the TABLE AREA is optional.

Specifies the name of the default storage area allocated for the tenant. You can configure the default storage area using following command: 
proutil db-name -C setobjectdefaultarea area-name [ object-type | all ]
INDEX AREA index _area_name

Is used to over-ride the default storage areas associated with the particular tenant. This area is used for database space allocation during the ALTER TABLEstatement execution. Specifying the INDEX AREA is optional.

If the storage area for an index is omitted in the syntax, then any index will be allocated in the table area.

To define INDEX AREA, you must define the TABLE AREA.

LOB AREA lob _area_name

Is used to over-ride the default storage areas associated with the particular tenant. This area is used for database space allocation during the ALTER TABLE statement execution. Specifying the LOB AREA is optional.

If the storage area for a LOB is omitted in the syntax, then any LOB will be allocated in the table area.

To define the LOB AREA, you must define the TABLE AREA.

USING NO SPACE

Indicates that no space is allocated for index data storage in the table. This phrase is valid only if the tenant partition for the table is allocated NO SPACE.

Note: For a particular tenant, the table and the Index partitions must have the same allocation state.
partition_definition

Uses the following syntax:

[PARTITION BY {RANGE|LIST}column_name
 [PRO_DESCRIPTION desc]
 [subpartition_definition,...]area_spec( partition_attribute [partition_attribute ], ...)

The partition_definition must contain values for all partition keys. If the partition keys are x, y, and z, then all the partition definitions must specify values for columns x, y, and z.

subpartition_definition

Uses the following syntax:

SUBPARTITION BY {RANGE|LIST} column_name [PRO_DESCRIPTION desc]
column_name

Uses the following syntax:

simple_column_name | (simple_column_name )
partition_attribute

Uses the following syntax:

PARTITION [partition_name ] VALUES { <= | IN } (column_values) 
 [area_spec ] [ PRO_DESCRIPTION desc ]
partition_name

Specifies the SQL identifier.

column_values

Uses the following syntax:

column_value [ , column_value, ...]
column_value

Uses a constant as its value. Each column_value corresponds to a column name of a partition or a subpartition, in the same order in which the column names are defined.

area spec

Specifies the default areas for all partitions defined, if not overridden by specific partition definitions. The syntax for area spec is:

( ( [ USING TABLE AREA area name] [ USING INDEX AREA area name] 
    [ USING LOB AREA area name] ) | [ USING NO SPACE ] )
index_name

Specifies the SQL identifier. The index must be a partition aligned index.

Notes

  • See Syntax for ABL attributes for a list of ABL table, column and index attributes.

  • OE SQL command behavior for Alter table <tbl-name> alter column <col-name> set pro_sql_width <new_width>

    OE SQL will return the error Invalid value for attribute "PRO_SQL_WIDTH" specified for column arr. (19164) for alter table <tblname> alter <colname> set pro_sql_width where, columns are of array type ( either array or vararray) and the new width is less than the minimum required value.

    This behavior is applicable only for columns of array type ( either array or vararray) where, the new width should be more than the minimum defined value. The minimum value can be calculated as min_sql_width = (extent * 2 ) -1 , where, the extent = number of elements in array.

    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.

  • The ALTER TABLE ALTER INDEX statement can use two index attributes, PRO_DESCRIPTION and PRO_ACTIVE. The PRO_DESCRIPTION attribute enables the index definition to accept free-form text in the same manner as ABL. The PRO_ACTIVE attribute takes only n as an argument, thereby changing the status of an index from active to inactive. Changing an status of an index to inactive is an action that must be performed offline. For a description of the PRO_DESCRIPTION and PRO_ACTIVE attributes, see Syntax for ABL attributes.
  • Table columns defined by OpenEdge SQL have default format values identical to those created by the Data Dictionary.
  • For details on using the ALTER TABLE ADD COLUMN statement to designate objects for buffer pool assignments, including an alternate buffer pool, see Manage the OpenEdge Database.
  • To enable transparent data encryption using the ALTER TABLE statement, see Learn about Security and Auditing.

Examples

Changing the name of a table

In the following example, the ALTER TABLE statement is used to change the name of a table from customer to Customers:

ALTER TABLE customer RENAME TO Customers;

Changing the name of a column within a table

In this example, the ALTER TABLE statement is used to change the name of a column within a table and the column named Address changes to Street:

ALTER TABLE customer RENAME Address TO Street;

Adding a column

In this example, table customer adds the column Region:

ALTER TABLE customer ADD COLUMN Region char(20);

Modifying a column

In this example, table customer changes an existing 32-bit INTEGER column into a 64-bit BIGINT column:

ALTER TABLE OrderLine ALTER COLUMN Qty SET PRO_DATA_TYPE BIGINT;

Once the above statement is executed, the column will appear as a BIGINT column both internally and to applications.

The above statement executed against a column that is not a 32-bit integer results in a syntax error.

Adding an ABL description to a table and change the ABL default data access index of the table

In this example, ALTER TABLE adds an ABL description to a table and changes the ABL default data access index of the table:

ALTER TABLE pub.customer SET PRO_DESCRIPTION 'Sports 2020 Customers';
ALTER TABLE pub.customer SET PRO_DEFAULT_INDEX CustNumIdx;

Renaming an index

In this example, ALTER TABLE RENAME INDEX is used to change an index named CustNum to CustomerNumberIndex:

ALTER TABLE Customers RENAME INDEX CustNum to CustomerNumberIndex;

The ALTER TABLE statement enables you to change the names of tables or columns or to add columns while your database is online servicing other requests. Other changes performed by ALTER TABLE must occur offline.

Marking existing partitions as read-only partitions 

ALTER TABLE Pub.tpcustomer SET READ_ONLY FOR PARTITION 
NoholdDKP15500, NoholdBBB15500, HoldSRS10000;

Marking existing partitions as read-write partitions 

ALTER TABLE Pub.tpcustomer SET READ_WRITE FOR PARTITION 
NoholdDKP15500, NoholdBBB15500, HoldSRS10000;