oracle tutorial webinars

ORA-02292

The incredible assortment of data tables that a user can work with in Oracle can be a blessing and, at times, a curse. Some of the most frustrating Oracle errors occur due to problems with volumes of data being pulled and recorded across multiple table sets, then at various times being manipulated. This action effectively allows a single keystroke to alter information across countless tables.

The ORA-02292 error is a product of this kind of situation. While it may seem a bit overwhelming compared to relatively simpler errors to overcome, such as syntax mistakes, there are a few different approaches that a user can take resolve the error and prevent it from recurring.

The Problem

The ORA-02292 error indicates that an “integrity constraint <constraint name> was violated – child record found”. What this indicates is that the user attempted to delete a record from a parent table (which is referenced by a foreign key), but a record in the child table exists. Before we continue, let us review what a parent-child relationship in Oracle really means.

The foreign key in Oracle provides a means to enforce referential integrity in a database. The table to be referenced is denoted as the “parent table”, which has its own primary key. This primary key matches with the foreign key in the “child table”. The foreign key and integrity constraints are designed for the purpose of maintaining data integrity, which is an adherence to the rules of certain variable levels as specified by a company and enforced by a systems administrator.

Examples of these variable levels are employee numbers or salaries, both of which a company will have specific rules to address The primary key in the parent table serves as the basis for which the levels are enforced.

Now that we have covered this information, let us turn back to the ORA-02292 error and how we can solve and prevent this error.

The Solution

In order to correct the ORA-02292 error, the user will need to update or delete the value into the child table first and subsequently delete the corresponding information in the parent table. Suppose the user created the following foreign key:

CREATE TABLE employer

( employer_id numeric(25) not null,
employer_name varchar2 (100) not null,
contact_name varchar2(100),
CONSTRAINT employer_pk PRIMARY KEY (employer_id)
);


CREATE TABLE employees

( employee_id numeric(25) not null,
employee_id varchar2 (25) not null,
contact_name varchar2(100),
CONSTRAINT employer_fk

FOREIGN KEY (employer_id)

REFERENCES employer (employer_id)
);

From there, the user tries to insert into the employees table the following:

INSERT INTO employer
(employer_id, employer_name, contact_name)
VALUES (525, ‘WALMART’, ‘SAM WALTON’);


INSERT INTO employees
(employee_id, employer_id)
VALUES (600, 525);

Suppose the user then attempted to delete the record from the employer table as follows:

DELETE from employer
WHERE employer_id = 525;

The user would then receive the following Oracle error message:

ORA-02292: integrity constraint (COLLECT.FK_EMPLOYER) violated – child record found

Because the employer_id value of 525 exists in the employees records, the user needs to first delete the record from the employees table:

DELETE from employees
WHERE employer_id = 525;

Then the user can delete from the employer table:

DELETE from employer
WHERE employer_id = 525;

Looking forward

Preventative measures can be taken to avoid an ORA-02292. A constraint can be created that looks like the following:

SQL> alter table emp
2 add (constraint job_fk foreign key (job_key)
3 references job (job_key)
4 on delete cascade);

From here out, when using INSERT or UPDATE for the job key column in the EMP table, the foreign key constraint will check to ensure that the job already exists in the JOB table.

Of course, this type of resolution is much more coding-intensive than would be seen with a syntax issue. If you find that you do not feel comfortable working within your database using this kind of coding on valuable referential tables, it would be advised to contact a licensed Oracle consultant for further information on the process.