共计 2640 个字符，预计需要花费 7 分钟才能阅读完成。
本篇内容介绍了“数据库更新表数据时出现 ORA-02292 错误怎么解决”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
在更新表的主键字段或 DELETE 数据时，如果遇到 ORA-02292: integrity constraint (xxxx) violated – child record found 这个是因为主外键关系，下面借助一个小列子来描述一下这个错误：
实验：
– 建立主表
SQL create table student(id number,name nvarchar2(12),constraint pk_student primary key(id));
Table created.
– 建立外键约束表
SQL create table grades(id number ,subject nvarchar2(12),scores number,constraint pk_grades primary key(id ,subject),constraint fk_student_id foreign key(id) references student(id));
Table created.
SQL insert into student values(1001, kerry
1 row created.
SQL insert into student values(1002, jimmy
1 row created.
SQL commit;
Commit complete.
SQL insert into grades values(1001, math , 120);
1 row created.
SQL insert into grades values(1001, english , 106);
1 row created.
SQL commit;
Commit complete.
– 查看：
SQL select * from student;
ID NAME
———- ————————
1001 kerry
1002 jimmy
SQL select * from grades;
ID SUBJECT SCORES
———- ———————— ———-
1001 math 120
1001 english 106
– 更新主表列
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 (SYS.FK_STUDENT_ID) violated – child record
found
– 报错，解决：首先找到外键约束和相关表，禁用外键约束，处理数据，然后启用外键约束：查询 dba_constraints
SQL col OWNER for a10
SQL select owner,CONSTRAINT_NAME,TABLE_NAME,SEARCH_CONDITION,VALIDATED from dba_constraints where constraint_name= FK_STUDENT_ID
OWNER CONSTRAINT_NAME TABLE_NAME SEARCH_CONDITION VALIDATED
———- —————————— —————————— —————————— ————-
SYS FK_STUDENT_ID GRADES VALIDATED
SQL
–disable 约束：
SQL alter table sys.grades disable constraint FK_STUDENT_ID;
Table altered.
SQL
SQL update student set id=1004 where name= kerry
1 row updated.
SQL commit;
Commit complete.
SQL update grades set id=1004 where id =1001;
2 rows updated.
SQL commit;
Commit complete.
– 修改完后，再 enable 约束：
SQL alter table sys.grades enable constraint FK_STUDENT_ID;
Table altered.
– 查看：
SQL select * from student;
ID NAME
———- ————————
1004 kerry
1002 jimmy
SQL select * from grades;
ID SUBJECT SCORES
———- ———————— ———-
1004 math 120
1004 english 106
SQL
— 如果是删除数据遇到这种情况，可以先删除子表数据，然后删除父表数据，如下：
SQL delete from student where id=1004;
delete from student where id=1004
*
ERROR at line 1:
ORA-02292: integrity constraint (SYS.FK_STUDENT_ID) violated – child record found
– 先删除子表中相关列数据，
SQL delete from grades where id in (select id from student where id=1004);
2 rows deleted.
– 再次删除
SQL delete from student where id=1004;
1 row deleted.
SQL commit;
Commit complete.
SQL select * from student;
ID NAME
———- ————————
1002 jimmy
SQL select * from grades;
no rows selected
SQL
“数据库更新表数据时出现 ORA-02292 错误怎么解决”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！
