Ошибка violated parent key not found - ErrorsMaster.ru - большая энциклопедия ошибок и их решений

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.

Источник

Learn the cause and how to resolve the ORA-02291 error message in Oracle.

Description

When you encounter an ORA-02291 error, the following error message will appear:

ORA-02291: integrity constraint <constraint name> violated — parent key not found

Cause

You tried to reference a table using a unique or primary key, but the columns that you listed did not match the primary key, or a primary key does not exist for this table.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

This error commonly occurs when you have a parent-child relationship established between two tables through a foreign key. You then have tried to insert a value into the child table, but the corresponding value does not exist in the parent table.

To correct this problem, you need to insert the value into the parent table first and then you can insert the corresponding value into the child table.

For example, if you had created the following foreign key (parent-child relationship).

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier (supplier_id)
);

Then you try inserting into the products table as follows:

INSERT INTO products
(product_id, supplier_id)
VALUES (1001, 5000);

You would receive the following error message:

Since the supplier_id value of 5000 does not yet exist in the supplier table, you need to first insert a record into the supplier table as follows:

INSERT INTO supplier
(supplier_id, supplier_name, contact_name)
VALUES (5000, 'Microsoft', 'Bill Gates');

Then you can insert into the products table:

INSERT INTO products
(product_id, supplier_id)
VALUES (1001, 5000);

Источник

General

Cloud Infrastructure

Whether you’re building something giant, or just aren’t quite sure where to start, come in here for broad discussions about all things Cloud!
Foundational cloud building blocks, including compute instances, storage and backup, virtual networking and security lists, and operating system — all your general cloud discussions.
Site-to-site VPN, FastConnect, WAF, Public DNS, Public IP Addressing, E-mail Delivery, and more.
Working with Containers, Kubernetes clusters (OKE), Kubernetes operators, the registry, artifacts, Serverless Functions, GraalVM, Verrazzano, Cluster API, Service Mesh, API Gateway, and related technologies.
Users, groups, policies, federation, compartments, authentication.
Cloud Guard, Security Advisor, Security Zones, Scanning, Vault, Bastion, Compliance & Audit, application performance monitoring, logging and analytics, events, operational insights, management agent, and related technologies.
AWS? Azure? GCP? OCI? Running your workloads across multiple vendor’s cloud environments.
Cost analysis, cost management, budgets, rewards programs, account & tenancy management, organizations & licenses
Running prepared Oracle and third-party applications

Data Management & Integration

All things Oracle Database, incuding Autonomous, DB Systems, Exadata, Data Safe, Multilingual Engine, Graph, Relational Duality, and more.

221.4k Posts

1.1m Comments
Free Oracle Database from release 23c onward.
Develop server-side Java applications that take advantage of the scalability and performance of Oracle Database.
All things MySQL and MySQL HeatWave, on Oracle Cloud or anywhere else it runs.
Flexible, non-relational database that can accommodate a wide variety of data models
The forum for 21c and prior versions of XE.

8.0k Posts

30.9k Comments
All about relational database languages, queries, and extensions.

185.1k Posts

1.0m Comments
SQL Developer, SQL Developer Data Modeler, SQLcl

21.7k Posts

74.7k Comments
A family of embedded key-value database libraries providing scalable high-performance data management services to applications.

6.7k Posts

22.0k Comments
All things specific to Oracle GoldenGate: GoldenGate for Oracle, GoldenGate for Non-Oracle, GoldenGate for BigData, GoldenGate Foundation Suites, GoldenGate Stream Analytics and OCI GoldenGate

4.9k Posts

19.0k Comments
All things specific to GG Free: Installation, licensing, any pipeline creation using the GG Free UI, relationship GG Free UI to MSA UI, etc.
All things about the Oracle SQL Developer Command Line (SQLcl)
Create, browse and edit logical, relational, physical, multi-dimensional, and data type models with a powerful graphical tool.

