ORA-02291: integrity constraint violated - parent key not found

ORA-02291: integrity constraint (LAB_A.ORDERS_CUST_FK) violated - parent key not found

The foreign key value you inserted does not exist in the parent table.

Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-14

ORA-02291 means a foreign key check failed: the value in the child column (for example orders.cust_id = 3) has no matching row in the parent table.

The message names the constraint, e.g. LAB_A.ORDERS_CUST_FK. Look it up in user_constraints to find the parent table, then check whether the value really exists there. The comparison is exact, so 'raw ' and 'RAW' are different values.

Causes and fixes

  1. The parent row does not exist yet
  2. A data migration copies child rows whose parent is missing
  3. The code value differs in case or trailing spaces

1The parent row does not exist yet

customers has IDs 1 and 2. Inserting an order for cust_id = 3 failed with ORDERS_CUST_FK. Joining user_constraints to itself showed the constraint points at CUSTOMERS_PK on CUSTOMERS, and COUNT(*) for customer 3 was 0.

After inserting customer 3 and committing, the same order insert succeeded (1 row created).

Setup
Setuprun as lab_a
CREATE TABLE customers (cust_id NUMBER CONSTRAINT customers_pk PRIMARY KEY, name VARCHAR2(30));
CREATE TABLE orders (order_id NUMBER PRIMARY KEY, cust_id NUMBER CONSTRAINT orders_cust_fk REFERENCES customers, amount NUMBER);
INSERT INTO customers VALUES (1, 'Kim');
INSERT INTO customers VALUES (2, 'Lee');
COMMIT;
OUTPUT
Table created.


Table created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
INSERT INTO orders VALUES (1001, 3, 500);
OUTPUT
INSERT INTO orders VALUES (1001, 3, 500)
*
ERROR at line 1:
ORA-02291: integrity constraint (LAB_A.ORDERS_CUST_FK) violated - parent key not found
Help: https://docs.oracle.com/error-help/db/ora-02291/
Checkrun as lab_a
COLUMN constraint_name FORMAT A16
COLUMN table_name FORMAT A10
COLUMN r_constraint_name FORMAT A16
COLUMN parent_table FORMAT A12
SELECT c.constraint_name, c.table_name, c.r_constraint_name, p.table_name AS parent_table
  FROM user_constraints c JOIN user_constraints p ON p.constraint_name = c.r_constraint_name
 WHERE c.constraint_name = 'ORDERS_CUST_FK';
SELECT COUNT(*) AS cust_3 FROM customers WHERE cust_id = 3;
OUTPUT
CONSTRAINT_NAME  TABLE_NAME R_CONSTRAINT_NAM PARENT_TABLE
---------------- ---------- ---------------- ------------
ORDERS_CUST_FK   ORDERS     CUSTOMERS_PK     CUSTOMERS

1 row selected.


    CUST_3
----------
         0

1 row selected.
Fixrun as lab_a
INSERT INTO customers VALUES (3, 'Park');
COMMIT;
OUTPUT
1 row created.


Commit complete.

Insert parents before children in the same program. If the parent is created by another process, check that it has committed first.

After the fixrun as lab_a
INSERT INTO orders VALUES (1001, 3, 500);
COMMIT;
OUTPUT
1 row created.


Commit complete.

2A data migration copies child rows whose parent is missing

INSERT INTO emp SELECT ... FROM emp_old copies 4 employees, but one of them points at department 30, which is not in dept. The whole statement failed, so none of the 4 rows were inserted.

A NOT EXISTS query found the orphan: 103 Park 30. After adding department 30, the same INSERT ... SELECT created 4 rows.

Setup
Setuprun as lab_a
CREATE TABLE dept (dept_id NUMBER CONSTRAINT dept_pk PRIMARY KEY, dname VARCHAR2(20));
CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, name VARCHAR2(20), dept_id NUMBER CONSTRAINT emp_dept_fk REFERENCES dept);
INSERT INTO dept VALUES (10, 'Sales');
INSERT INTO dept VALUES (20, 'IT');
CREATE TABLE emp_old (emp_id NUMBER, name VARCHAR2(20), dept_id NUMBER);
INSERT INTO emp_old VALUES (101, 'Kim', 10);
INSERT INTO emp_old VALUES (102, 'Lee', 20);
INSERT INTO emp_old VALUES (103, 'Park', 30);
INSERT INTO emp_old VALUES (104, 'Choi', 10);
COMMIT;
OUTPUT
Table created.


