Oracle Error ORA-02291

Error code

ORA-02291

Product

Oracle Database

Versions

Oracle Database 19c, 21c, 23ai and 26ai (same wording)

Category

Constraints & Integrity

Last reviewed

24 October 2026

What it means

ORA-02291 means an INSERT or UPDATE on a child table would store a foreign key value that has no matching primary or unique key in the parent table, so Oracle rejected the statement.

A foreign key says that every value in the child column must already exist as a primary or unique key in the parent table. Oracle's reference gives the cause in one line: a foreign key value has no matching primary key value. The message names the foreign key constraint (on the child table) as schema.name; it does not name the parent table or the value, so the first step is to look the constraint up in the data dictionary.

A NULL foreign key is not checked: a single-column foreign key that is NULL always passes, and Oracle's SQL Language Reference adds that a composite foreign key is satisfied when at least one of its columns is NULL. If the constraint is DEFERRABLE and deferred, the check happens at COMMIT instead of after each statement; if it fails then, the whole transaction is rolled back.

Common causes

• The child row is inserted before its parent, typically because a load or a test-data script runs tables in the wrong order.
• The foreign key value is wrong: a typo, an id from another environment, or a value that was never created.
• The parent row was deleted, or never committed by another session, before the child was written.
• An UPDATE changes a foreign key column to a value that has no parent.
• The data types or formats differ, for example codes with leading zeros or trailing spaces that do not match the parent key exactly.

Diagnose it

Read-only These queries only read the data dictionary (USER_, ALL_ and DBA_ views) and dynamic performance views (V$). They do not change data, privileges or settings, so they are safe to run first. DBA_ and V$ views need the matching privileges.

SELECT c.table_name AS child_table, cc.column_name AS child_column,
       p.owner AS parent_owner, p.table_name AS parent_table,
       p.constraint_name AS parent_key, c.deferrable, c.deferred
FROM all_constraints c
JOIN all_cons_columns cc
  ON cc.owner = c.owner AND cc.constraint_name = c.constraint_name
JOIN all_constraints p
  ON p.owner = c.r_owner AND p.constraint_name = c.r_constraint_name
WHERE c.owner = 'SHOP'
  AND c.constraint_name = 'ORDERS_CUSTOMER_FK'
ORDER BY cc.position;

SELECT *
FROM shop.customers
WHERE id = 42;  -- the foreign key value you tried to write

SELECT s.*
FROM shop.orders_staging s
WHERE s.customer_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM shop.customers c WHERE c.id = s.customer_id);

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

INSERT INTO shop.customers (id, email, name)
VALUES (42, 'sara@example.com', 'Sara Ahmed');

INSERT INTO shop.orders (id, customer_id, order_date)
VALUES (1001, 42, DATE '2026-10-24');

INSERT INTO shop.orders (id, customer_id, order_date)
SELECT s.id, s.customer_id, s.order_date
FROM shop.orders_staging s
WHERE EXISTS (SELECT 1 FROM shop.customers c WHERE c.id = s.customer_id);

ALTER TABLE shop.orders DROP CONSTRAINT orders_customer_fk;

ALTER TABLE shop.orders ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES shop.customers (id)
  DEFERRABLE INITIALLY IMMEDIATE;

Examples

An order for a customer that does not exist

Not reproduced here: no Oracle Database is installed for this site. The result shows the message format documented in Oracle's error reference, with this example's constraint name filled in (the tables are assumed to be in schema SHOP).

CREATE TABLE customers (
    id    NUMBER PRIMARY KEY,
    email VARCHAR2(200) NOT NULL
);

CREATE TABLE orders (
    id          NUMBER PRIMARY KEY,
    customer_id NUMBER CONSTRAINT orders_customer_fk REFERENCES customers (id),
    order_date  DATE
);

INSERT INTO orders (id, customer_id, order_date) VALUES (1001, 42, SYSDATE);

ORA-02291: integrity constraint (SHOP.ORDERS_CUSTOMER_FK) violated - parent key not found

Loading child rows before the parent table

Not reproduced here: no Oracle Database is installed for this site. The constraint name is illustrative: a foreign key declared without a name gets a system-generated SYS_C name, which differs on every database. The whole INSERT ... SELECT is rolled back, not just the rows without a parent.

-- the load script runs the child table first
INSERT INTO orders (id, customer_id, order_date)
SELECT id, customer_id, order_date FROM orders_staging;

-- the customers are only loaded afterwards
INSERT INTO customers (id, email)
SELECT id, email FROM customers_staging;

ORA-02291: integrity constraint (SHOP.SYS_C008456) violated - parent key not found

How to prevent it

Name foreign keys (CONSTRAINT orders_customer_fk) so the message tells you which relationship failed. Load and seed tables in dependency order: parents first, children last; when you delete, go the other way.

Check staging data with a NOT EXISTS query before the load, and keep the foreign key enabled in every environment so bad references are caught at the source. Handle ORA-02291 in application code as "the referenced item no longer exists".

Common questions

Learn more on CodeWithSQL

• Primary key vs foreign key
• SQL constraints explained

Official documentation

• ORA-02291 (Oracle Database Error Messages)
• Constraint clause: foreign keys and DEFERRABLE (Oracle SQL Language Reference 19c)
• SET CONSTRAINT[S] (Oracle SQL Language Reference 19c)
• ALL_CONSTRAINTS (Oracle Database Reference 19c)

Last technically reviewed 24 October 2026; examples and diagnostics run on Oracle Database Error Messages reference (26ai, 21c and 19c); not run locally. Messages and explanations follow Oracle's Database Error Messages reference; no Oracle Database was available, so the examples were not run locally.