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,3079 gold badges56 silver badges89 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,5623 gold badges26 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 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,3079 gold badges56 silver badges89 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,5623 gold badges26 silver badges25 bronze badges
2 Answers
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
-
Here table_name mean parent table?
Jan 21, 2021 at 9:35
-
No, child table. Sorry, I made mistake in where clause, use constraint_name, not r_constraint_name column. When you got error ORA-02292 you can take constraint name from error message and put in the query.
Jan 21, 2021 at 12:11
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.5k4 gold badges10 silver badges31 bronze badges
Home » Oracle » Oracle Sql » Oracle delete cascade: ORA-02292 Integrity Constraints error
If you are deleting rows from a table that is referenced by the foreign key of another table, then you will get integrity constraints errors(ORA-02292). This can be avoided by using DELETE CASCADE in Oracle
Let’s take an example. In this, we will first create the sample tables, then insert rows and then try for the delete statement
CREATE TABLE "EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO"),
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "DEPT" ("DEPTNO") ENABLE
);
CREATE TABLE "DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
)
Now once the table is created, we insert a couple of sample rows to demonstrate the situation
SQL>
insert into DEPT values(10, 'ACCOUNTING', 'NEW YORK');
insert into dept values(20, 'RESEARCH', 'DALLAS');
insert into dept values(30, 'RESEARCH', 'DELHI');
insert into dept values(40, 'RESEARCH', 'MUMBAI');
insert into emp values( 7698, 'BLAKE', 'MANAGER', 7839, to_date('1-5-2007','dd-mm-yyyy'), 2850, null, 10 );
insert into emp values( 7782, 'CLARK', 'MANAGER', 7839, to_date('9-6-2008','dd-mm-yyyy'), 2450, null, 10 );
insert into emp values( 7788, 'SCOTT', 'ANALYST', 7566, to_date('9-6-2012','dd-mm-yyyy'), 3000, null, 20 );
insert into emp values( 7789, 'TPM', 'ANALYST', 7566, to_date('9-6-2017','dd-mm-yyyy'), 3000, null, null );
insert into emp values( 7560, 'T1OM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, 20 );
insert into emp values( 7790, 'TOM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, null );
commit;
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------
7698 BLAKE MANAGER 7839 01-MAY-07 2850 10
7782 CLARK MANAGER 7839 09-JUN-08 2450 10
7788 SCOTT ANALYST 7566 09-JUN-12 3000 20
7789 TPM ANALYST 7566 09-JUN-17 3000
7790 TOM ANALYST 7567 09-JUL-17 4000
4534 xyz 1000 20
4576 abc 1000
7560 T1OM ANALYST 7567 09-JUL-17 4000 20
SQL> delete from dept;
delete from dept
*
ERROR at line 1:
ORA-02292: integrity constraints FK_DEPTNO violated -child record found
ORA-02292 can be avoided if we specify on delete cascade option while creating the EMP table. Then when all the rows of the dept are deleted, all the records from EMP are also deleted. This is true to single row delete also, all the records pertaining to that are deleted from the EMP table also
CREATE TABLE "EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO"),
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "DEPT" ("DEPTNO") ENABLE
ON DELETE CASCADE
);
CREATE TABLE "DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
)
SQL> desc emp
Name Null? Type
----------------------------------------- -------- ----------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL>
SQL> desc dept
Name Null? Type
----------------------------------------- -------- ---------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL>insert into DEPT values(10, 'ACCOUNTING', 'NEW YORK');
insert into dept values(20, 'RESEARCH', 'DALLAS');
insert into dept values(30, 'RESEARCH', 'DELHI');
insert into dept values(40, 'RESEARCH', 'MUMBAI');
insert into emp values( 7698, 'BLAKE', 'MANAGER', 7839, to_date('1-5-2007','dd-mm-yyyy'), 2850, null, 10 );
insert into emp values( 7782, 'CLARK', 'MANAGER', 7839, to_date('9-6-2008','dd-mm-yyyy'), 2450, null, 10 );
insert into emp values( 7788, 'SCOTT', 'ANALYST', 7566, to_date('9-6-2012','dd-mm-yyyy'), 3000, null, 20 );
insert into emp values( 7789, 'TPM', 'ANALYST', 7566, to_date('9-6-2017','dd-mm-yyyy'), 3000, null, null );
insert into emp values( 7560, 'T1OM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, 20 );
insert into emp values( 7790, 'TOM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, null );
commit;
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- -------
7698 BLAKE MANAGER 7839 01-MAY-07 2850 10
7782 CLARK MANAGER 7839 09-JUN-08 2450 10
7788 SCOTT ANALYST 7566 09-JUN-12 3000 20
7789 TPM ANALYST 7566 09-JUN-17 3000
7790 TOM ANALYST 7567 09-JUL-17 4000
4534 xyz 1000 20
4576 abc 1000
7560 T1OM ANALYST 7567 09-JUL-17 4000 20
SQL> delete from dept;
4 rows deleted
SQL> commit;
SQL> Select count(*) from emp
0 rows
So all the records from emp are also deleted and we have not received any errors also which we are getting in the earlier example
Hope you like this post on oracle delete cascade
Related Articles
Update statement in oracle: We use the Update statement in oracle to modify the existing rows in the oracle table in the oracle database. An update can be executed in multiple ways
DROP TABLE ORACLE: Learn about drop table in Oracle, Drop table if exists in Oracle, drop multiple tables in one command, drop table cascade constraints
Oracle Create table: Tables are the basic unit of data storage in an Oracle Database. we cover how to use Oracle create table command to create a table with a foreign key /primary key
How to delete a row in oracle: Delete from the table in oracle is used to delete the rows. DELETE rows can be done using EXISTS/NOT EXISTS clause, table based on subquery
Oracle documentation on delete