14 Replies Latest reply: Mar 19, 2008 12:33 AM by 378057 RSS

    ora-2291 error not getting fixed

    378057
      I have a table question_bank with a primary key on column qb_id. This key is referenced by other several table as a foreign key. Problem is that the primary key is enabled with index created on this column, but I am unable to insert rows in other table and an error ora--2291 integrity constraint occurs that primary key not found, whereas the requisite primary key is present in question_bank table.

      I have tried dropping the primary key with cascade option and recreated primary key and index but the same error keeps occurring. Can anyone guide me what could be the issue and how to resolve it.

      Warm regards,
      Vipul
        • 1. Re: ora-2291 error not getting fixed
          378057
          Maybe the following information will help:

          Database: Oracle 8.1.7
          Operating System: Windows 2000

          I had created the oracle user with 'imp' utility from an existing Oracle 8.1.7 database, where this primary key is functioning properly.
          • 2. Re: ora-2291 error not getting fixed
            Eduardo Legatti
            Hi,

            Well, the error message is very clear. In a master-child relationship, the master record has to exist before the child can reference it. If not, an ORA-02291 error will be raised. Can you confirm that?

            Cheers

            Legatti
            • 3. Re: ora-2291 error not getting fixed
              378057
              I have around 38000 rows in the question_bank table and it has its child records in other tables. But now after I created it with import utility the child tables does not accept record.

              I have many other tables with master-child relationship but they are functioning fine after the import. It is only with this table question_bank.
              • 4. Re: ora-2291 error not getting fixed
                247514
                When you got the error, it will display the constrain name violated,

                ORA-02291: integrity constraint (xxxx_FK) violated - parent key not found

                You need to check this constrain using DBA_CONSTRAINTS.

                I don't think Oracle will report this wrong, you must have overlooked something.
                • 5. Re: ora-2291 error not getting fixed
                  Eduardo Legatti
                  Hi,

                  >>the child tables does not accept record.
                  You should confirm what are the foreign key constraints defined in that child tables that are causing this "problem" as Yingkuan said. In addition, you can use this SQL below:
                  select r.owner, r.table_name,r.constraint_name
                  from
                  user_constraints r, user_constraints o
                  where
                  r.r_owner = o.owner and r.r_constraint_name = o.constraint_name
                  and o.constraint_type in ('P','U') and r.constraint_type = 'R'
                  and o.table_name = 'QUESTION_BANK';
                  Cheers

                  Legatti
                  • 6. Re: ora-2291 error not getting fixed
                    378057
                    Hello,
                    I did check the foreign key constraints and found to be ok. The result of one of the foreign key for the suggested sql statement is
                    OWNER     TABLE_NAME     CONSTRAINT_NAME
                    TUTOR     QUESTION_CHOICES     QUE_CHOICE_QB_ID_QUE_BANK_R

                    As I have mentioned earlier I have already recreated the primary key and foreign key but the problem still persists. Should I try exporting the table 'question_bank' and importing it again.

                    Regards....Vipul
                    • 7. Re: ora-2291 error not getting fixed
                      620661
                      I dont think that would solve your problem. Whetever oracle is suggesting is correct. While importing rows into child table it checks for constraints. If data is important for you then you can disable all constraints and then import. Afterwards you can crosscheck if you are able to enforce the constraints.
                      • 8. Re: ora-2291 error not getting fixed
                        378057
                        I suspect the issue in question_bank table as none of the child table is allowing the record to be inserted due to foreign key constraint failing.

                        I wish to take the following steps:
                        a.Truncate table Question_bank.
                        b.Export table question_bank from source database.
                        c.Import table question_bank into target database.

                        What do you say?
                        • 9. Re: ora-2291 error not getting fixed
                          620661
                          I can not imagine about the data you are dealing with, your problem is suggesting that question_bank table doesnot have specific rows needed by reffering tables. As I said you can disable constraint(s) and then import child tables. How will your method change the scenario...There might be one condition in which you may have deleted rows from question_bank table.
                          • 10. Re: ora-2291 error not getting fixed
                            378057
                            The existing child table has the foreign key enabled and I am trying to enter a new record in the child table with a primary key already existing in the master table. Even then an ora-02291 exception is raised. I have not deleted any rows from the master table question_bank.
                            • 11. Re: ora-2291 error not getting fixed
                              620661
                              That can not be. You are missing something somewhere. Import all tables witout constraints and then create constraints manually(just a trivial experiment).
                              • 12. Re: ora-2291 error not getting fixed
                                378057
                                I identified the issue and resolved it. The problem was in the associated index to the primary key. I have many indexes on question_bank table. There was a nonunique index cola_colb_idx on colA (primary key column) and colB. Also there was a unique index cola_idx on colA.

                                Now when I imported the table the primary key got associated with cola_colb_idx, which was a nonunique index on two columns.

                                I dropped the table and imported the table without the indexes and constraints and manually created the indexes and constraints. This resolved the issue.

                                I have a question now that how to identify which index is associated with primary key?

                                Thank you for the patience and suggestions.

                                Regards...Vipul
                                • 13. Re: ora-2291 error not getting fixed
                                  Eduardo Legatti
                                  Hi,

                                  >>I have a question now that how to identify which index is associated with primary key?
                                  LEGATTI@XE> create table t (id number constraint pk_t primary key);

                                  Table created.

                                  LEGATTI@XE> select constraint_name,index_name from user_constraints where table_name='T';

                                  CONSTRAINT_NAME                INDEX_NAME
                                  ------------------------------ ------------------------------
                                  PK_T                           PK_T
                                  Cheers

                                  Legatti
                                  • 14. Re: ora-2291 error not getting fixed
                                    378057
                                    Hello Legatti,
                                    Thank you for your input. I believe the sql statement given by you is valid on Oracle 10g. How can I get details in Oracle 8i (8.1.7) as there is no index_name column in user_constraints view.

                                    Regards...Vipul