4.0k Posts

10.5k Comments
Discussions about the web version of SQL Developer.
Storing and managing structured data in your Oracle database

3.2k Posts

10.2k Comments

Development

APEX is Oracle’s strategic low-code application platform used by customers across every industry and geography.

131.3k Posts

474.8k Comments
Data Science, Data Labeling, models and model training, data science & machine learning development
Wide-ranging discussion on all things about developing software and implementing solutions on Oracle Cloud, including development environments.
In-depth discussion about using a your favorite frameworks and languages to build your apps on and with Oracle Cloud
Automating the development and delivery of your applications.

65.2k Posts

134.9k Comments
OCI API, CLI & SDK, plus automations like Ansible and Terraform
Create web and mobile apps directly from your browser in a visual environment.
General discussion of open source software, including Oracle’s open source projects.
Discussions about the Oracle Data Provider for .NET (ODP.NET)

7.8k Posts

20.9k Comments
Tools for connecting to and managing an Oracle Database or Oracle Autonomous Database within VS Code
Tools for connecting to and managing an Oracle Database or Oracle Autonomous Database within Visual Studio
Oracle Forms and Reports. Oracle Forms remains an widely used application development technology for PL/SQL based applications.

71.3k Posts

253.1k Comments

Java

The standard in community-driven enterprise software developed using the Java Community Process.

168.8k Posts

441.0k Comments
Accessing your databases from a Java client application using JDBC.

32.7k Posts

103.6k Comments
Develop highly functional, reliable, and portable applications for today’s most powerful embedded systems

8.1k Posts

24.2k Comments
22.1k Posts

74.7k Comments
Your favorite Java IDEs and Frameworks to help you create and deploy your Java applications.

129.0k Posts

408.0k Comments
Oracle’s implementation of the JVM

13.8k Posts

40.8k Comments
Writing applications in the Java programming language

86.3k Posts

466.1k Comments
Create and deploy portable code for desktop and server enbironments

94.2k Posts

274.7k Comments
Cryptography, public key infrastructure, secure communication, authentication, access control, and more.

13.8k Posts

37.3k Comments
Java User Groups (JUGs) are volunteer organizations that strive to distribute Java-related knowledge around the world. They provide a meeting place for Java users to get information, share resources and solutions, increase networking, expand Java Technology expertise, and above all, drink beer, eat pizza and have fun. The JUG Community is the meeting point for JUGs, helping promote the expansion of the worldwide Java Community
Just getting started? We’re here to help!

65.0k Posts

400.9k Comments

Communities

This is a category dedicated the community and collaboration amongst developer communities
If you’ve built something you are proud of, come share it with the community — for feedback or for fun!

Data and Analytics

Big Data Service & Big Data Appliance, Data Catalog, Data Integration, Data Flow
OCI AI Language, Speech, Vision, Anomaly Detection Services; Oracle Digital Assistant
Media Flow & Media Streams
A scalable and secure Oracle Cloud service that provides a full set of capabilities to explore and perform collaborative analytics.

Learning & Certification

Get hands-on with Oracle technologies at your own pace.

Oracle Applications

Deliver insurance solutions and digital customer experiences that build your customers’ confidence and loyalty with an insurance management system.
Formerly Oracle Policy Automation, decision automation software products for modeling and deploying business rules within the enterprise.
An integrated set of business applications for automating customer relationship management (CRM), enterprise resource planning (ERP) and supply chain management (SCM) processes.

13.3k Posts

27.7k Comments
A hybrid search-analytical database that organizes complex and varied data from disparate source systems into a flexible data model.
A full suite of integrated applications that can address your business needs for Human Capital Management (HCM) and Enterprise Resource Planning (ERP).

10.8k Posts

23.2k Comments
Customer relationship management system used to help companies connect and manage sales, marketing, and customer support.
Keeping track of your assets. Includes Oracle Content Management, formerly known as Oracle Content and Experience.

