CODEWITHSUNDEEP
sqloracle-databaseparent
Zbarcea Christian
Zbarcea Christian

Reputation: 9548

Oracle sql ORA-02291 parent key not found

I have a copied table:

CREATE TABLE d_clients_copy As SELECT * FROM d_clients;

Only data structure and values are copied, constraints not.

So I am adding the Primary Key to the newly created data base:

ALTER TABLE d_clients_copy
add CONSTRAINT client_number_pk
    PRIMARY KEY (client_number);

Now I have a primary key column in the newly created table.

I have a table with events, and I'm trying to reference the client_number from the c_clients_copy table.

ALTER TABLE d_events_copy
add CONSTRAINT client_number_fk
    FOREIGN KEY (client_number)
    REFERENCES d_clients_copy (client_number);

And oracle gives the following error:

ORA-02298: cannot validate ... - parent keys not found.

Which one is the parent? I am so confused.

Note: each table contains data.

Upvotes: 0

Views: 6523

Answers (1)

David Aldridge
David Aldridge

Reputation: 52346

When you copied the two tables you possibly had changes occurring between the time that the selects were issued.

By default the second select will not be selecting data as of the same time as the first select because the default query isolation level for Oracle is READ COMMITTED.

To change this behaviour you have to modify the query isolation level for the session to SERIALIZABLE: http://docs.oracle.com/cd/B28359_01/server.111/b28318/consist.htm#CNCPT1320

Upvotes: 2

Related Questions