How do I rename a table in Oracle so that all foreign keys, constraints, triggers and sequences are updated and any existing data is preserved?

Question

I need to rename a table in Oracle but I want to be sure that any foreign keys, constraints, triggers and sequences that reference the table are updated to use the new name.

How can I be sure that I have not broken anything?

Note that I want to preserve any existing data that the table contains.

Nicolle

Answer 1

It depends on what you mean by "any foreign keys, constraints, triggers and sequences that reference the table are updated to use the new name."

Any existing indexes, constraints, and triggers against the table being renamed will automatically reference the new name.

However, any naming conventions used for those objects won't automatically use the updated name. For example, if the primary key for TABLE_NAME is generally named TABLE_NAME_PK, renaming TABLE_NAME to NEW_TABLE_NAME won't automatically rename the primary key constraint to NEW_TABLE_NAME_PK.

What will need to be checked is code - packages, procedures, and functions - which referenced the old table name, as well as any triggers which referenced the old table name. Similarly, views against the old table name will break as well. The view ALL_DEPENDENCIES can help identify which of those objects need to be updated.

Septal

Thanks Adam. @Justin suggested that triggers will continue to work correctly. Are you saying that I will need to manually correct the triggers? For example, if I had an autoincrement trigger (CREATE TRIGGER trg_bi_foo BEFORE INSERT ON foo FOR EACH ROW BEGIN SELECT seq_foo.NEXTVAL INTO :NEW.foo_id FROM DUAL; END;) would the "BEFORE INSERT ON foo" be updated automatically to "BEFORE INSERT ON bar"? – Nicolle

Yes. But if you have some other trigger on some other table that performs a query against foo, that trigger won't be automatically updated to query against bar. – Septal

Answer 2

If you

ALTER TABLE old_table_name
 RENAME TO new_table_name;

all the existing constraints (foreign key and other constraints) and triggers will reference the newly renamed object. Sequences have no relationship to tables so there will be no impact on the sequences (though if you mean that you are referencing the sequence in a trigger on the table, the trigger will continue to reference the same sequence after the rename). Any stored procedures that you have written that reference the old table name, however, will need to be updated to reference the new table name.

Now, while the constraints and triggers will continue to work correctly, they will retain their original names. If you have naming conventions for these objects that you want to maintain after the table name, you'd need to do more. For example, if you want a row-level before insert trigger on table FOO to be named TRG_BI_FOO and you rename the table to BAR, you'd need to alter the trigger explicitly to change its name

ALTER TRIGGER trg_bi_foo
  RENAME TO trg_bi_bar;

Similarly, you'd need to rename your constraints and indexes

ALTER TABLE bar
  RENAME CONSTRAINT pk_foo TO pk_bar;

Bowyer

Answer 3

ALTER TABLE oldName RENAME TO newName

Will preserve the table's dependencies and data but there can always be a piece of PL/SQL that references the old name which is going to become invalid.

Azrael