sql - Integrity constraint violated - Parent key not found

Question

I am creating two tables each having one primary key column and another column that is used to link both tables using a foreign key.

create table t1(a number not null primary key,
                b number);
                
create table t2 ( c number ,
                  d number not null primary key);
                  
alter table t1 add foreign key (b) references t2(d);         
alter table t2 add foreign key (c) references t1(a);

Now when I try to insert values in any one of the table I get the error as below

ORA-02291: integrity constraint (SQL_KVQVOPFDDGLIGJGJSPOQZZIPN.SYS_C0049615414) violated - parent key not found ORA-06512: at "SYS.DBMS_SQL", line 1721

How can I insert the data on both columns of table t1 at once?

question from:https://stackoverflow.com/questions/65868840/integrity-constraint-violated-parent-key-not-found

Answer 1

Constraints should be created as deferrable initially deferred, otherwise it won't work because one table references another and vice versa. In Oracle database versions which didn't support deferred constraints, you should have dropped foreign key constraints, insert rows (paying attention that foreign keys won't be violated) and then reinforce those constraints.

So:

SQL> create table t1(a number not null primary key,
  2                  b number);

Table created.

SQL> create table t2 ( c number ,
  2                    d number not null primary key);

Table created.

SQL> alter table t1 add constraint fk12 foreign key (b)
  2  references t2(d) deferrable initially deferred;

Table altered.

SQL> alter table t2 add constraint fk21 foreign key (c)
  2  references t1(a) deferrable initially deferred;

Table altered.

SQL>

Testing: referential integrity is checked when you COMMIT:

SQL> insert into t1 (a, b) values (1, 2);

1 row created.

SQL> insert into t2 (c, d) values (1, 2);

1 row created.

SQL> commit;

Commit complete.

SQL>

If you enter invalid combination (values that violate referential integrity), you won't be stopped at the moment on insert but COMMIT (as I've already said):

SQL> insert into t1 (a, b) values (5, 6);

1 row created.

SQL> insert into t2 (c, d) values (9, 9);

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SCOTT.FK12) violated - parent key not found


SQL>