5 Replies Latest reply: Jul 16, 2012 11:12 AM by 949794 RSS

    Foreign Key Error - parent keys not found

    orclrunner
      I have two table like so:

      CREATE TABLE cases
      (
      case_id number(10),
      ...
      )
      TABLESPACE surgery
      /

      CREATE TABLE caseprocs
      (
      caseproc_id number(10),
      case_id number(10 NOT NULL,
      ...
      )
      TABLESPACE surgery
      /

      -- Now I create the constraints:

      alter table cases
      add constraint cases_pk primary key(case_id)
      /
      alter table caseprocs
      add constraint caseprocs_pk primary key(caseproc_id)
      /
      -- everything runs successfully to this point

      alter table caseprocs
      add ( constraint caseprocs_fk foreign key (case_id)
      references cases (case_id)
      on delete cascade )
      /

      ORA-02298: cannot validate(SURGERY.CASEPROCS_FK) - parent keys not found

      Any suggestions? I am able to create foreign key constraints on other tables in the surgery shema without any problems.
        • 1. Re: Foreign Key Error - parent keys not found
          249980
          This all works fine in Oracle 9.2 *

          * minor typo in caseprocs DDL

          What version of Oracle are you running ?
          • 2. Re: Foreign Key Error - parent keys not found
            438877
            caseprocs table contains case_id which is not in
            cases table. Check it. If you don't worry about
            orphan rows in caseprocs - use novalidate option.

            Rgds.
            • 3. Re: Foreign Key Error - parent keys not found
              438877
              Easy example:
              SQL> edit
              Wrote file afiedt.buf
              &nbsp
                1  CREATE TABLE caseprocs
                2  (
                3  caseproc_id number(10),
                4  case_id number(10) NOT NULL
                5* )
              SQL> /
              &nbsp
              Table created.
              &nbsp
              SQL> insert into caseprocs values(1,1);
              &nbsp
              1 row created.
              &nbsp
              SQL> commit;
              &nbsp
              Commit complete.
              &nbsp
              SQL> alter table cases
                2  add constraint cases_pk primary key(case_id)
                3  /
              &nbsp
              Table altered.
              &nbsp
              SQL> alter table caseprocs
                2  add constraint caseprocs_pk primary key(caseproc_id)
                3  /
              &nbsp
              Table altered.
              &nbsp
              SQL> alter table caseprocs
                2  add ( constraint caseprocs_fk foreign key (case_id)
                3  references cases (case_id)
                4  on delete cascade )
                5  /
              add ( constraint caseprocs_fk foreign key (case_id)
              &nbsp                 *
              ERROR at line 2:
              ORA-02298: cannot validate (SCOTT.CASEPROCS_FK) - parent keys not found
              But:
              SQL> delete from caseprocs;
              &nbsp
              1 row deleted.
              &nbsp
              SQL> alter table caseprocs
                2  add ( constraint caseprocs_fk foreign key (case_id)
                3  references cases (case_id)
                4  on delete cascade )
                5  /
              &nbsp
              Table altered.
              or:
              SQL> alter table caseprocs drop constraint caseprocs_fk;
              &nbsp
              Table altered.
              &nbsp
              SQL> insert into caseprocs values(1,1);
              &nbsp
              1 row created.
              &nbsp
              SQL> commit;
              &nbsp
              Commit complete.
              &nbsp
              SQL> alter table caseprocs
                2  add ( constraint caseprocs_fk foreign key (case_id)
                3  references cases (case_id)
                4  on delete cascade novalidate )
                5  /
              &nbsp
              Table altered.
              Rgds.

              • 4. Re: Foreign Key Error - parent keys not found
                orclrunner
                Thanks All!

                The problem was I had over 1,300 orphaned records in the child table (caseprocs). Once I deleted these orphaned records I was able to create a foreign key constraint.

                • 5. Re: Foreign Key Error - parent keys not found
                  949794
                  Thanks!!

                  I deleted all of the values in the column I wanted to use for my foriegn key then was allowed to create the key.