Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2003
    Posts
    148

    Unanswered: Migrating Relationship Help

    Hi all.
    Im moving from sql server to oracle and I need some help migrating FK's mainly. I have created the tables and migrated the data, but whenever I try to create relationships between the tables, I receive a ORA-02298 parent keys not found error. This happens even in very small tables ( less than 25 rows ) and they are being related to a table that is decent size (10k rows) How can i go about sucessfully migrating these relationships???

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    did you create the Primary Keys on the tables first?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Dec 2003
    Posts
    148
    Sorry, I should have specified. I did in fact create PK's on the tables first.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    have you considered that you can't have a record in the table with a foreign key constraint for which no corresponding primary key value exists.

    please post your ALTER TABLE script which creates the FK.

    thanks
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Dec 2003
    Posts
    148
    Code:
    ALTER TABLE "SOL"."TBL_REQUESTS" 
        ADD (CONSTRAINT "TBL_REQS_FK91092254331110" FOREIGN 
        KEY("CAPPRID") 
        REFERENCES "SOL"."TBL_MANAGERS"("ID"))
    Please note I am using enterprise mgr console for this.
    Lastly, I did consider that my FK's must have corresponding PK's to point to...I did create all the PK's first, which of course was not an issue..thank you for your help in this matter.
    Last edited by RhythmAddict; 08-11-04 at 17:10.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    please run this and post any results:
    PHP Code:
    select distinct CAPPRID
    from TBL_REQUESTS a
    where NOT EXISTS 
    (
         
    select ID 
         from TBL_MANAGERS b
           where a
    .CAPPRID b.ID); 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Dec 2003
    Posts
    148
    My results are below. I am having this same error occur on numerous tables, and in this case my result is zero. However, if I use this query (changing respecive table/field names) on other tables, I do generate values above zero. Am I correct in inferring this query is finding rows FKs that are lacking matching PK's? Assuming this is correct, is there a way for me to find which rows have these errors to correct them? Thanks again

    Code:
    SQL> select distinct CAPPRID 
      2  from TBL_REQUESTS a 
      3  where NOT EXISTS ( 
      4       select ID 
      5       from TBL_MANAGERS b 
      6         where a.CAPPRID = b.ID); 
    
       CAPPRID
    ----------
             0

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    you are correct.

    somehow you have children with no parents.
    In this situation you have a CAPPRID of '0', but
    you have ID value of '0' on the parent table TBL_MANAGERS.

    unfortunately you don't have many options.
    the main option is to clean up all your data by finding the
    'orphans' and either put them in the parent tables or get rid
    of them altogether.

    as a quick fix you could use the "ENABLE NOVALIDATE"
    clause on all your FKs. This creates the FK but only validates new
    or updated rows.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Dec 2003
    Posts
    148
    For example, the tbl_channels table posts these results:

    Code:
    SQL> select distinct CCHANNEL
      2  from TBL_REQUESTS a 
      3  where NOT EXISTS ( 
      4       select CCHANNEL 
      5       from TBL_CHANNELS b 
      6         where a.CCHANNEL = b.CCHANNEL); 
    
    CCHANNEL
    ---------------
    1
    10
    11
    12
    2
    3
    4
    5
    6
    7
    8
    
    CCHANNEL
    ---------------
    9
    
    12 rows selected.
    
    SQL>
    Is there an easy way for me to find the corresponding rows?

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by RhythmAddict
    Is there an easy way for me to find the corresponding rows?
    I am not familiar with your column naming methods.
    This is a good example of why I like to call a column EXACTLY
    what it is instead of making up a new name even though it
    references a column with a DIFFERENT name as a parent.

    This, to me, shows a possible lack of normalization.

    Example:
    TBL_REQUESTS.CAPPRID = TBL_MANAGERS.ID

    how would anyone know that?

    So, from this perspective I can't guess if this is gonna be
    easy for you or not.

    Looks like you are getting the hang of it though.

    if you use the ENABLE NOVALIDATE then you could
    start writing dynamic sql based on the FK relationships.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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