1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-02298: parent keys not found

Discussion in 'SQL PL/SQL' started by fh84, Nov 27, 2009.

  1. fh84

    fh84 Guest

    hello to all,
    the code below:
    Code (SQL):

    CREATE TABLE ELVES
    (
      ELF_NAME VARCHAR2(20)NOT NULL,
      ELF_ID VARCHAR2(2000) NOT NULL,
      ELF_AGE INTEGER,
      ELF_REWARD_POINTS INTEGER,  
      CONSTRAINT ELF_NAME_PK PRIMARY KEY(ELF_NAME)
    );

    --2) Create Children table
    CREATE TABLE CHILDREN
    (
      CHILD_ID VARCHAR2(2000) NOT NULL,
      CHILD_NAME VARCHAR2(2000),
      STREET VARCHAR2(2000),
      COUNTY VARCHAR2(2000),
      POSTCODE VARCHAR2(2000),
      COUNTRY VARCHAR2(2000),
      "DATE" DATE,
      ACTIONED CHAR,
      CONSTRAINT CHILDREN_PK PRIMARY KEY(CHILD_ID)
    );

    --3)Create Toy_Info table
    CREATE TABLE TOY_INFO
    (
      TOY_ID VARCHAR2(2000) NOT NULL,
      TOY_TYPE VARCHAR2(2000) NOT NULL,
      TOY_NAME VARCHAR2(2000) NOT NULL,
      TOY_COST INTEGER,
      AGE_RANGE VARCHAR2(2000),
      CONSTRAINT TOY_INFO_PK PRIMARY KEY(TOY_ID)
    );

    CREATE TABLE TOY_ORDER
    (
      ORDER_ID VARCHAR2(2000) NOT NULL,
      CHILD_ID VARCHAR2(2000) NOT NULL,
      TOY_ID VARCHAR2(2000) NOT NULL,
      ELF_NAME VARCHAR2(20)NOT NULL,
      TOY_NAME VARCHAR2(2000) NOT NULL,
      READY_TO_BE_ALLOCATED CHAR,
      DATE_ALLOCATION DATE,
      CONSTRAINT TOY_ORDER_PK PRIMARY KEY(ORDER_ID)
    );



    ALTER TABLE TOY_ORDER
    ADD CONSTRAINT TOY_ORDER_ELVES_FK1 FOREIGN KEY("ELF_NAME") REFERENCES ELVES("ELF_NAME") ENABLE;

    ALTER TABLE TOY_ORDER
    ADD CONSTRAINT TOY_ORDER_CHILDREN_FK1 FOREIGN KEY("CHILD_ID") REFERENCES CHILDREN ("CHILD_ID") ENABLE;

    ALTER TABLE TOY_ORDER
    ADD CONSTRAINT TOY_ORDER_TOY_INFO_FK1 FOREIGN KEY("TOY_ID") REFERENCES TOY_INFO("TOY_ID") ENABLE;
     
    when i run this code, foreing keys for CHILD_ID and TOY_ID created but not for ELF_NAME.
    *Cause: an alter table validating constraint failed because the table has
    child records.
    Am new to this and would like your help.
    Thank you in advanced!
     
  2. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260

    Hello there,

    I executed the very same code (written in a sql file named script.sql) and run it. I didn't get any error message, everything worked pretty well.

    Code (Text):

    SQL> @script.sql

    Table created


    Table created


    Table created


    Table created


    Table modified


    Table modified


    Table modified

    SQL>
     
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    probably you are giving alter scripts with data in the table wich leads the such errors. Becoz..

     
  4. debasisdas

    debasisdas Active Member

    Messages:
    46
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    Bangalore, India
    Are you running the script along with some other table ?

    How there can be any data when the table is newly created.