SQL 禁用 Oracle 中的所有表约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/128623/
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
Disable all table constraints in Oracle
提问by oneself
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.
如何使用单个命令禁用 Oracle 中的所有表约束？这可以用于单个表、表列表或所有表。
回答by WW.
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.
最好避免写出临时假脱机文件。使用 PL/SQL 块。您可以从 SQL*Plus 运行它或将它放入包或过程中。加入 USER_TABLES 是为了避免视图约束。
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.
您不太可能真的想要禁用所有约束（包括 NOT NULL、主键等）。您应该考虑将constraint_type 放在WHERE 子句中。
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;
/
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.
再次启用约束有点棘手 - 您需要先启用主键约束，然后才能在外键约束中引用它们。这可以通过在constraint_type 上使用ORDER BY 来完成。'P' = 主键，'R' = 外键。
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;
/
回答by Cyryl1972
To take in count the dependencies between the constraints:
要计算约束之间的依赖关系：
SET Serveroutput ON
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'
ORDER BY c.constraint_type DESC,c.last_change DESC
)
LOOP
FOR D IN
(SELECT P.Table_Name Parent_Table,C1.Table_Name Child_Table,C1.Owner,P.Constraint_Name Parent_Constraint,
c1.constraint_name Child_Constraint
FROM user_constraints p
JOIN user_constraints c1 ON(p.constraint_name=c1.r_constraint_name)
WHERE(p.constraint_type='P'
OR p.constraint_type='U')
AND c1.constraint_type='R'
AND p.table_name=UPPER(c.table_name)
)
LOOP
dbms_output.put_line('. Disable the constraint ' || d.Child_Constraint ||' (on table '||d.owner || '.' ||
d.Child_Table || ')') ;
dbms_utility.exec_ddl_statement('alter table ' || d.owner || '.' ||d.Child_Table || ' disable constraint ' ||
d.Child_Constraint) ;
END LOOP;
END LOOP;
END;
/
回答by Mike McAllister
It's not a single command, but here's how I do it. The following script has been designed to run in SQL*Plus. Note, I've purposely written this to only work within the current schema.
这不是一个单一的命令，但我是这样做的。以下脚本设计为在 SQL*Plus 中运行。请注意，我特意将其编写为仅在当前模式中工作。
set heading off
spool drop_constraints.out
select
'alter table ' ||
owner || '.' ||
table_name ||
' disable constraint ' || -- or 'drop' if you want to permanently remove
constraint_name || ';'
from
user_constraints;
spool off
set heading on
@drop_constraints.out
To restrict what you drop, filter add a where clause to the select statement:-
要限制您删除的内容，请过滤在 select 语句中添加一个 where 子句：-
- filter on constraint_type to drop only particular types of constraints
- filter on table_name to do it only for one or a few tables.
- 过滤约束类型以仅删除特定类型的约束
- 过滤 table_name 以仅对一张或几张表执行此操作。
To run on more than the current schema, modify the select statement to select from all_constraints rather than user_constraints.
要在多个当前模式上运行，请修改 select 语句以从 all_constraints 而不是 user_constraints 中进行选择。
Note- for some reason I can't get the underscore to NOT act like an italicization in the previous paragraph. If someone knows how to fix it, please feel free to edit this answer.
注意- 由于某种原因，我无法让下划线在上一段中表现得不像斜体。如果有人知道如何修复它，请随时编辑此答案。
回答by user486360
Use following cursor to disable all constraint.. And alter query for enable constraints...
使用以下光标禁用所有约束......并更改启用约束的查询......
DECLARE
cursor r1 is select * from user_constraints;
cursor r2 is select * from user_tables;
BEGIN
FOR c1 IN r1
loop
for c2 in r2
loop
if c1.table_name = c2.table_name and c1.status = 'ENABLED' THEN
dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' disable constraint ' || c1.constraint_name);
end if;
end loop;
END LOOP;
END;
/
回答by David Aldridge
This can be scripted in PL/SQL pretty simply based on the DBA/ALL/USER_CONSTRAINTS system view, but various details make not as trivial as it sounds. You have to be careful about the order in which it is done and you also have to take account of the presence of unique indexes.
这可以非常简单地基于 DBA/ALL/USER_CONSTRAINTS 系统视图在 PL/SQL 中编写脚本，但各种细节并不像听起来那么简单。您必须注意完成的顺序，还必须考虑唯一索引的存在。
The order is important because you cannot drop a unique or primary key that is referenced by a foreign key, and there could be foreign keys on tables in other schemas that reference primary keys in your own, so unless you have ALTER ANY TABLE privilege then you cannot drop those PKs and UKs. Also you cannot switch a unique index to being a non-unique index so you have to drop it in order to drop the constraint (for this reason it's almost always better to implement unique constraints as a "real" constraint that is supported by a non-unique index).
该顺序很重要，因为您不能删除由外键引用的唯一键或主键，并且其他模式中的表上可能存在外键引用您自己的主键，因此除非您具有 ALTER ANY TABLE 权限，否则您不能丢弃那些 PK 和 UK。此外，您不能将唯一索引切换为非唯一索引，因此您必须删除它才能删除约束（因此，将唯一约束实现为非唯一约束支持的“真实”约束几乎总是更好-唯一索引）。
回答by Adam Bellaire
回答by Ankireddy Polu
SELECT 'ALTER TABLE '||substr(c.table_name,1,35)||
' DISABLE CONSTRAINT '||constraint_name||' ;'
FROM user_constraints c, user_tables u
WHERE c.table_name = u.table_name;
This statement returns the commands which turn off all the constraints including primary key, foreign keys, and another constraints.
该语句返回关闭所有约束的命令，包括主键、外键和其他约束。
回答by Cyryl1972
This is another way for disabling constraints (it came from https://asktom.oracle.com/pls/asktom/f?p=100:11:2402577774283132::::P11_QUESTION_ID:399218963817)
这是禁用约束的另一种方法（它来自 https://asktom.oracle.com/pls/asktom/f?p=100:11:2402577774283132::::P11_QUESTION_ID:399218963817）
WITH qry0 AS
(SELECT 'ALTER TABLE '
|| child_tname
|| ' DISABLE CONSTRAINT '
|| child_cons_name
disable_fk
, 'ALTER TABLE '
|| parent_tname
|| ' DISABLE CONSTRAINT '
|| parent.parent_cons_name
disable_pk
FROM (SELECT a.table_name child_tname
,a.constraint_name child_cons_name
,b.r_constraint_name parent_cons_name
,LISTAGG ( column_name, ',') WITHIN GROUP (ORDER BY position) child_columns
FROM user_cons_columns a
,user_constraints b
WHERE a.constraint_name = b.constraint_name AND b.constraint_type = 'R'
GROUP BY a.table_name, a.constraint_name
,b.r_constraint_name) child
,(SELECT a.constraint_name parent_cons_name
,a.table_name parent_tname
,LISTAGG ( column_name, ',') WITHIN GROUP (ORDER BY position) parent_columns
FROM user_cons_columns a
,user_constraints b
WHERE a.constraint_name = b.constraint_name AND b.constraint_type IN ('P', 'U')
GROUP BY a.table_name, a.constraint_name) parent
WHERE child.parent_cons_name = parent.parent_cons_name
AND (parent.parent_tname LIKE 'V2_%' OR child.child_tname LIKE 'V2_%'))
SELECT DISTINCT disable_pk
FROM qry0
UNION
SELECT DISTINCT disable_fk
FROM qry0;
works like a charm
奇迹般有效
回答by diaphol
with cursor for loop (user = 'TRANEE', table = 'D')
带游标 for 循环（用户 = 'TRANEE'，表 = 'D'）
declare
constr all_constraints.constraint_name%TYPE;
begin
for constr in
(select constraint_name from all_constraints
where table_name = 'D'
and owner = 'TRANEE')
loop
execute immediate 'alter table D disable constraint '||constr.constraint_name;
end loop;
end;
/
(If you change disable to enable, you can make all constraints enable)
（如果将禁用更改为启用，则可以启用所有约束）
回答by Cristina Bazar
You can execute all the commands returned by the following query :
您可以执行以下查询返回的所有命令：
select 'ALTER TABLE '||substr(c.table_name,1,35)|| ' DISABLE CONSTRAINT '||constraint_name||' ;' from user_constraints c --where c.table_name = 'TABLE_NAME' ;
选择'ALTER TABLE'||substr(c.table_name,1,35)|| '禁用约束'||constraint_name||' ;' 来自 user_constraints c --where c.table_name = 'TABLE_NAME' ;
