In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
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.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.