📋

Description

The ORA-02291 error in Oracle Database indicates a foreign key constraint violation. This occurs when attempting to insert or update a record in a child table with a foreign key value that does not exist in the parent table's primary key column.

💬

Error Message

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

🔍

Known Causes

3 known causes

⚠️

Missing Parent Record

The referenced primary key record does not exist in the parent table. 💻

⚠️

Incorrect Foreign Key Value

The foreign key value being inserted or updated is misspelled or incorrect. ⚙

⚠️

Data Import Issues

Data import processes may introduce inconsistencies if the parent table data is not imported first. 🌐

🛠️

Solutions

4 solutions available

1. Insert Parent Record First easy

Create the referenced parent row before the child

1

Identify missing parent key

-- Find which value doesn't exist in parent
-- If inserting order with customer_id = 999:
SELECT * FROM customers WHERE customer_id = 999;
-- If no rows, the parent doesn't exist

2

Insert parent then child

-- First insert parent:
INSERT INTO customers (customer_id, name)
VALUES (999, 'New Customer');

-- Then insert child:
INSERT INTO orders (order_id, customer_id, amount)
VALUES (1, 999, 100.00);

2. Check for Data Type Mismatch medium

Ensure FK value matches PK type exactly

1

Verify data types match

-- Check column types
SELECT table_name, column_name, data_type, data_length
FROM user_tab_columns
WHERE table_name IN ('CUSTOMERS', 'ORDERS')
AND column_name = 'CUSTOMER_ID';

2

Common issue: string vs number

-- If parent PK is NUMBER but you're inserting string:
INSERT INTO orders (customer_id) VALUES ('999');  -- Might fail

-- Use correct type:
INSERT INTO orders (customer_id) VALUES (999);    -- NUMBER

3. Handle Bulk Inserts medium

Order operations in data loads

1

Load parent tables first

-- In SQL*Loader or data pump:
-- 1. Load parent tables first
-- 2. Then load child tables

-- Or temporarily disable constraint:
ALTER TABLE orders DISABLE CONSTRAINT fk_customer;
-- Load data...
ALTER TABLE orders ENABLE CONSTRAINT fk_customer;

2

Find orphaned records after load

-- Find child records without parents:
SELECT * FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM customers c
  WHERE c.customer_id = o.customer_id
);

4. Use Deferrable Constraint advanced

Defer constraint check until commit

1

Create deferrable FK constraint

ALTER TABLE orders ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
DEFERRABLE INITIALLY DEFERRED;

2

Insert in any order, constraint checked at commit

-- Can insert child first:
INSERT INTO orders VALUES (1, 999, 100);
INSERT INTO customers VALUES (999, 'Customer');
COMMIT;  -- FK checked here

Oracle ORA-02291: Parent Key Missing