RENAME statement
The RENAME statement renames an existing table or index.
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 only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).
Authorization
The privileges held by the
authorization ID of the statement must include at least one of the
following authorities:
- CONTROL privilege on the table or index
- Ownership of the table or index, as recorded in the OWNER column of the SYSCAT.TABLES catalog view for a table, and the SYSCAT.INDEXES catalog view for an index
- ALTERIN privilege on the schema
- SCHEMAADM authority on the schema
- DBADM authority
Syntax
Description
- TABLE 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 must not be the name of a catalog table (SQLSTATE
42832), a materialized query table, a typed table (SQLSTATE 42997), a created temporary table, a declared global
temporary table (SQLSTATE 42995), a nickname, or an object other than
a table or an alias (SQLSTATE 42809). The TABLE keyword is optional.
The name must not identify a table that is referenced in a row permission definition or a column mask definition (SQLSTATE 42917).
- INDEX source-index-name
- Names the existing index that is to be renamed. The name, including the schema name, must identify an index that already exists in the database (SQLSTATE 42704). It must not be the name of an index on a created temporary table or a declared global temporary table (SQLSTATE 42995). The schema name must not be SYSIBM, SYSCAT, SYSFUN, or SYSSTAT (SQLSTATE 42832). target-identifier
- Specifies the new name for the table or index without a schema name. The schema name of the source object is used to qualify the new name for the object. The qualified name must not identify a table, view, alias, or index that already exists in the database (SQLSTATE 42710).
Rules
When renaming a table, the source table must not:
- Be referenced in any existing materialized query table definitions
- Be referenced in any existing statistical view definition. This includes
the system-generated statistical view that is created as part of index creation which includes an
expression-based key.
Note: With the release of Db2 11.5.7, renaming of tables with an index having an expression-based key is possible, if the expression does not contain qualified names.
- Be the subject table of an existing trigger
- Be a parent or dependent table in any referential integrity constraints
- Be the scope of any existing reference column
- Be referenced in a spatial registration with Spatial Extender. Unregister the spatial column or columns, and then reregister them after the rename is completed.
- Be referenced by an XSR object that has been enabled for decomposition
An error (SQLSTATE 42986) is returned if the source table violates one or more of these conditions.
When renaming an
index:
- The source index must not be a system-generated index for an implementation table on which a typed table is based (SQLSTATE 42858).
Notes
- CHECK constraints with three part names are not supported and will return SQL0750. Use only the column name instead.
- Catalog entries are updated to reflect the new table or index name.
- All authorizations associated with the source table or index name are transferred to the new table or index 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).
- RENAME TABLE invalidates any packages that are dependent on the source table. RENAME INDEX invalidates any packages that are dependent on the source index.
- If an alias is used for the source-table-name, it must resolve to a table name. The alias is not changed by the RENAME statement and continues to refer to the old table name. The table is renamed within its original schema
- A table with primary key or unique constraints can be renamed if none of the primary key or unique constraints are referenced by any foreign key.
Examples
- Example 1: Change the name of the EMP table to EMPLOYEE.
RENAME TABLE EMP TO EMPLOYEE RENAME TABLE ABC.EMP TO EMPLOYEE
- Example 2: Change the name of the index NEW-IND to IND.
RENAME INDEX NEW-IND TO IND RENAME INDEX ABC.NEW-IND TO IND