Oracle - 删除表约束而不删除表

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数据库的批量迁移。第一步是将一大堆表重命名为稍后删除它们的准备（但我现在需要将数据保存在其中）。需要删除对它们的任何外键约束 - 它们根本不应连接到数据库的其余部分。如果我现在放弃它们我可以使用CASCADE CONSTRAINTS，但重命名只是改变了约束。

Is there a way I can drop all of the constraints that CASCADE CONSTRAINTS would drop without dropping the table itself?

有没有办法可以放弃CASCADE CONSTRAINTS在不丢弃表本身的情况下放下的所有约束？

2 个解决方案

#1

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 loop;

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子句中指定列表，除非它们的名称有一些模式。

#2

You can disable/re-enable constraints without dropping them. Take a look at this article.

您可以在不删除约束的情况下禁用/重新启用约束。看看这篇文章。

#1