如何重命名 Oracle 中的表，以便更新所有外键、约束、触发器和序列并保留任何现有数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9279492/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
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?
提问by Warren Blumenow
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.
我需要在 Oracle 中重命名一个表，但我想确保引用该表的任何外键、约束、触发器和序列都更新为使用新名称。
How can I be sure that I have not broken anything?
我如何确定我没有破坏任何东西？
Note that I want to preserve any existing data that the table contains.
请注意，我想保留表中包含的任何现有数据。
回答by Justin Cave
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 FOOto be named TRG_BI_FOOand you rename the table to BAR, you'd need to alter the trigger explicitly to change its name
现在，虽然约束和触发器将继续正常工作，但它们将保留其原始名称。如果您希望在表名之后保留这些对象的命名约定，则需要做更多的工作。例如，如果您希望在表FOO上插入触发器之前的行级别被命名TRG_BI_FOO并将表重命名为BAR，则需要显式更改触发器以更改其名称
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;
回答by Adam Musch
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_NAMEis generally named TABLE_NAME_PK, renaming TABLE_NAMEto NEW_TABLE_NAMEwon't automatically rename the primary key constraint to NEW_TABLE_NAME_PK.
但是，用于这些对象的任何命名约定都不会自动使用更新后的名称。例如，如果 for 的主键TABLE_NAME通常命名为TABLE_NAME_PK，则重命名TABLE_NAME为NEW_TABLE_NAME不会自动将主键约束重命名为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_DEPENDENCIEScan help identify which of those objects need to be updated.
需要检查的是代码——包、过程和函数——它们引用了旧表名，以及任何引用旧表名的触发器。同样，针对旧表名的视图也会中断。该视图ALL_DEPENDENCIES可以帮助确定哪些对象需要更新。
回答by vc 74
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.
将保留表的依赖项和数据，但总会有一段 PL/SQL 引用将变得无效的旧名称。
