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-02291: integrity constraint violated - parent key not found

Discussion in 'General' started by rajchoopra, Apr 5, 2013.

  1. rajchoopra

    rajchoopra Active Member

    Messages:
    5
    Likes Received:
    1
    Trophy Points:
    65
    Hi All,
    I need to insert some new date in table called state,when doing so i am facing an error mentioned below.
    below i furnished the query i used and the constraints involved both the tables,please guide me to resolve the error so that i can insert the new data
    into state table.

    query used:
    INSERT INTO STATE (COUNTRY_CODE,STATE_CODE,NAME,CREATED_BY,DATE_CREATED ,
    TAX_PERCENTAGE,TIMEZONE_ID,DAYLIGHT_SAVING)
    VALUES(3,'AG','Aguascalientes','User01','05-APR-13',0,1,1)
    /

    ERROR at line 1:
    ORA-02291: integrity constraint (PRODUCT1.FK_STATE_TIMEZONE_ID) violated - parent key not found

    the constraints in state table are :

    TABLE_NAME CONSTRAINT_NAME CONSTRAINT STATUS
    ------------------------------ ------------------------------ ---------- --------
    STATE FK_STATE_UPDATED_BY Foreign ENABLED
    STATE FK_STATE_TIMEZONE_ID Foreign ENABLED
    STATE FK_STATE_CREATED_BY Foreign ENABLED
    STATE FK_STATE_COUNTRY_CODE Foreign ENABLED
    STATE SYS_C004002 Check ENABLED
    STATE SYS_C004003 Check ENABLED
    STATE SYS_C004004 Check ENABLED
    STATE SYS_C004005 Check ENABLED
    STATE SYS_C004006 Check ENABLED
    STATE PK_STATE Primary ENABLED


    constraints in timezone table are


    TABLE_NAME CONSTRAINT_NAME CONSTRAINT STATUS
    ------------------------------ ------------------------------ ---------- --------
    TIMEZONE FK_TIMEZONE_UPDATED_BY Foreign ENABLED
    TIMEZONE FK_TIMEZONE_CREATED_BY Foreign ENABLED
    TIMEZONE SYS_C004070 Check ENABLED
    TIMEZONE SYS_C004071 Check ENABLED
    TIMEZONE SYS_C004072 Check ENABLED
    TIMEZONE SYS_C004073 Check ENABLED
    TIMEZONE PK_TIMEZONE_ID Primary ENABLED
     
  2. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi,

    You are inserting TIMEZONE_ID as 1 . Its a foreign key column . That value i.e 1 should be available in "timezone" table.

    Plz check the below query whether data is available or not.

    select * from timezone where TIMEZONE_ID = 1 ;


    Regards
    Sambasiva Reddy
     
    rajchoopra likes this.
  3. rajchoopra

    rajchoopra Active Member

    Messages:
    5
    Likes Received:
    1
    Trophy Points:
    65
    Hi Sambasiva,

    thanks for reply.sry for not being clear in the query.
    i am trying to insert "cst" in place "1" in the mentioned query.

    ie)
    INSERT INTO STATE (COUNTRY_CODE,STATE_CODE,NAME,CREATED_BY,DATE_CREA TED ,
    TAX_PERCENTAGE,TIMEZONE_ID,DAYLIGHT_SAVING)
    VALUES(3,'AG','Aguascalientes','User01','05-APR-13',0,'cst',1)


    select * from timezone:
    TIMEZ OFFSET CREATED_BY DATE_CREA UPDATED_BY DATE_UPDA
    ----- ---------- -------------------- --------- -------------------- ---------
    EST 0 admin01 06-NOV-00
    CST 1 admin01 06-NOV-00
    MST 2 admin01 06-NOV-00
    PST 3 admin01 06-NOV-00
    HST 5 admin01 06-NOV-00


    this is the data in timezone table, i need to refer cst from this table.
    how can i proceed to refer this ?
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You cannot reference it by number, you need to use the actual key value:

    Code (SQL):
    INSERT INTO STATE (COUNTRY_CODE,STATE_CODE,NAME,CREATED_BY,DATE_CREA TED ,
    TAX_PERCENTAGE,TIMEZONE_ID,DAYLIGHT_SAVING)
    VALUES(3,'AG','Aguascalientes','User01','05-APR-13',0,'CST',1)
    /