禁用 Oracle 中的所有表约束 [英] Disable all table constraints in Oracle

问题描述

如何使用单个命令禁用 Oracle 中的所有表约束?这可以用于单个表、表列表或所有表.

How can I disable all table constrains in Oracle with a single command? This can be either for a single table, a list of tables, or for all tables.

推荐答案

最好避免写出临时假脱机文件.使用 PL/SQL 块.您可以从 SQL*Plus 运行它或将它放入包或过程中.加入 USER_TABLES 是为了避免视图约束.

It is better to avoid writing out temporary spool files. Use a PL/SQL block. You can run this from SQL*Plus or put this thing into a package or procedure. The join to USER_TABLES is there to avoid view constraints.

您不太可能真的想禁用所有约束(包括 NOT NULL、主键等).您应该考虑将constraint_type 放在WHERE 子句中.

It's unlikely that you really want to disable all constraints (including NOT NULL, primary keys, etc). You should think about putting constraint_type in the WHERE clause.

BEGIN
  FOR c IN
  (SELECT c.owner, c.table_name, c.constraint_name
   FROM user_constraints c, user_tables t
   WHERE c.table_name = t.table_name
   AND c.status = 'ENABLED'
   AND NOT (t.iot_type IS NOT NULL AND c.constraint_type = 'P')
   ORDER BY c.constraint_type DESC)
  LOOP
    dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" disable constraint ' || c.constraint_name);
  END LOOP;
END;
/

再次启用约束有点棘手 - 您需要先启用主键约束，然后才能在外键约束中引用它们.这可以通过在constraint_type 上使用ORDER BY 来完成.'P' = 主键，'R' = 外键.

Enabling the constraints again is a bit tricker - you need to enable primary key constraints before you can reference them in a foreign key constraint. This can be done using an ORDER BY on constraint_type. 'P' = primary key, 'R' = foreign key.

BEGIN
  FOR c IN
  (SELECT c.owner, c.table_name, c.constraint_name
   FROM user_constraints c, user_tables t
   WHERE c.table_name = t.table_name
   AND c.status = 'DISABLED'
   ORDER BY c.constraint_type)
  LOOP
    dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" enable constraint ' || c.constraint_name);
  END LOOP;
END;
/

这篇关于禁用 Oracle 中的所有表约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！