Ошибка oracle 2291

I am creating a database that is trying to access values from a foreign key. I have created two following tables

CREATE TABLE Component(
    ComponentID varchar2(9) PRIMARY KEY
    , TypeID varchar2(9) REFERENCES TypeComponent(TypeComponentID)
)

INSERT INTO Component VALUES(192359823,785404309)
INSERT INTO Component VALUES(192359347,785404574)
INSERT INTO Component VALUES(192359467,785404769)
INSERT INTO Component VALUES(192359845,785404867)
INSERT INTO Component VALUES(192359303,785404201)
INSERT INTO Component VALUES(192359942,785404675)


CREATE TABLE TypeComponent (
    TypeComponentID varchar2(9) PRIMARY KEY
    , Type_Description varchar2(30) CONSTRAINT Type_Description 
        CHECK(Type_Description IN('Strap', 'Buckle', 'Stud')) NOT NULL
)

INSERT INTO TypeComponent VALUES(785404309, 'Strap')
INSERT INTO TypeComponent VALUES(785404574, 'Stud')
INSERT INTO TypeComponent VALUES(785404769, 'Buckle')
INSERT INTO TypeComponent VALUES(785404867, 'Strap')
INSERT INTO TypeComponent VALUES(785404201, 'Buckle')
INSERT INTO TypeComponent VALUES(785404675, 'Stud')

These are the two tables. Component and TypeComponent. Component is the parent entity to TypeComponent, and I am trying to run the following INSERT statement:

INSERT INTO Component VALUES(192359823,785404309)

but it is giving me the error

This is the session that I have so far in Oracle SQL dev

ORA-02291: integrity constraint violated – parent key not found error occurs when a foreign key value in the child table does not have a matching primary key value in the parent table, as stated by a foreign key constraint. You try to insert a row into a child table that does not have a corresponding parent row. The column value you supplied for the child table did not match the primary key in the parent table.

You try to insert or update a row in the child table. The value in the child table’s reference column should be available in the parent table’s primary key column. If the primary key column does not have a value, the row cannot be inserted or updated in the child table. The parent key’s integrity constraint was violated.

The value of the child table’s foreign key column should be the same as the value of the parent table’s primary key column. If the value does not exist in the parent table, an error ORA-02291: integrity constraint violated – parent key not found will be thrown.

Cause

A foreign key value has no matching primary key value.

Action

Delete the foreign key or add a matching primary key.

The Problem

When two tables in a parent-child relationship are created, a referential foreign key constraint is generated and enforces the relationship between the two tables. The value of the foreign key column in the child table is decided by the value of the primary key column in the parent table.

A value that is not available in the parent table cannot be inserted or updated in the child table. If you try to insert or update a value in the foreign key column of a child table, Oracle will throw the parent key integrity constraint violation error.

create table dept
(
 id numeric(5) primary key,
 name varchar2(100)
);

create table employee
(
  id numeric(5) primary key,
  name varchar2(100),
  deptid numeric(5) references dept(id)
);

insert into employee values(1,'Yawin',1);

Error

Error starting at line : 17 in command -
insert into employee values(1,'Yawin',1)
Error report -
ORA-02291: integrity constraint (HR.SYS_C0012551) violated - parent key not found

Solution 1

If the integrity constraint is violated, knowing the parent and child tables involved in the foreign key relationship is important. The parent and child table names, as well as the column names, may be retrieved using the integrity constraint name. The parent table, child table, parent column name, child column name, and integrity constraint name will be shown in the following sql query.

select r.constraint_name Foreign_key_constraint,
    p.owner parent_owner, p.table_name parent_table, pc.column_name parent_column_name, 
    r.owner child_owner, r.table_name child_table, rc.column_name child_colum_name
from user_constraints p
join user_cons_columns pc on p.owner=pc.owner 
        and p.table_name=pc.table_name and p.constraint_name = pc.constraint_name
        and p.constraint_type='P'
