在更新表的主键字段或DELETE数据时,如果遇到ORA-02292: integrity constraint (xxxx) violated - child record found 这个是因为主外键关系,下面借助一个小列子来描述一下这个错误:
实验:
--建立主表
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
--报错,解决:首先找到外键约束和相关表,禁用外键约束,处理数据,然后启用外键约束:查询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>
---如果是删除数据遇到这种情况,可以先删除子表数据,然后删除父表数据,如下:
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
--先删除子表中相关列数据,
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>
完结!!!!!!!!!!!!!!1
实验:
--建立主表
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
--报错,解决:首先找到外键约束和相关表,禁用外键约束,处理数据,然后启用外键约束:查询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>
---如果是删除数据遇到这种情况,可以先删除子表数据,然后删除父表数据,如下:
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
--先删除子表中相关列数据,
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>
完结!!!!!!!!!!!!!!1
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31397003/viewspace-2146798/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31397003/viewspace-2146798/