RENAME COLUMN

Use the RENAME COLUMN statement to change the name of a column.

This statement is an extension to the ANSI/ISO standard for SQL.

Syntax

>>-RENAME COLUMN--+--------+--table--.--old_column-------------->
                  '-owner.-'
 
>--TO--new_column----------------------------------------------><

Element	Description	Restrictions	Syntax
new_column	Name that you declare here to replace old_column	Must be unique among column names in table. See also How Triggers Are Affected.	Identifier, p. Identifier
old_column	Column to rename	Must exist within table	Identifier, p. Identifier
owner	Owner of the table	Must be the owner of table	Owner Name, p. Owner Name
table	Table that contains old_column	Must exist in the current database	Identifier, p. Identifier

Usage

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

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

In Extended Parallel Server, you cannot rename the columns of a fragmented table if the table is fragmented by range. For more information, see RANGE Method Clause (XPS).

How Views and Check Constraints Are Affected

If you rename a column that appears in a view, the text of the view definition in the sysviews system catalog table is updated to reflect the new column name. If you rename a column that appears in a check constraint, the text of the check constraint in the syschecks system catalog table is updated to reflect the new column name.

How Triggers Are Affected

If you rename a column that appears within the definition a trigger, it is replaced with the new name only in the following instances:

When it appears as part of a correlation name inside the FOR EACH ROW action clause of a trigger
When it appears as part of a correlation name in the INTO clause of an EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement
When it appears as a triggering column in the UPDATE clause

When the trigger executes, if the database server encounters a column name that no longer exists in the table, an error is returned.

Example of RENAME COLUMN

The following example assigns the new name of c_num to the customer_num column in the customer table: