I have a huge pl/sql stored procedure, where we make some deletions as long as insertions.
Procedure starts with the statement
EXECUTE IMMEDIATE 'SET CONSTRAINTS ALL DEFERRED'
And at the last commit statement I receive ORA-02292: integrity constraint AAA violated.
The questions is that I don’t know which statement exactly causes it, because I have both deletion from parent table (before child one) and insertions into child table before parent.
I tried to google it, but everywhere it’s said that 02292 happens when I try to delete only.
Could this error happen when I try to insert value in the child table but there is no this entry in the parent?
Also, what is the difference between 02292 and 02291?
asked Jun 15, 2012 at 15:58
2
ORA-02292 indicates that the error occurred because A) the constraint has no ON DELETE clause specified, and B) you deleted a row from the master table which had matching references in the child table. Your choices are to modify the constraint so have an ON DELETE CASCADE or to ensure that all child records are deleted before deleting from the master. My preference would be to add ON DELETE CASCADE but I suppose there could be reasons not to do so. See ORA-02292.
ORA-02291 is sort of the opposite of this. ORA-02291 will be raised if you attempt to insert a row into a child table, but the key field values on your new child row as specified in the constraint do not exist in the master table. See ORA-02291.
Anders
8,2079 gold badges55 silver badges87 bronze badges
answered Jun 15, 2012 at 18:41
If you want to disable the constraint from the name to solve ORA-02292.
-
Look for the table name bounded to that constraint
SELECT owner, table_name FROM dba_constraints WHERE constraint_name = '{CONSTRAINT_NAME}'; -
Disable constraint (this command should be executed by an admin user)
ALTER TABLE {TABLE_NAME} DISABLE constraint {CONSTRAINT_NAME} cascade;
answered Jan 27, 2017 at 11:00
Lorenzo LerateLorenzo Lerate
3,4722 gold badges25 silver badges25 bronze badges
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.
I have written a procedure that has as input the CUSTOMER_ID. Then the procedure deletes the corresponding customer from the table CUSTOMERS.
CREATE OR REPlACE PROCEDURE DELETE_CUSTOMER
(CUSTOMER_ID NUMBER) AS
TOT_CUSTOMERS NUMBER;
BEGIN
DELETE FROM CUSTOMERS
WHERE CUSTOMERS.CUSTOMER_ID = DELETE_CUSTOMER.CUSTOMER_ID;
TOT_CUSTOMERS := TOT_CUSTOMERS - 1;
END;
/
I have to execute the procedure to delete customer with id 1.
EXECUTE DELETE_CUSTOMER(01);
When I do this, I get an error
Error starting at line : 120 in command -
BEGIN DELETE_CUSTOMER(01); END;
Error report -
ORA-02292: integrity constraint (TUG81959.ORDERS_FK_CUSTOMERS) violated - child record found
ORA-06512: at "TUG81959.DELETE_CUSTOMER", line 5
ORA-06512: at line 1
02292. 00000 - "integrity constraint (%s.%s) violated - child record found"
*Cause: attempted to delete a parent key value that had a foreign
dependency.
*Action: delete dependencies first then parent or disable constraint.
I know this is because there is a foreign key for CUSTOMER_ID on the table ORDERS, which means the customer cannot be deleted because he has placed an order.
How do I write the code so that I can first delete the corresponding ORDER_DETAILS and then delete the corresponding ORDERS so that I can finally be able to delete a record from CUSTOMERS?
I tried rewriting the code but I am just lost now:
CREATE OR REPlACE PROCEDURE DELETE_CUSTOMER
(CUSTOMER_ID_IN NUMBER) AS
TOT_CUSTOMERS NUMBER;
CURSOR C1 IS
DELETE FROM ORDERS
WHERE ORDERS.ORDER_ID = CUSTOMER_ID.ORDER_ID;
CURSOR C2 IS
DELETE FROM ORDER_DETAILS
WHERE ORDER_DETAILS.ORDER_ID = CUSTOMER_ID.ORDER_ID;
CURSOR C3 IS
DELETE FROM CUSTOMERS
WHERE CUSTOMERS.CUSTOMER_ID = DELETE_CUSTOMER.CUSTOMER_ID;
BEGIN
OPEN C1;
OPEN C2;
OPEN C3;
IF C1%FOUND AND C2%FOUND AND C3%FOUND
THEN TOT_CUSTOMERS := TOT_CUSTOMERS - 1;
END IF;
CLOSE C1;
CLOSE C2;
CLOSE C3;
END;
/
Here are the tables for reference:
Debugging ORA-02292: integrity constraint (OWNER.CONSTRAINT) violated – child record found
Did you find this post useful? Does your organization need Oracle services? We can help.
Working with Oracle databases can be daunting to developers and analysts who lack a deep understanding of relational models and SQL. One excellent example of a mystifying pitfall is the dreaded ORA-02292: integrity constraint error. This error often occurs when users are attempting to DELETE rows in a well-normalized schema. For example’s sake, I’ll focus on Oracle Transportation Management (OTM), which is where I’ve spent much of my time over the last six months.
Given the T in OTM, configurators and analysts often focus on orders and shipments. After discovering the backdoor SQL Servlet or connecting via SQL Developer, new configurators and analysts may think they can simply delete rows from the ORDER_RELEASE or SHIPMENT table to remove unwanted data. This often goes something like this…
> DELETE FROM order_release
WHERE order_release_gid = 'CUSTOMER.ORDER_NUMBER'
Error starting at line 1 in command:
DELETE FROM order_release WHERE order_release_gid = 'CUSTOMER.ORDER_NUMBER'
Error report: SQL Error: ORA-02292: integrity constraint (GLOGOWNER.FK_SSUL_OR_GID) violated - child record found
02292. 00000 - "integrity constraint (%s.%s) violated - child record found"
*Cause: attempted to delete a parent key value that had a foreign dependency.
*Action: delete dependencies first then parent or disable constraint.
Oops. The database is telling us that there are records in a different table in the database that point to this record. If we delete this record, the broken reference will confuse the database. But how do we know where the reference is? There are nearly 2000 tables in OTM, so looking through them one-by-one isn’t an option…
At this point, most analysts will learn not to try direct SQL deletions for tables. If you ask, they’ll tell you it’s not possible to do outside of the OTM UI. While it’s true that the UI or application should be used for most common deletion tasks, this misunderstanding of database constraints and normalization can promulgate into broader misunderstandings of feasibility and relational models. So let’s back up and understand what actually went on in this ORA-2292 error above. How do we know where the dependencies on ORDER_RELEASE are?
Oracle databases contain a special table that stores all constraints in the database, conveniently named ALL_CONSTRAINTS. Looking at ALL_CONSTRAINTS, you’ll see that the first two fields are OWNER and CONSTRAINT_NAME. To find the constraint violated in ORA-2292 above, look for the following portion of the message – “integrity constraint (GLOGOWNER.FK_SSUL_OR_GID) violated.” In this error string, GLOGOWNER corresponds to the constraint owner, and FK_SSUL_OR_GID corresponds to the constraint name. So, where does this get us?
> SELECT owner, constraint_name, constraint_type, table_name, r_owner, r_constraint_name
FROM all_constraints
WHERE owner='GLOGOWNER'
AND constraint_name='FK_SSUL_OR_GID';
owner | constraint_name | constraint_type | table_name | r_owner | r_constraint_name
GLOGOWNER | FK_SSUL_OR_GID | R | S_SHIP_UNIT_LINE | GLOGOWNER | PK_ORDER_RELEASE
OK, but what does this mean? In a nutshell, the table S_SHIP_UNIT_LINE has a column that must match up with the PK_ORDER_RELEASE constraint…yes, another constraint to look up.
> SELECT owner, constraint_name, constraint_type, table_name, r_owner, r_constraint_name
FROM all_constraints
WHERE owner='GLOGOWNER'
AND constraint_name='PK_ORDER_RELEASE';
owner | constraint_name | constraint_type | table_name | r_owner | r_constraint_name
GLOGOWNER | PK_ORDER_RELEASE | P | ORDER_RELEASE | |
> SELECT * FROM all_cons_columns
WHERE owner='GLOGOWNER'
AND constraint_name = 'PK_ORDER_RELEASE';
owner | constraint_name | table_name | column_name | position
GLOGOWNER | PK_ORDER_RELEASE | ORDER_RELEASE | ORDER_RELEASE_GID | 1
This one isn’t so bad – PK constraints in OTM’s schema always map to the corresponding GID, or unique identifier for a record. So, to summarize the ORA-2292 error above, we can’t delete the record from ORDER_RELEASE because a record from S_SHIP_UNIT_LINE is tied to that ORDER_RELEASE_GID.
Now, if that makes this seem easy, stop yourself – it’s not. This is just one of many possible integrity constraints that an ORDER_RELEASE may have. Let’s try to summarize the above process into a single query. Given a constraint, how would we find the tables and columns that are tied together?
> SELECT ac.owner AS left_owner, ac.constraint_name AS left_name, ac.table_name AS left_table, acc.column_name AS left_column, acc.position AS left_position, acr.owner AS right_owner, acr.constraint_name AS right_name, acr.table_name AS right_table, accr.column_name AS right_column, accr.position AS right_position
FROM all_constraints ac
JOIN all_cons_columns acc ON ac.constraint_name=acc.constraint_name
JOIN all_constraints acr ON ac.r_constraint_name=acr.constraint_name
JOIN all_cons_columns accr ON acr.constraint_name=accr.constraint_name
WHERE ac.owner='GLOGOWNER'
AND ac.constraint_name='FK_SSUL_OR_GID';
LEFT_OWNER | LEFT_NAME | LEFT_TABLE | LEFT_COLUMN | LEFT_POSITION | RIGHT_OWNER | RIGHT_NAME | RIGHT_TABLE | RIGHT_COLUMN | RIGHT_POSITION
GLOGOWNER | FK_SSUL_OR_GID | S_SHIP_UNIT_LINE | ORDER_RELEASE_GID | 1 | GLOGOWNER | PK_ORDER_RELEASE | ORDER_RELEASE | ORDER_RELEASE_GID | 1
Great! While the query isn’t small, the only parts you should need to change are the two final WHERE constraints. The output is easy to understand – LEFT_TABLE.LEFT_COLUMN references RIGHT_TABLE.RIGHT_COLUMN.
For extra credit, how would we find every such dependency between a table and other tables? While this may seem much harder than the original query, we’ve already joined the relevant table.
> SELECT ac.owner AS left_owner, ac.constraint_name AS left_name, ac.table_name AS left_table, acc.column_name AS left_column, acc.position AS left_position, acr.owner AS right_owner, acr.constraint_name AS right_name, acr.table_name AS right_table, accr.column_name AS right_column, accr.position AS right_position FROM all_constraints ac JOIN all_cons_columns acc ON ac.constraint_name=acc.constraint_name JOIN all_constraints acr ON ac.r_constraint_name=acr.constraint_name JOIN all_cons_columns accr ON acr.constraint_name=accr.constraint_name WHERE acr.table_name='ORDER_RELEASE'; ... (37 rows returned)
There – 37 first-degree constraints on the ORDER_RELEASE table. Now, if only each of these 37 tables didn’t have their own integrity constraints…but more to come on this in a later post! In the meantime, a few things to remember or bookmark:
- While I’ve focused on the OTM example in this post, the processes in this post are applicable to any Oracle database.
- To learn more about the constraint and constraint column tables, read the documentation: ALL_CONSTRAINTS ; ALL_CONS_COLUMNS.
- These constraints can extend “recursively” through tables. For example, the S_SHIP_UNIT_LINE table depends on the PK_S_SHIP_UNIT_LINE table.
- FKs build a real, directed network! For real-world data models, these networks can be substantial and complex.
Did you find this post useful? Does your organization need Oracle services? We can help.
Share This Story, Choose Your Platform!
Top Sliding Bar
Recent Tweets
Newsletter
Sign-up to get the latest news and update information. Don’t worry, we won’t send spam!
When I try to delete something from parent table of an application, I get an integrity constraint violated error.
How can i found the referencing table name using query?
asked Jan 21, 2021 at 7:24
If you know the constraint name from the error ORA-02292 message you can find table name referensed to:
select table_name
from all_constraints c
where constraint_name = '<constrant name>'
answered Jan 21, 2021 at 7:36
2
You can use user_constraints table as follows:
select c.*
from user_constraints p
join user_constraints c on p.constraint_name = c.r_constrainst_name
where p.constraint_type = 'P'
and p.table_name = '<Your_Table_name>'
You can also use user_cons_columns in above query to get particular column name of the child table too.
answered Jan 21, 2021 at 7:33
PopeyePopeye
35.4k4 gold badges10 silver badges31 bronze badges