[ACCEPTED]-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?-preserve

Accepted answer
Score: 24

If you

ALTER TABLE old_table_name
 RENAME TO new_table_name;

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

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

ALTER TRIGGER trg_bi_foo
  RENAME TO trg_bi_bar;

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

ALTER TABLE bar
  RENAME CONSTRAINT pk_foo TO pk_bar;
Score: 13

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

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

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

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

Score: 1
ALTER TABLE oldName RENAME TO newName

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

More Related questions