共計(jì) 2640 個字符，預(yù)計(jì)需要花費(fèi) 7 分鐘才能閱讀完成。
本篇內(nèi)容介紹了“數(shù)據(jù)庫更新表數(shù)據(jù)時出現(xiàn) ORA-02292 錯誤怎么解決”的有關(guān)知識，在實(shí)際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！
在更新表的主鍵字段或 DELETE 數(shù)據(jù)時，如果遇到 ORA-02292: integrity constraint (xxxx) violated – child record found 這個是因?yàn)橹魍怄I關(guān)系，下面借助一個小列子來描述一下這個錯誤：
實(shí)驗(yàn)：
– 建立主表
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
– 報(bào)錯，解決：首先找到外鍵約束和相關(guān)表，禁用外鍵約束，處理數(shù)據(jù)，然后啟用外鍵約束：查詢 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
— 如果是刪除數(shù)據(jù)遇到這種情況，可以先刪除子表數(shù)據(jù)，然后刪除父表數(shù)據(jù)，如下：
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
– 先刪除子表中相關(guān)列數(shù)據(jù)，
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
“數(shù)據(jù)庫更新表數(shù)據(jù)時出現(xiàn) ORA-02292 錯誤怎么解決”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注丸趣 TV 網(wǎng)站，丸趣 TV 小編將為大家輸出更多高質(zhì)量的實(shí)用文章！
