Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

How to solve the ORA-02292 error when the database updates the table data

2025-01-21 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to solve ORA-02292 errors when updating table data in the database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

When updating the primary key field or DELETE data of a table, if you encounter ORA-02292: integrity constraint (xxxx) violated-child record found because of the primary foreign key relationship, the error is described with the help of a small column:

Experiment:

-- create a master table

SQL > create table student (id number,name nvarchar2 (12), constraint pk_student primary key (id))

Table created.

-- establishing a foreign key constraint table

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)

1 row created.

SQL > insert into student values (1002)

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.

-- View:

SQL > select * from student

ID NAME

-

1001 kerry

1002 jimmy

SQL > select * from grades

ID SUBJECT SCORES

-

1001 math 120

1001 english 106

-- Update the main table column

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

Error reporting, solving: first find foreign key constraints and related tables, disable foreign key constraints, process data, and then enable foreign key constraints: query 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 constraint:

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.

-- after modification, enable the constraint:

SQL > alter table sys.grades enable constraint FK_STUDENT_ID

Table altered.

-- View:

SQL > select * from student

ID NAME

-

1004 kerry

1002 jimmy

SQL > select * from grades

ID SUBJECT SCORES

-

1004 math 120

1004 english 106

SQL >

-if this is the case with deleting data, you can delete the child table data first, and then delete the parent table data, as follows:

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

-- delete the related column data in the child table first

SQL > delete from grades where id in (select id from student where id=1004)

2 rows deleted.

-- delete again

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 >

This is the end of the content of "how to solve ORA-02292 errors when updating table data in the database". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report