DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Parent keys not found

  1. #1
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    Hi,
    when I do the following, I get the ora-2298
    SQL> alter table INLINEAD enable constraint FKINLINEAD01;
    alter table INLINEAD enable constraint FKINLINEAD01
    *
    ERROR at line 1:
    ORA-02298: cannot validate (HUBV2.FKINLINEAD01) - parent keys not found

    How do I find out the parent keys for the parent table? Please help. Thanks.

  2. #2
    Join Date
    Jan 2000
    Location
    Silver Spring MD USA
    Posts
    105
    SELECT r_constraint_name
    FROM DBA_CONSTRAINTS
    WHERE CONSTRAINT_NAME='FKINLINEAD01';


  3. #3
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    Thanks, Maachan,
    I did according to your sql statement, and find the constraint name of the parent table and I enabled it. And then run the query in my first post, but it still gave the same error message. Why is that?
    The following is what I did:

    SQL> SELECT r_constraint_name
    2 FROM DBA_CONSTRAINTS
    WHERE CONSTRAINT_NAME='FKINLINEAD01';

    R_CONSTRAINT_NAME
    ------------------------------
    PKADAGENCY
    PKADAGENCY



    SQL> alter table adagency enable constraint pkadagency;

    Table altered.




  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    ORA error is not complaining that it can't find the PK or UK of the parent table which your FK references. It complains because you have records in your child table that have no coresponding values in the parent table to reference.

    For example, you have a DEPARTMENT table (parent table) with departments 10, 20 and 30. You also have EMPLOYEE table (child table) with employee name and department number they work in. In EMPLOYEE table you have some people with DEPTNO=10, some with DEPTNO=20, some with DEPTNO=30, but you also have entered one employee with DEPTNO=40. Now you try to enforce foreign key on EMPLOYEE.DEPTNO which references DEPARTMENT.DEPTNO. Oracle will not be able to enforce this FK because you have no coresponding row in EMPLOYEE for employee with DEPTNO=40 and will return you ORA-2298.

    If you wan't to find rows from your EMPLOYEE table that has no coresponding row in DEPARTMENT you would go with something like this:

    SELECT * FROM employee WHERE NOT EXISTS
    (SELECT 1 FROM department WHERE department.deptno=employee.deptno);
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    Thanks very much for your detailed explaination, Jmodic.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width