#Oracle: ORA-02298: cannot validate "key" – parent keys not found

When trying to add a foreign key to a table sometimes the error ORA-02298 is thrown.

Let’s have two tables a MyTable and a MyTableHistory connected by a historyid column from MyTable that points to the id column from MyTableHistory

Try then to add add foreign key like:

ALTER TABLE MyTableHistory add CONSTRAINT MyKey foreign key (default_id) references MyTable;

If we are in the case of the ORA-02298 error the following message will be given by Oracle:

Error report -
ORA-02298: cannot validate (MyKey) - parent keys not found
02298. 00000 - "cannot validate (%s.%s) - parent keys not found"
*Cause:    an alter table validating constraint failed because the table has child records.
*Action:   Obvious

Funny how someone marked the solution as “Obvious” 🙂 Not so obvious because I had to look around some time to figure out what was the problem.

This error means that you have ids in your MyTableHistory table that are not present in the MyTable table. The foreign key can therefore not be applied.

This can be checked by running a query like:

SELECT * FROM MyTableHistory WHERE id NOT IN (SELECT MyTable.historyid FROM MyTable);

If the above query returns some rows it means that at some point we deleted (cleaned) some rows from the MyTable without cleaninf also the refered entries from MyTableHistory.

Do do the proper clean-up I had to execute:

DELETE FROM MyTableHistory WHERE id NOT IN (SELECT MyTable.historyid FROM MyTable);

After the clean-up is done we then ca execute again:

ALTER TABLE MyTableHistory add CONSTRAINT MyKey foreign key (default_id) references MyTable;

Success !