SQL Oracle - 删除表约束而不删除表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3701233/
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
Oracle - drop table constraints without dropping tables
提问by Submonoid
I'm doing some bulk migration of a large Oracle database. The first step of this involves renaming a whole load of tables as a preparation for dropping them later (but I need to keep the data in them around for now). Any foreign key constraints on them need to be dropped - they shouldn't be connected to the rest of the database at all. If I were dropping them now I could CASCADE CONSTRAINTS, but rename simply alters the constraints.
我正在对大型 Oracle 数据库进行一些批量迁移。第一步涉及重命名整个表负载，为以后删除它们做准备（但我现在需要保留其中的数据）。需要删除对它们的任何外键约束——它们根本不应该连接到数据库的其余部分。如果我现在删除它们，我可以级联约束，但重命名只会改变约束。
Is there a way I can drop all of the constraints that CASCADE CONSTRAINTS would drop without dropping the table itself?
有没有办法可以删除所有 CASCADE CONSTRAINTS 会删除的约束而不删除表本身？
回答by APC
You can do it with dynamic SQL and the data dictionary:
您可以使用动态 SQL 和数据字典来实现：
begin
for r in ( select table_name, constraint_name
from user_constraints
where constraint_type = 'R' )
loop
execute immediate 'alter table '|| r.table_name
||' drop constraint '|| r.constraint_name;
end loop;
end;
If the tables are owned by more than one user you'll need to drive from DBA_CONSTRAINTS and include OWNER in the projection and the executed statement. If you want to touch less than all the tables I'm afraid you'll need to specify the list in the WHERE clause, unless there's some pattern to their names.
如果表由多个用户拥有，您需要从 DBA_CONSTRAINTS 驱动并在投影和执行的语句中包含 OWNER。如果您想接触的表少于所有表，恐怕您需要在 WHERE 子句中指定列表，除非它们的名称有某种模式。
回答by Klaus Byskov Pedersen
You can disable/re-enable constraints without dropping them. Take a look at this article.
您可以禁用/重新启用约束而不删除它们。看看这篇文章。
