ORA-02291: integrity constraint violated - parent key not found
ORA-02291: integrity constraint (LAB_A.ORDERS_CUST_FK) violated - parent key not foundThe 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
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
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;Table created.
Table created.
1 row created.
1 row created.
Commit complete.INSERT INTO orders VALUES (1001, 3, 500);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/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;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.INSERT INTO customers VALUES (3, 'Park');
COMMIT;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.
INSERT INTO orders VALUES (1001, 3, 500);
COMMIT;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
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;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.INSERT INTO emp SELECT emp_id, name, dept_id FROM emp_old;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/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); EMP_ID NAME DEPT_ID
---------- ------------------------------ ----------
103 Park 30
1 row selected.INSERT INTO dept VALUES (30, 'Support');
COMMIT;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.
INSERT INTO emp SELECT emp_id, name, dept_id FROM emp_old;
COMMIT;
SELECT COUNT(*) AS emp_cnt FROM emp;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
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;Table created.
Table created.
1 row created.
1 row created.
Commit complete.INSERT INTO item VALUES (1, 'raw ', 'Steel plate');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/SELECT '[' || type_code || ']' AS stored FROM item_type ORDER BY type_code;
SELECT '[' || UPPER(TRIM('raw ')) || ']' AS input_fixed FROM dual;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.
INSERT INTO item VALUES (1, UPPER(TRIM('raw ')), 'Steel plate');
COMMIT;
SELECT item_id, type_code, item_name FROM item;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