Ошибка ora 02291 integrity constraint

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.

PC running slow?

Improve the speed of your computer today by downloading this software — it will fix your PC problems.

If you have an error integrity constraint in Oracle on your system, I hope this guide will help you.

ORA-02291 is usually followed by “Integrity message constraint – parent not found”. This means that you tried to reference this table using the primary key, but the posts you specified did not match the primary key type.

[“Business Unit”: “Code”: “BU059”, “Label”: “IBM Software without GST”, “Product”: “Code”: “SSVSEF”, “Label”: “IBM InfoSphere DataStage”, ” Component” :””,”ARM Category”:[],”Platform”:[“code”:”PF025″,”label”:”Platform”,”Independent”],”Version”:”7.5.1″, “Issue “:””,”Industry”:”code”:”LOB10″,”label”:”Data & AI”]

APAR Status

Error Description

Oracle Enterprise considers some constraint violations to be fatalErrors - Most restrictions for both are reported as an integrity error.sqlcode-1. There are several that fall outside the scope:ORA-02290: limits will bethis is the test condition(string.The string)hurtreason: inserted values ​​don't do itsatisfy the given check constraint.Action: N   insert which values ​​violateforced it.ORA-02291: integrity constraint (string-string)wounded foundReason: Guardian Key of the foreign Key value is the sameno primary key value.Action: remove the Australian key or add one that suits you.primary key.Restrictionora-02292: lifetime values ​​(string.string)wounded - foundReason: abstinence of the child. Attempting to remove the value "Mom", "Important" or "Dad".which dependency had on a foreign key.Action: Remove dependencies from duplicate content then parent first orDisable restriction.They must be created to be in the SQL list.oraUpsert code. which, c, according to experts, are more likely to be sent to the failure servicewhich cause a fatal error for encoding.

Local Solution

Issue Overview

************************************************** ******* * **** IN A RELATIONSHIP:all S **** *********************custom platforms 7.5.1******************************************************* ******************************DESCRIPTION OF THE PROBLEM:The original patch that was deployed in Aprilal has a problem that is notpossible to perform.number of blocks'******************************************************* ******************************RECOMMENDATION:use the current patch number. Conclusion
We******************************************************* ******************************

Problem: It's Impossible To Reproduce The Look Of This Art. However, A New Patch Is Coming Out.The Last Code That Solved The Problem On The Client's Site.

Workaround

Comments

Information-apar

The

Correct Information

Applicable Components

The following example shows a bug, and there are a few fixes we can handle living in Oracle:

Error

-- Create
SQL> main table create cart testprimary(primary key id, name varchar2(10));
The table has been created.
-- Table Foregin create SQL> table
Create testforeign(f_id f_name number, varchar2(10), number, foreign key(p_id) p_id recommendation testprimary(id) );
the table has been created.

insert

— Data in your two tables
Insert sql> basic test parameters (1, ‘RAM’);
1 short period created.
SQL> testprimary to values(2,’RAM2′);
Insert 1 Creates scratches.
Pastesql> in Testprimary(3,’RAM3′);
Feed value 1 created.

happened

— error:
SQL> remove from where id testprimary is 1 testprimary;
remove from where=detection 1
*
Internet Integrity Discipline, ERROR 1:
ora-02292: (TEST.SYS_C008202) broken – child record
found

Reason
If we try to successfully delete the data from the maintables, we will first remove the mention table (child table)
When creating foreign keys, owners can specify an ON DELETE clause to indicate why rows behind the parent table are being deleted.
ON DELETE If cascading: a row from the parent table is frequently deleted, everything from the child table row related to this short period will be deleted.
ON DELETE SET NULL: and when the row of the parent row is deleted, all rows from the reference to table 1 to the row of this external content key are set to NULL.

Option 1: Correct information about table search restrictions in data

1. Find foreign table key name and column name from invalid constraint name:

col for multiple owner table names a15
Collar for a15
column name column associated with a15
primary owner pass for a10
main constraint name col a15
select for a.constraint_type,a.owner,a.table_name,b.column_name,a.r_owner "PrimaryOwner",a.All_constraints r_constraint_name "primaryconstraintname" from all_cons_columns a where
A b.CONSTRAINT_NAME = b.CONSTRAINT_NAME and a.constraint_name is OWNER 'sys_c008202';

c PrimaryOwn table_name column_name PrimaryConstrai- ————– ————— ————— ——————-R —— TEST FOREIGN TEST P_ID TEST SYS_C0082012. The best primary key of the table has now been found.
Note. Use a longer query exit constraint name and enter the enforcement information key.

col for table name a15
Branded collar for working with a15
column name column for a15
main owner pass with a10
Constraint name col is primary for a15
select a.constraint_type,a.owner,a.table_name,b.column_name,a.r_owner "PrimaryOwner",a."PrimaryConstraintName", r_constraint_name created by a, all_constraints all_cons_columns b where
A.CONSTRAINT_NAME means b.CONSTRAINT_NAME and a.constraint_name 'SYS_C008201';

C = TABLE_NAME OWNER COLUMN_NAME PrimaryOwn PrimaryConstrai- —- ———– —- ————— —— ——— TEST ——p PRIMARY IDENTIFICATION TEST3. Now you can get any of our existing rows of numbers in the reverse child that you want to delete in the main table using IN clause query.

-- I'm only giving you an example, you can also use it to delete data.
select * from TESTFOREIGN where p_id is selected (when id comes from id testprimary where = 2:1;)


option You can disableinclude or data with restrictions and operations performed

Improve the speed of your computer today by downloading this software — it will fix your PC problems.