ORA-02291 integrity constraint violated solution and commands

The Oracle error ORA-02291 is triggered when a foreign key constraint is violated. Specifically, it occurs when an attempt is made to insert or update a row in a child table that references a value in the parent table that does not exist.

Here is a breakdown of the error and solutions:

Error Explanation.

ORA-02291: Integrity constraint (constraint_name) violated - parent key not found.

This means the foreign key you're trying to insert or update references a non-existent value in the parent table.

Common Causes:

Inserting a value in a foreign key column that doesn't exist in the referenced (parent) table.

Updating a foreign key column with a value not present in the parent table.

Deleting a row from the parent table that is still referenced by a row in the child table (without cascading).

Solutions:

1. Ensure Parent Key Exists

Make sure the value you're trying to insert or update in the foreign key column exists in the parent table.

Example:

INSERT INTO child_table (fk_column, other_columns)

VALUES (parent_key_value, 'other_value');

Before this, ensure that parent_key_value exists in the parent table:

SELECT * FROM parent_table WHERE pk_column = parent_key_value;

If the value doesn't exist, insert it into the parent table:

INSERT INTO parent_table (pk_column, other_columns)

VALUES (parent_key_value, 'other_value');

2. Use ON DELETE CASCADE or ON DELETE SET NULL

If the issue arises from trying to delete a parent record that is still referenced by the child table, you can use cascading behavior to handle this scenario.

ON DELETE CASCADE: Automatically deletes the child records when the parent is deleted.

ON DELETE SET NULL: Sets the foreign key in the child table to NULL when the parent record is deleted.

Example of foreign key with ON DELETE CASCADE:

ALTER TABLE child_table
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (fk_column)
REFERENCES parent_table (pk_column)
ON DELETE CASCADE;

3. Check Foreign Key Constraint Definition

If you're troubleshooting existing tables, you might want to check the constraint itself.

Example:

SELECT constraint_name, constraint_type, table_name

FROM user_constraints

WHERE constraint_name = 'YOUR_CONSTRAINT_NAME';

4. Disable Foreign Key Constraints (not recommended for long-term)

You can temporarily disable the foreign key constraint if needed, but this is generally not recommended unless you are sure about the data's integrity.

Disable constraint:

ALTER TABLE child_table
DISABLE CONSTRAINT fk_constraint_name;

Enable constraint:

ALTER TABLE child_table
ENABLE CONSTRAINT fk_constraint_name;

5. Correct the Data in the Child Table

If the foreign key column in the child table contains invalid values (i.e., values not present in the parent table), you can update or delete those rows.

Find invalid rows:

SELECT * 
FROM child_table c
WHERE NOT EXISTS (SELECT 1 FROM parent_table p WHERE p.pk_column = c.fk_column);

Delete invalid rows:

DELETE FROM child_table
WHERE fk_column = 'invalid_value';
Update invalid values:

UPDATE child_table
SET fk_column = 'valid_value'
WHERE fk_column = 'invalid_value';

 

Summary:

To resolve ORA-02291, you need to ensure that every foreign key value in the child table has a corresponding primary key in the parent table, or modify the relationship with options like ON DELETE CASCADE.