3 Replies Latest reply: May 11, 2008 8:34 PM by 26741 RSS

    ORA-02298 : cannot validate - parent keys not

    549855
      Hi,
      I am trying to create a FK-PK constraints.
      In the alter table command i am using the caluse:
      exceptions INTO ... in order to catch all the exception rows .
      Can you please explain why i am getting this error message ?

      I am using the following command:
      SQL> ALTER TABLE STARQ.CALL_FACT ADD (
      2 CONSTRAINT CALL_FACT_FK_cmcustdim1
      3 FOREIGN KEY (CUST_ID)
      4 REFERENCES STARQ.CM_CUST_DIM_1 (DWH_CUST_ID)
      5 exceptions INTO starq.exceptions );

      CONSTRAINT CALL_FACT_FK_cmcustdim1
      *
      ERROR at line 2:
      ORA-02298: cannot validate (STARQ.CALL_FACT_FK_CMCUSTDIM1) - parent keys not found
        • 1. Re: ORA-02298 : cannot validate - parent keys not
          Eduardo Legatti
          Hi,

          >>Can you please explain why i am getting this error message ?
          This is a normal behavior. After issued the "alter table add constraint ..." command, you need to query on EXCEPTIONS table.
          LEGATTI@XE> create table a (id number constraint pk_a primary key);

          Table created.

          LEGATTI@XE> create table b (id number);

          Table created.

          LEGATTI@XE> insert into b values (1);

          1 row created.

          LEGATTI@XE> create table exceptions(row_id rowid,
            2                     owner varchar2(30),
            3                     table_name varchar2(30),
            4                     constraint varchar2(30));

          Table created.

          LEGATTI@XE> alter table b add constraint fk_b_a foreign key (id) references a exceptions into exceptions;
          alter table b add constraint fk_b_a foreign key (id) references a exceptions into exceptions
          *
          ERROR at line 1:
          ORA-02298: cannot validate (LEGATTI.FK_B_A) - parent keys not found

          LEGATTI@XE> select * from exceptions;

          ROW_ID             OWNER                          TABLE_NAME                     CONSTRAINT
          ------------------ ------------------------------ ------------------------------ ------------------------------
          AAAD3mAAEAAAABoAAA LEGATTI                        B                              FK_B_A
          Cheers

          Legatti
          • 2. Re: ORA-02298 : cannot validate - parent keys not
            549855
            Hi Legatti,
            Thas was great example,but could you please explain me why only after insterting
            more than 70,000 rows into the exceptions table the error raised ?

            SQL> ALTER TABLE STARQ.CALL_FACT ADD (
            2 CONSTRAINT CALL_FACT_FK_cmcustdim1
            3 FOREIGN KEY (CUST_ID)
            4 REFERENCES STARQ.CM_CUST_DIM_1 (DWH_CUST_ID)
            5 exceptions INTO starq.exceptions );

            CONSTRAINT CALL_FACT_FK_cmcustdim1
            *
            ERROR at line 2:
            ORA-02298: cannot validate (STARQ.CALL_FACT_FK_CMCUSTDIM1) - parent keys not found


            SQL> select count(*) from starq.exceptions;

            COUNT(*)
            ----------
            70539
            • 3. Re: ORA-02298 : cannot validate - parent keys not
              26741
              With the exceptions table, you're telling Oracle, "yes, please raise an error
              but log all the rows which are failing". Therefore, Oracle logs those 70,539
              failing rows into the exceptions table and then reports the error for you.
              The constraint has failed validation but at least now you know which rows
              are failing (maybe your table has 1 million rows but only 70,539 are failing).

              Had it failed at the first row as you expected, then it wouldn't log those
              70,539 rows in the exceptions table. You'd have to write your own procedures
              to identify all the failing rows, not just the first one.

              So, isn't it better that Oracle tells you which rows have failed ? That is the
              purpose of the exceptions table.