Table created.


1 row created.


1 row created.


Table created.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
INSERT INTO emp SELECT emp_id, name, dept_id FROM emp_old;
OUTPUT
INSERT INTO emp SELECT emp_id, name, dept_id FROM emp_old
*
ERROR at line 1:
ORA-02291: integrity constraint (LAB_A.EMP_DEPT_FK) violated - parent key not found
Help: https://docs.oracle.com/error-help/db/ora-02291/
Checkrun as lab_a
SELECT o.emp_id, o.name, o.dept_id
  FROM emp_old o
 WHERE o.dept_id IS NOT NULL
   AND NOT EXISTS (SELECT 1 FROM dept d WHERE d.dept_id = o.dept_id);
OUTPUT
    EMP_ID NAME                              DEPT_ID
---------- ------------------------------ ----------
       103 Park                                   30

1 row selected.
Fixrun as lab_a
INSERT INTO dept VALUES (30, 'Support');
COMMIT;
OUTPUT
1 row created.


Commit complete.

Run the NOT EXISTS check before a bulk load. Whether to add the missing parent or leave the row out is a data decision; this lab added the parent.

After the fixrun as lab_a
INSERT INTO emp SELECT emp_id, name, dept_id FROM emp_old;
COMMIT;
SELECT COUNT(*) AS emp_cnt FROM emp;
OUTPUT
4 rows created.


Commit complete.


   EMP_CNT
----------
         4

1 row selected.

3The code value differs in case or trailing spaces

item_type stores [RAW] and [FG]. The insert used 'raw ' (lower case with a trailing space), which matches neither, so it failed with ITEM_TYPE_FK even though a "raw" type exists.

Normalizing the input with UPPER(TRIM('raw ')) gives [RAW], and the insert succeeded.

Setup
Setuprun as lab_a
CREATE TABLE item_type (type_code VARCHAR2(10) CONSTRAINT item_type_pk PRIMARY KEY, type_name VARCHAR2(20));
CREATE TABLE item (item_id NUMBER PRIMARY KEY, type_code VARCHAR2(10) CONSTRAINT item_type_fk REFERENCES item_type, item_name VARCHAR2(20));
INSERT INTO item_type VALUES ('RAW', 'Raw material');
INSERT INTO item_type VALUES ('FG', 'Finished goods');
COMMIT;
OUTPUT
Table created.


Table created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
INSERT INTO item VALUES (1, 'raw ', 'Steel plate');
OUTPUT
INSERT INTO item VALUES (1, 'raw ', 'Steel plate')
*
ERROR at line 1:
ORA-02291: integrity constraint (LAB_A.ITEM_TYPE_FK) violated - parent key not found
Help: https://docs.oracle.com/error-help/db/ora-02291/
Checkrun as lab_a
SELECT '[' || type_code || ']' AS stored FROM item_type ORDER BY type_code;
SELECT '[' || UPPER(TRIM('raw ')) || ']' AS input_fixed FROM dual;
OUTPUT
STORED
------------
[FG]
[RAW]

2 rows selected.


INPUT
-----
[RAW]

1 row selected.

Common when codes come from Excel uploads or screen input. Wrap the values in [ ] as above to see hidden spaces.

After the fixrun as lab_a
INSERT INTO item VALUES (1, UPPER(TRIM('raw ')), 'Steel plate');
COMMIT;
SELECT item_id, type_code, item_name FROM item;
OUTPUT
1 row created.


Commit complete.


   ITEM_ID TYPE_CODE  ITEM_NAME
---------- ---------- --------------------
         1 RAW        Steel plate

1 row selected.

FAQ

How do I find the parent table of a foreign key constraint in Oracle?

Query user_constraints for the constraint name in the message and join r_constraint_name back to user_constraints. Here ORDERS_CUST_FK pointed at CUSTOMERS_PK on CUSTOMERS.

How do I find which rows cause ORA-02291 in an INSERT ... SELECT?

Select the source rows with NOT EXISTS (SELECT 1 FROM parent WHERE parent.key = source.key). Those are the orphans.

Why ORA-02291 when the parent value exists?

Check case and spaces. 'raw ' does not match 'RAW'. Normalize the input, e.g. UPPER(TRIM(...)).

Did any rows get inserted when ORA-02291 occurred?

No. The failing statement is rolled back as a whole. In this lab the INSERT ... SELECT inserted nothing, and the retry created all 4 rows.

How we verify

Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.

lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free

Related errors