SQL Reference

RENAME TABLE

The RENAME TABLE statement renames an existing table.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include either SYSADM or DBADM authority or CONTROL privilege.

Syntax

           .-TABLE-.
>>-RENAME--+-------+--source-table-name--TO--target-identifier--><
 

Description

source-table-name

Names the existing table that is to be renamed. The name, including the schema name, must identify a table that already exists in the database (SQLSTATE 42704). It can be an alias identifying the table. It must not be the name of a catalog table (SQLSTATE 42832), a summary table, a typed table (SQLSTATE 42997), a nickname, or an object of other than table or alias (SQLSTATE 42809).

target-identifier

Specifies the new name for the table without a schema name. The schema name of the source-table-name is used to qualify the new name for the table. The qualified name must not identify a table, view, or alias that already exists in the database (SQLSTATE 42710).

Rules

The source table must not:

• Be referenced in any existing view definitions or summary table definitions
• Be referenced in any triggered SQL statements in existing triggers or be the subject table of an existing trigger
• Be referenced in an SQL function
• Have any check constraints
• Have any generated columns other than the identity column
• Be a parent or dependent table in any referential integrity constraints
• Be the scope of any existing reference column.

An error (SQLSTATE 42986) is returned if the source table violates one or more of these conditions.

Notes

• Catalog entries are updated to reflect the new table name.
• All authorizations associated with the source table name are transferred to the new table name (the authorization catalog tables are updated appropriately).
• Indexes defined over the source table are transferred to the new table (the index catalog tables are updated appropriately).
• Any packages that are dependent on the source table are invalidated.
• If an alias is used for the source-table-name, it must resolve to a table name. The table is renamed within the schema of this table. The alias is not changed by the RENAME statement and continues to refer to the old table name.
• A table with primary key or unique constraints may be renamed if none of the primary key or unique constraints are referenced by any foreign key.

Example

Change the name of the EMP table to EMPLOYEE.

  RENAME TABLE EMP TO EMPLOYEE