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
Re: ORA-02291: integrity constraint (S3034595.FKPOSITION_ID) violated - parent key no
Quote:
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.