程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> ORA-02292: integrity constraint (xxxx) violated,ora-02292violated

ORA-02292: integrity constraint (xxxx) violated,ora-02292violated

編輯:Oracle教程

ORA-02292: integrity constraint (xxxx) violated,ora-02292violated


在更新表的主鍵字段或DELETE數據時,如果遇到ORA-02292: integrity constraint (xxxx) violated - child record found 這個是因為主外鍵關系,下面借助一個小列子來描述一下這個錯誤:

SQL> create table student
  2  (
  3    id  number,
  4    name nvarchar2(12),
  5    constraint pk_student primary key(id) 
  6  );
 
Table created.
QL> create table grades
  2  (  id  number ,
  3     subject nvarchar2(12),
  4     scores number,
  5     constraint pk_grades primary key(id ,subject),
  6     constraint fk_student_id foreign key(id) references student(id)
  7  );
 
Table created.
 
SQL> insert into student
  2  values(1001,'kerry');
 
1 row created.
 
SQL> insert into student
  2  values(1002,'jimmy');
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> insert into grades
  2  values(1001, 'math', 120);
 
1 row created.
 
SQL> insert into grades
  2  values(1001, 'english', 106);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
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 (TEST.FK_STUDENT_ID) violated - child record
found
 
 
SQL> 

 

 

 

遇到這種情況,首先找到外鍵約束和相關表,禁用外鍵約束,處理數據,然後啟用外鍵約束。

SELECT OWNER, CONSTRAINT_NAME, TABLE_NAME 
FROM DBA_CONSTRAINTS 
WHERE CONSTRAINT_NAME=&CONSTRAINT_NAME;
 
SELECT OWNER, CONSTRAINT_NAME, TABLE_NAME 
FROM USER_CONSTRAINTS 
WHERE CONSTRAINT_NAME=&CONSTRAINT_NAME;
 
 
SQL> ALTER TABLE TEST.GRADES DISABLE CONSTRAINT FK_STUDENT_ID;
 
Table altered.
 
SQL> update student set id=1004 where name='kerry';
 
1 row updated.
 
 
SQL> update grades set id=1004 where id =1001;
 
2 rows updated.
 
SQL> commit;
 
Commit complete.
 
SQL> ALTER TABLE TEST.GRADES ENABLE CONSTRAINT FK_STUDENT_ID;
 
Table altered.
 
SQL> 

 

 

如果是刪除數據遇到這種情況,可以先刪除子表數據,然後刪除父表數據。

SQL> delete from student where id=1004;
delete from student where id=1004
*
ERROR at line 1:
ORA-02292: integrity constraint (TEST.FK_STUDENT_ID) violated - child record
found
 
 
SQL> delete from grades
  2  where id in
  3  ( select id from student
  4    where id=1004);
 
2 rows deleted.
 
SQL> delete from student where id=1004;
 
1 row deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> 
 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved