SQL 删除一行返回 - “ORA-02292: 完整性约束 (..) 违反 - 找到子记录”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23226486/
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
SQL deletion of a row returning- "ORA-02292: integrity constraint (..) violated - child record found"
提问by envyM6
I've got a database consisting of four tables. Relation schema as follows in the picture:
我有一个由四个表组成的数据库。图中关系图如下：
And here are the rows:
这是行：
Now I'm trying to delete the owner with owner id OW1. Because the id is a primary key in owner table and foreign key in other table its not allowing me to delete the row. Here is the SQL I tried:
现在我正在尝试删除所有者 id 的所有者OW1。因为 id 是所有者表中的主键和其他表中的外键，所以不允许我删除该行。这是我尝试过的 SQL：
delete from owners
where ownerid = 'OW1' and petid = 'PT1'
And it returns :
它返回：
ORA-02292: integrity constraint (TEST_1.ADDRESSES_OWNERS_FK) violated - child record found
ORA-02292: integrity constraint (TEST_1.ADDRESSES_OWNERS_FK) violated - child record found
And I'm not allowed to set the delete rule to 'CASCADE' in relation diagram. Please help :(
而且我不允许在关系图中将删除规则设置为“CASCADE”。请帮忙 ：（
回答by Alex Poole
Well, if an anonymous block counts as one statement, just wrap your deletes in a block:
好吧，如果一个匿名块算作一个语句，只需将您的删除包裹在一个块中：
begin
delete from addresses where ownerid = 'OW1';
delete from contacts where ownerid = 'OW1';
delete from pets where ownerid = 'OW1';
delete from owners where ownerid = 'OW1';
end;
/
SQL Fiddle. Seems like a bit of a cheat, but if those are the conditions you've been given...
SQL小提琴。似乎有点作弊，但如果这些是你得到的条件......
回答by Chethan
Sometimes, it is tedious to find out which child reference tables are resisting from deleting the data from the parent table. In such cases, we can find the references to the main table using this query.
有时，找出哪些子引用表拒绝从父表中删除数据很乏味。在这种情况下，我们可以使用此查询找到对主表的引用。
select * from
all_constraints where
r_constraint_name in
(select constraint_name from all_constraints
where table_name= '<PARENT_TABLE_NAME>');
By going through the result list, one can easily find out the references. I hope it helps.
通过查看结果列表，可以轻松找到参考文献。我希望它有帮助。
