oracle 删除具有外键约束的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14261926/
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
Deletion of rows with foreign key constraints
提问by Deepak Sharma
I am working on a requirement.
我正在处理一个需求。
There is one table.I want to delete a row from this table but i cannot delete it because
有一个表。我想从该表中删除一行，但我无法删除它，因为
1.This table is referred by 79 table through foreign key relationship 2.Each of those 79 tables are referred by other 100's of table
1.这张表被79张表通过外键关系引用2.这79张表中的每一张都被其他100张表引用
so you can imagine its kind of tree structure.So i need to start from bottom and reach to TOP.I need to do it through SQL
所以你可以想象它的树状结构。所以我需要从底部开始并到达顶部。我需要通过SQL来完成
So from where I should start ???Do i need to create a temp table or anything which i can do any approach which can be useful???
所以我应该从哪里开始？？？我需要创建一个临时表或任何我可以做的任何有用的方法吗？？？
Note:CASCADE DELETE NOT ALLOWED
注意：不允许级联删除
I might not have access to any sys tables.. any approach that can be handy??? I am using oracle 10g
我可能无法访问任何 sys 表.. 任何可以方便的方法？？？我正在使用 oracle 10g
回答by Christopher Lates
See: http://www.techonthenet.com/oracle/foreign_keys/disable.php
请参阅：http: //www.techonthenet.com/oracle/foreign_keys/disable.php
ALTER TABLE table_name
disable CONSTRAINT constraint_name;
Just be sure to understand the consequences of doing such a procedure and the effects it will have on other data / application functions.
请务必了解执行此类程序的后果及其对其他数据/应用程序功能的影响。
Also, if it wasn't obvious be sure to enable the constraint afterwards
此外，如果不明显，请确保事后启用约束
ALTER TABLE table_name
enable CONSTRAINT constraint_name;
回答by Thomas Tschernich
You could use the following query (that does not need access to sys tables) to get all the Foreign Keys of a table and their corresponding Primary Keys. You could use this to build some kind of recursive function that goes through your tree and deletes all parent entries.
您可以使用以下查询（不需要访问 sys 表）来获取表的所有外键及其对应的主键。您可以使用它来构建某种递归函数，该函数遍历您的树并删除所有父条目。
select uc.constraint_name, ucc.table_name, ucc.column_name from
user_constraints uc
left join user_cons_columns ucc on (uc.r_constraint_name = ucc.constraint_name)
where uc.table_name = 'TABLENAME'
and uc.constraint_type = 'R'
