This discussion is archived
8 Replies Latest reply: Aug 9, 2012 4:49 AM by Toon.Koppelaars2 RSS

ORA-02291: integrity constraint violated - parent key not found

CP Newbie
Currently Being Moderated
I have a row in parent table. I am using the same sequence from the parent table during insertion. (In fact I am getting the sequence from the drop down during insertion in plsql developer tool). Still I am getting this error.

1) Parent table's primary key is Child table's primary key as well as foreign key.
2) Parent table's primary key is referred as primary key in some other child table as well.
3) But not using Supertype-Subtype relationship. Just direct reference

Can you please tell me why am I getting this error.
  • 1. Re: ORA-02291: integrity constraint violated - parent key not found
    Venkadesh Raja Pro
    Currently Being Moderated
    Refer this

    http://www.dba-oracle.com/t_ora_02291_integrity_constraint_string_string_violated_parent_key_not_found.htm
  • 2. Re: ORA-02291: integrity constraint violated - parent key not found
    CP Newbie
    Currently Being Moderated
    I have referred all these. And I am well aware of this error. But my doubt is why am I not able to insert when I have data in parent table.
  • 3. Re: ORA-02291: integrity constraint violated - parent key not found
    Purvesh K Guru
    Currently Being Moderated
    CP wrote:
    I have a row in parent table. I am using the same sequence from the parent table during insertion. (In fact I am getting the sequence from the drop down during insertion in plsql developer tool). Still I am getting this error.
    You might be using the same sequence object but while inserting you are using sequence.nextval for your Foreign Key.
    Can you prove whats happening using a sample Parent, Child Table and some dummy data.
    1) Parent table's primary key is Child table's primary key as well as foreign key.
    IMV, this is not a good design to keep Foreign Key and Primary Key as same. I am assuming you know the fact that by this design, your Parent can have a Single Child. Hence, a change in Business req, to support multiple childs can be a headache for you. ;)
  • 4. Re: ORA-02291: integrity constraint violated - parent key not found
    sybrand_b Guru
    Currently Being Moderated
    Because the integrity check is made immediately, and the database is temporarily inconsistent.
    When you set the foreign key to be deferred it will evaluate at commit, when everything should be ok again.

    ---------
    Sybrand Bakker
    Senior Oracle DBA
  • 5. Re: ORA-02291: integrity constraint violated - parent key not found
    CP Newbie
    Currently Being Moderated
    Purvesh K:
    I am assuming you know the fact that by this design, your Parent can have a Single Child. Hence, a change in Business req, to support multiple childs can be a headache for you.>

    Can you please let me know why can't I have more than 1 table. As I mentioned in my 2nd point, I have more than 1 child table for a same parent.
  • 6. Re: ORA-02291: integrity constraint violated - parent key not found
    Purvesh K Guru
    Currently Being Moderated
    sybrand_b wrote:
    Because the integrity check is made immediately, and the database is temporarily inconsistent.
    When you set the foreign key to be deferred it will evaluate at commit, when everything should be ok again.

    ---------
    Sybrand Bakker
    Senior Oracle DBA
    Hi Sybrand,

    Curious to understand this.

    In below test case (which I am assuming is on same lines as OP's):
    drop sequence seq;
    drop table chi;
    drop table par;
    
    
    create sequence seq start with 1 increment by 1 maxvalue 99 cycle;
    
    create table par
    (
      pk_col      number      primary key,
      col         varchar2(2)
    );
    
    create table chi
    (
      pk_col      number      primary key,
      col         varchar2(5),
      constraint fk_pk_col  foreign key(pk_col) references par(pk_col)
    );
    
    select *
      from user_cons_columns
     where table_name in ('PAR', 'CHI');
    
    OWNER                          CONSTRAINT_NAME                TABLE_NAME                     COLUMN_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      POSITION               
    ------------------------------ ------------------------------ ------------------------------ ------------------------------------
    PURVESH                        SYS_C0011977                   PAR                            PK_COL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           1                      
    PURVESH                        SYS_C0011978                   CHI                            PK_COL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           1                      
    PURVESH                        FK_PK_COL                      CHI                            PK_COL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           1                      
    
    
    insert into par values (seq.nextval, 'A');
    insert into chi values (seq.currval, 'AA');
    
    insert into par values (seq.nextval, 'B');
    insert into chi values (seq.currval, 'BB');
    
    insert into par values (seq.nextval, 'C');
    insert into chi values (seq.currval, 'CC');
    
    insert into par values (seq.nextval, 'D');
    insert into chi values (seq.currval, 'DD');
    
    --Successfully Inserted.
    
    insert into par values (seq.nextval, 'E');
    insert into chi values (seq.nextval, 'EE');
    
    Error starting at line 2 in command:
    insert into chi values (seq.nextval, 'EE')
    Error report:
    SQL Error: ORA-02291: integrity constraint (PURVESH.FK_PK_COL) violated - parent key not found
    02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
    *Cause:    A foreign key value has no matching primary key value.
    *Action:   Delete the foreign key or add a matching primary key.
    In this test case, an Integrity was made immediately and reported an error.

    Can you please explain if I misunderstood your point?
  • 7. Re: ORA-02291: integrity constraint violated - parent key not found
    Purvesh K Guru
    Currently Being Moderated
    CP wrote:
    Purvesh K:
    I am assuming you know the fact that by this design, your Parent can have a Single Child. Hence, a change in Business req, to support multiple childs can be a headache for you.>

    Can you please let me know why can't I have more than 1 table. As I mentioned in my 2nd point, I have more than 1 child table for a same parent.
    I never mentioned "can't I have more than 1 table". I was talking in terms of Records.

    Say, your Parent ID 1, can possibly have more than 1 child (speaking in General terms excluding your Requirements), hence if you keep your Foreign Key as Primary Key, your Table would not allow you, would it?
    I do understand that you being knowledgeable about your requirements have designed the Tables accordingly, I was just raising a concern I saw.

    No Objections to your design. :)
  • 8. Re: ORA-02291: integrity constraint violated - parent key not found
    Toon.Koppelaars2 Newbie
    Currently Being Moderated
    Maybe your child table has a 2nd foreign key to some other table as well?
    Or is the error clearly stating the FK-constraint name to the parent table you mention?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points