On-Premises Infrastructure

133.0k Posts

408.5k Comments
Application Development Software

35.9k Posts

104.7k Comments
102.9k Posts

367.1k Comments
17.6k Posts

49.9k Comments
8.1k Posts

16.5k Comments
63.5k Posts

165.9k Comments
102.4k Posts

238.6k Comments
41.0k Posts

114.9k Comments

International Languages

Local community discussions in the Chinese language.

2.3k Posts

15.9k Comments
Local community discussions in the German language.
Local community discussions in the Japanese language.
Local community discussions in the Portuguese language.

Источник

ORA-02291

The pleasure of Oracle software is the ease through which information can communicate across multiple tables in a database. Beyond having the ability to cleanly join tables and merge parameters, a number of devices in the software permit the access to and referencing of data from multiple tables, with unique features that allow you to create statements that can render formerly complex database issues with relatively little trouble.

Still, no user is perfect and no database can predict all of the potential errors that can arise during everyday use. In the realm of manipulating data across multiple data tables, a common error that you can encounter is the ORA-02291.

The Problem

ORA-02291 is typically accompanied with the message, “integrity constraint <constraint name> violated – parent key not found”. This means that you attempted to execute a reference to a certain table using a primary key. However, in the process of doing so, the columns that you specified failed to match the primary key. The error can also be triggered when referencing a primary key that does not exist for the table in question.

Before moving on, we should note a few things about primary keys. A primary key is a field or combination of fields that can distinctly denote a record. It can be established in either an ALTER TABLE or CREATE TABLE statement. A given table can only have one primary key, and none of the fields that populate the primary key can hold a null value. A primary key cannot exceed thirty-two columns.

Now that we have an understanding of primary keys, we can address the error at hand. Often, the error will arise when there is a parent-child relationship between two tables via a foreign key. A foreign key is a method to state that values in one particular table must exist in another. Typically the referenced table is a parent table, while the child table is where the foreign key emanates from. A primary key in a parent table will, most of the time, be referenced by a foreign key in the child table.

The ORA-02291 will be triggered when you attempt to insert a value into the child table (with the foreign key), but the value does not exist in the corresponding parent table. This violates the integrity of the referential relationship, prompting Oracle to issue an error message.

The Solution

In order to remedy this error, you will need to insert the value that you attempted to place in the child table into the parent table first. Once inserted as a parent row, you can go back and insert the value into the child table.

An Example

Let’s say that you first attempted to build the parent-child key relationship:
CREATE TABLE employees ( employee_id numeric (20) not null, employee_name varchar2(75) not null, supervisor_name varchar2(75), CONSTRAINT employee_pk PRIMARY KEY (employee_id) );
CREATE TABLE departments ( department_id numeric (20) not null, employee_id numeric (20) >not null, CONSTRAINT fk_employee FOREIGN KEY (employee_id) REFERENCES employee (employee_id) );
From there, you attempt to place the following in the departments table:
INSERT INTO departments (department_id, employee_id) VALUES (250, 600) ;
You will receive an “ORA-02291: integrity constraint violated” error. Since the employee_id value of 600 does not already occur in the employees table, you will have to go back and insert the following into the employees table:
INSERT INTO employees (employees_id, employees_name, supervisor_name) VALUES (600);
You can then return to the departments table and finish the key relationship:
INSERT INTO departments (department_id, employee_id) VALUES (250, 600);
Looking forward

Working with multiple sets of data tables can seem daunting, and it can be easy to get the references mixed up. Luckily, Oracle alleviates a great deal of stress associated with working in multiple tables at once. Remaining aware of how you are cross-referencing information from table to table can provide a solid foundation to avoiding an error like the ORA-02291. Still, because this problem requires a little bit of background knowledge and coding to solve, it would be advised to speak with a licensed Oracle software consultant if you find yourself continually having issues addressing this error.

Источник