Informix Guide to SQL: Syntax SQL Statements

Use the RENAME TABLE statement to change the name of a table.

You can rename a table if any of the following statements are true:

• You own the table.
• You have the DBA privilege on the database.
• You have the Alter privilege on the table.

You cannot change the table owner by renaming the table. You can use the owner. convention in the old name of the table, but an error occurs during compilation if you try to use the owner. convention in the new name of the table.

If you are using Enterprise Decision Server, you cannot rename a table that contains a dependent GK index.

In an ANSI-compliant database, you must use the owner. convention in the old name of the table if you are referring to a table that you do not own.

You cannot use the RENAME TABLE statement to move a table from the current database to another database or to move a table from another database to the current database. The table that you want to rename must reside in the current database. The renamed table that results from the statement remains in the current database.

If a view references the table that was renamed, and the view resides in the same database as the table, the database server updates the text of the view in the sysviews system catalog table to reflect the new table name. For further information on the sysviews system catalog table, see the Informix Guide to SQL: Reference.

If you rename a table that has a trigger, it produces the following results:

• The database server replaces the name of the table in the trigger definition.
• The table name is not replaced where it appears inside any triggered actions.
• The database server 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.

The following example reorganizes the items table. The intent is to move the quantity column from the fifth position to the third. The example illustrates the following steps:

1. Create a new table, new_table, that contains the column quantity in the third position.
2. Fill the table with data from the current items table.
3. Drop the old items table.
4. 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; 

Related statements: ALTER TABLE, CREATE TABLE, DROP TABLE, and RENAME COLUMN.