数据库更新表数据时出现ORA

64次阅读
没有评论

共计 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 小编将为大家输出更多高质量的实用文章!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-15发表,共计2640字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)