Renaming Tables and Columns
Tables and columns can be renamed using the following statements new to Ingres 10:
[EXEC SQL] ALTER TABLE [schema.]table_name RENAME TO new_table_name
[EXEC SQL] ALTER TABLE [schema.]table_name RENAME [COLUMN] old_column_name TO new_column_name
[EXEC SQL] RENAME TABLE [schema.]table_name TO new_table_name
These commands update the core catalogs to reflect the new table or column name. If a table name or column name is referred to in views, procedures, constraints, rules or other dependent objects, then the table name or column name is not allowed to be renamed; if renaming is attempted, appropriate error messages are given for each case. Ingres deals with each of the dependencies in the following ways:
• Indexes, grants, comments, synonyms, sequences, partitions, and extension tables are automatically transferred to the newly renamed table or column. For grants, query text is updated to reflect the new table and column names.
• A renaming operation on a table is not allowed if any views, procedures, rules, integrities, check or referential constraints, non-grant permits, or security alarms are dependent on the table being renamed. Dependencies for each case will be listed in the error log. Primary key and unique constraints will be transferred to the new table name.
• A renaming operation on a column is not allowed if any views, constraints, or integrities are dependent on the column being renamed or there are any procedures, rules, referential or check constraints, non-grant permits, or security alarms dependent on the table that contains the column being renamed. Dependencies on the column will be listed for each case in the error log.
• Any forms, join definitions, or reports that refer to the old table or column name will be invalidated; these must be recreated and reloaded.
• Any copydb (unloaddb) scripts generated before renaming the table or column will no longer be usable for reload operations.
Note: Notes:
• The new SQL renaming syntax works only with Ingres 10.
• The ALTER TABLE RENAME or RENAME TABLE operation can be rolled back.
• Procedures are not permitted to execute ALTER TABLE RENAME or RENAME TABLE statements.
• Ingres Star does not support ALTER TABLE RENAME or RENAME TABLE statements for renaming tables and columns.
For more information:
• About statement syntax and examples, and complete rules and restrictions for renaming tables and columns, see the SQL Reference Guide.
• About registering renamed tables or columns with Ingres Star, see Register Table as Link Statement—Define Table to Ingres Star and Register Tables with StarView in the Star User Guide.
• About updating the dd_regist_columns and dd_regist_tables tables with updated table and column names, see those sections and Reregistering Renamed Tables and Tables with Renamed Columns in the Replicator User Guide.