在更新表的主鍵字段或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>