join user_constraints r on p.constraint_name=r.r_constraint_name and r.constraint_type='R'
join user_cons_columns rc on r.owner=rc.owner 
        and r.table_name=rc.table_name and r.constraint_name = rc.constraint_name
        and r.constraint_type='R'
where r.constraint_name='SYS_C0012551' 
order by p.owner, p.table_name, pc.column_name, rc.position;

Output

Foreign_key_constraint | parent_owner |parent_table | parent_column_name |child_owner | child_table | child_colum_name
SYS_C0012548	HR	DEPT	ID	HR	EMPLOYEE	DEPTID

Solution 2

The value you are trying to put into the child table reference column does not exist in the parent table. You must first enter the value that you intended to insert into the child table into the parent table. After inserting the value as a parent row, you may go back and enter it into the child table.

insert into dept values (1, 'sales');

insert into employee values(1,'Yawin',1)

Output

1 row inserted.

1 row inserted.

Solution 3

You are attempting to insert a row into a child table for which the primary key does not exist in the parent table. Before you enter a child, make sure you have a parent key for that child in the parent table.

insert into employee values(1,'Yawin',1)

insert into employee values(1,'Yawin',1)
Error report -
ORA-02291: integrity constraint (HR.SYS_C0012551) violated - parent key not found


insert into employee values(1,'Yawin',100) -- the value 100 exist in the dept table.

I am having a hard time with ORA-02291: integrity constraint, I have a table (TF_MODE) which is linked to another table (PRODUCT) such that you can have a row in TF_MODE for various products. The problem is I can insert 10 rows perfectly in the TF_MODE table but the moment I try to insert the 11th row (i.e when the ID = 11) I get the «ORA-02291: integrity constraint» even though the foreign key does exist for the 11th entry in the PRODUCT table, the strange thing is the query is successful if I change the ID to let’s say 12/14, but for some ID’s it won’t work e.g 999,1000 ; there is no fixed pattern for which it won’t work, so basically the problem is specifically with the few ID values and not with the fact that for that foreign key the value don’t exist in the parent table (actually the value does exist).

I am using Oracle 11g.

Any idea what could be the reason behind this strange behavior ?

Thanks !

Update:

There are no triggers, the only check constraints are ‘IS NOT NULL’.

TFMODE:

CREATE TABLE "DESIGN"."TFMODE"
(
"ID"                NUMBER(38,0) NOT NULL ENABLE,
"F_PRODUCT"         NUMBER(38,0) NOT NULL ENABLE,
"SYS.CURRENT_I[MA]" NUMBER(10,3) NOT NULL ENABLE,
"LAST_MODIFIED_DATE" DATE NOT NULL ENABLE,
"VOLTAGE" NUMBER(10,3) NOT NULL ENABLE,
CONSTRAINT "TFMODE_PK" PRIMARY KEY ("ID") USING 
INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(
INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 
0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT 
CELL_FLASH_CACHE DEFAULT) TABLESPACE "DESIGN" ENABLE,
CONSTRAINT "TFMODE_PRODUCT_FK1" FOREIGN KEY ("ID") REFERENCES 
 "DESIGN"."PRODUCT" ("ID") ENABLE
) 

 SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
NOCOMPRESS LOGGING STORAGE
(
INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT 
CELL_FLASH_CACHE DEFAULT
)
TABLESPACE "DESIGN" ;

PRODUCT:

CREATE TABLE "DESIGN"."PRODUCT"
(
"ID"           NUMBER(*,0) NOT NULL ENABLE,
"MANUFACTURER" VARCHAR2(100 BYTE),
...
)
TABLESPACE "DESIGN" ;

The PRODUCT table has lot’s of columns and have been omitted for brevity.

Insert Statement (from java application):

INSERT INTO TFMODE VALUES  ( 11, 953, 2.27, to_date('2014-11-18','YYYY-MM-DD'), 3.8) 

I tried editing things from sql developer but same results, here’s the screen shots:

ID=11 doesn’t work

ID=12 works