database oracle - 违反完整性约束 - 找到子记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11054272/
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
oracle - integrity constraint violated - child record found
提问by javagirl
I have a huge pl/sql stored procedure, where we make some deletions as long as insertions. Procedure starts with the statement
我有一个巨大的 pl/sql 存储过程，只要插入，我们就会在其中进行一些删除。程序从语句开始
EXECUTE IMMEDIATE 'SET CONSTRAINTS ALL DEFERRED'
And at the last commitstatement I receive ORA-02292: integrity constraint AAA violated.
The questions is that I don't know which statement exactly causes it, because I have both deletion from parent table (before child one) and insertions into child table before parent.
I tried to google it, but everywhere it's said that 02292 happens when I try to delete only.
在最后一条commit语句中，我收到了 ORA-02292：违反了完整性约束 AAA。问题是我不知道究竟是哪个语句导致了它，因为我从父表中删除（在子表之前）和在父表之前插入到子表中。我试图用谷歌搜索它，但到处都说 02292 发生在我只尝试删除时。
Could this error happen when I try to insert value in the child table but there is no this entry in the parent?
当我尝试在子表中插入值但父表中没有此条目时会发生此错误吗？
Also, what is the difference between 02292 and 02291?
另外，02292和02291有什么区别？
回答by Bob Jarvis - Reinstate Monica
ORA-02292 indicates that the error occurred because A) the constraint has no ON DELETE clause specified, and B) you deleted a row from the master table which had matching references in the child table. Your choices are to modify the constraint so have an ON DELETE CASCADE or to ensure that all child records are deleted before deleting from the master. My preference would be to add ON DELETE CASCADE but I suppose there could be reasons not to do so. See ORA-02292.
ORA-02292 表示发生错误是因为 A) 约束没有指定 ON DELETE 子句，并且 B) 您从主表中删除了在子表中有匹配引用的行。您的选择是修改约束，以便使用 ON DELETE CASCADE 或确保在从主记录中删除之前删除所有子记录。我更喜欢添加 ON DELETE CASCADE 但我想可能有理由不这样做。见ORA-02292。
ORA-02291 is sort of the opposite of this. ORA-02291 will be raised if you attempt to insert a row into a child table, but the key field values on your new child row as specified in the constraint do not exist in the master table. See ORA-02291.
ORA-02291 与此相反。如果您尝试将行插入到子表中，但主表中不存在约束中指定的新子行上的键字段值，则会引发 ORA-02291。见ORA-02291。
回答by Lorenzo Lerate
If you want to disable the constraint from the name to solve ORA-02292.
如果要从名称中禁用约束来解决 ORA-02292。
Look for the table name bounded to that constraint
SELECT owner, table_name FROM dba_constraints WHERE constraint_name = '{CONSTRAINT_NAME}';Disable constraint (this command should be executed by an admin user)
ALTER TABLE {TABLE_NAME} DISABLE constraint {CONSTRAINT_NAME} cascade;
查找绑定到该约束的表名
SELECT owner, table_name FROM dba_constraints WHERE constraint_name = '{CONSTRAINT_NAME}';禁用约束（此命令应由管理员用户执行）
ALTER TABLE {TABLE_NAME} DISABLE constraint {CONSTRAINT_NAME} cascade;
