• ORA-02292: integrity constraint (xxxx) violated


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

     

    clip_image001

     

     

    遇到这种情况,首先找到外键约束和相关表,禁用外键约束,处理数据,然后启用外键约束。

    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> 
     

  • 相关阅读:
    openstack官方指导书
    获取当前日期时间并格式化
    获取url中的参数
    页签切换
    app开屏广告
    开发接口文档--本接口文档是读取控制器方法上的注释自动生成的
    bzoj 1491: [NOI2007]社交网络
    bzoj 3996: [TJOI2015]线性代数
    5.6水题合集
    bzoj 3528: [Zjoi2014]星系调查
  • 原文地址:https://www.cnblogs.com/kerrycode/p/5765644.html
Copyright © 2020-2023  润新知