To rename a table, you must be the owner of the table, or have the
ALTER privilege on the table, or have the DBA privilege on the database.
An error occurs if old_table is a synonym, rather
than the name of a table.
The renamed table remains in the current database. You cannot use the RENAME
TABLE statement to move a table from the current database to another database,
nor to rename a table that resides in another database.
In Dynamic Server, you cannot change the table owner by renaming the table. An error occurs if you try to specify an owner. qualifier
for the new name of the table.
In Extended Parallel Server, a user with DBA privilege on the database
can change the owner of a table, if the table is local. Both the table name
and owner can be changed by a single statement. The following example uses
the RENAME TABLE statement to change the owner of a table:
RENAME TABLE tro.customer TO mike.customer
When the table owner is changed, you must specify both the old owner and
new owner.
Important:
When the owner of a table
is changed, the existing privileges granted by the original owner are retained.
In Extended Parallel Server, you cannot rename a table
that contains a dependent GK index.
In an ANSI-compliant database, if you are not the owner of old_table, you must specify owner.old_table as the old name of the table.
If old_table is referenced by a view in the current database,
the view definition is updated in the sysviews system
catalog table to reflect the new table name. For further information on the sysviews system catalog table, see the IBM Informix Guide to SQL: Reference.
If old_table is a triggering table, the database
server takes these actions:
- Replaces the name of the table in the trigger definition but does not replace the table name where it appears inside any
triggered actions
- Returns an error if the new table name is the same as a correlation name
in the REFERENCING clause of the trigger definition
When the trigger executes, the database server returns an error if it encounters
a table name for which no table exists.
You can reorganize the items table to move the quantity column from the fifth position to the third position
by following these steps:
- Create a new table, new_table, that contains the
column quantity in the third position.
- Fill the table with data from the current items table.
- Drop the old items table.
- Rename new_table with the name items.
The following example uses the RENAME TABLE statement as the last step:
CREATE TABLE new_table
(
item_num SMALLINT,
order_num INTEGER,
quantity SMALLINT,
stock_num SMALLINT,
manu_code CHAR(3),
total_price MONEY(8)
);
INSERT INTO new_table
SELECT item_num, order_num, quantity, stock_num,
manu_code, total_price FROM items;
DROP TABLE items;
RENAME TABLE new_table TO items;
>>-RENAME TABLE--+--------+--old_table--TO----------------------> '-owner.-' >--+-------------------+--new_table---------------------------->< | (1) | '--------new_owner.-'