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

Thread: ORA-02291: integrity constraint (S3034595.FKPOSITION_ID) violated - parent key not fo

  1. #1
    Join Date
    Sep 2004
    Posts
    1

    ORA-02291: integrity constraint (S3034595.FKPOSITION_ID) violated - parent key not fo

    hi
    i have the above error the
    follwing is the position table


    CREATE TABLE POSITION
    ( POSITION_ID CHAR(8) NOT NULL,
    DESCRIPTION VARCHAR2(50),
    DUTIES VARCHAR2(50),
    SALARY CHAR(15),
    LEVEL_NO CHAR(15),
    CONSTRAINT PKPOSITION PRIMARY KEY (POSITION_ID) );


    and the job table



    CREATE TABLE JOB
    ( JOB_NUMBER CHAR(8) NOT NULL,
    DATE_CREATED DATE,
    DEPT_ID CHAR(9),
    POSITION_ID CHAR(9),

    CONSTRAINT PKJOB PRIMARY KEY (JOB_NUMBER),
    CONSTRAINT FKDEPT_ID FOREIGN KEY (DEPT_ID ) REFERENCES DEPARTMENT,
    CONSTRAINT FKPOSITION_ID FOREIGN KEY (POSITION_ID ) REFERENCES POSITION);



    when i use
    insert into job
    values(31002,'1/july/2001',26001,27002);
    ther is an error
    ORA-02291:integrity constraint (FKPOSITION_ID) violated - parent key not found

    i have allready created data in position table for position_id 27002


    help appreciated

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: ORA-02291: integrity constraint (S3034595.FKPOSITION_ID) violated - parent key no

    Originally posted by ken1212

    i have allready created data in position table for position_id 27002
    No you haven't, that's why you're getting this error. Most likely, your problem is with type mismatch. You are storing a char(8) in one table and a char(9) in another.

    Note: If you're really storing numbers, use NUMBER data type.

    Code:
    SQL> desc job
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     JOB_NUMBER                                NOT NULL CHAR(8)
     DATE_CREATED                                       DATE
     DEPT_ID                                            CHAR(9)
     POSITION_ID                                        CHAR(9)
    
    SQL> desc position
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     POSITION_ID                               NOT NULL CHAR(8)
     DESCRIPTION                                        VARCHAR2(50)
     DUTIES                                             VARCHAR2(50)
     SALARY                                             CHAR(15)
     LEVEL_NO                                           CHAR(15)
    
    SQL> insert into position values (123, 'dude', 'all', 123, 15);
    
    1 row created.
    
    SQL> insert into job values (1, sysdate, 1, 123);
    insert into job values (1, sysdate, 1, 123)
    *
    ERROR at line 1:
    ORA-02291: integrity constraint (SYSTEM.FKPOSITION_ID) violated - parent key
    not found
    
    
    SQL> select '|' || position_id || '|' from position;
    
    '|'||POSIT
    ----------
    |123     |
    
    SQL> alter table job disable constraint position_id;
    alter table job disable constraint position_id
    *
    ERROR at line 1:
    ORA-02431: cannot disable constraint (POSITION_ID) - no such constraint
    
    
    SQL> alter table job disable constraint fkposition_id;
    
    Table altered.
    
    SQL> insert into job values (1, sysdate, 1, 123);
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select '|' || position_id || '|' from job;
    
    '|'||POSITI
    -----------
    |123      |
    notice position_id in job is 9 chars, in position is 8 chars.
    Jeff Hunter

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