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?
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.
Answer
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;