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 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:
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 OpenEdge Development: Internationalizing 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.
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.
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]
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.
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 Syntaxfor 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 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.
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;
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 2000 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;
Change the name of a table
