8 Replies Latest reply: Aug 9, 2012 6:49 AM by Toon.Koppelaars2-Oracle RSS

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

    CP
      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
          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
            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
              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
                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
                  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
                    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
                      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-Oracle
                        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?