代码改变世界

ORA-02292: integrity constraint (xxxx) violated - child record found

  潇湘隐者  阅读(14513)  评论(0编辑  收藏  举报

在更新表的主键字段或DELETE数据时,如果遇到ORA-02292: integrity constraint (xxxx) violated - child record found 这个是因为主外键关系,下面借助一个小列子来描述一下这个错误:

SQL> create table student
  2  (
  3    id  number,
  4    name nvarchar2(12),
  5    constraint pk_student primary key(id) 
  6  );
 
Table created.
QL> create table grades
  2  (  id  number ,
  3     subject nvarchar2(12),
  4     scores number,
  5     constraint pk_grades primary key(id ,subject),
  6     constraint fk_student_id foreign key(id) references student(id)
  7  );
 
Table created.
 
SQL> insert into student
  2  values(1001,'kerry');
 
1 row created.
 
SQL> insert into student
  2  values(1002,'jimmy');
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> insert into grades
  2  values(1001, 'math', 120);
 
1 row created.
 
SQL> insert into grades
  2  values(1001, 'english', 106);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> update student set id=1004 where name='kerry';
update student set id=1004 where name='kerry'
*
ERROR at line 1:
ORA-02292: integrity constraint (TEST.FK_STUDENT_ID) violated - child record
found
 
 
SQL> 

 

clip_image001

 

 

遇到这种情况,首先找到外键约束和相关表,禁用外键约束,处理数据,然后启用外键约束。

SELECT OWNER, CONSTRAINT_NAME, TABLE_NAME 
FROM DBA_CONSTRAINTS 
WHERE CONSTRAINT_NAME=&CONSTRAINT_NAME;
 
SELECT OWNER, CONSTRAINT_NAME, TABLE_NAME 
FROM USER_CONSTRAINTS 
WHERE CONSTRAINT_NAME=&CONSTRAINT_NAME;
 
 
SQL> ALTER TABLE TEST.GRADES DISABLE CONSTRAINT FK_STUDENT_ID;
 
Table altered.
 
SQL> update student set id=1004 where name='kerry';
 
1 row updated.
 
 
SQL> update grades set id=1004 where id =1001;
 
2 rows updated.
 
SQL> commit;
 
Commit complete.
 
SQL> ALTER TABLE TEST.GRADES ENABLE CONSTRAINT FK_STUDENT_ID;
 
Table altered.
 
SQL> 

 

 

如果是删除数据遇到这种情况,可以先删除子表数据,然后删除父表数据。

SQL> delete from student where id=1004;
delete from student where id=1004
*
ERROR at line 1:
ORA-02292: integrity constraint (TEST.FK_STUDENT_ID) violated - child record
found
 
 
SQL> delete from grades
  2  where id in
  3  ( select id from student
  4    where id=1004);
 
2 rows deleted.
 
SQL> delete from student where id=1004;
 
1 row deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> 
 

编辑推荐:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· DeepSeek 解答了困扰我五年的技术问题
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
历史上的今天:
2015-08-12 RedHat Linux RHEL6配置本地YUM源
2013-08-12 ORACLE的SPFILE与PFILE
点击右上角即可分享
微信分享提示