ORA-02291 - "integrity constraint violated-parent key not found" - error with only few inserts

Question

New to sql here and cant find the answer to this problem...

create table Stranka (
 id_stranka integer not null, 
 ime varchar(50) not null,
 telst integer not null,
 tk_id_naslov integer
); 

alter table Stranka 
add (constraint pk_Stranka primary key (id_stranka)); 

create table Avto (
 id_avto integer not null,
 regst varchar(50) not null,
 stmotorja integer not null, 
 stpopravila integer not null,
 tk_id_stranka integer
);

alter table Avto 
add (constraint pk_Avto primary key (id_avto));


alter table Avto
add(constraint tk_avto_id_stranka foreign key (tk_id_stranka) references 
Avto(id_avto));

here are the inserts

insert into Avto values (1, 'LJ-123-12', 1123, 4, 1); 
insert into Avto values (2, 'LJ-A21-BP', 3039, 1, 2);
insert into Avto values (3, 'MB-H62-06', 1562, 2, 5);
insert into Avto values (4, 'LJ-AT2-19', 2021, 2, 4);
insert into Avto values (5, 'LJ-12S-23', 1784, 4, 6);

the question i have is why do i get the above listed error for inserts number 3 and 5 but not for number 1, 2 and 4? What am i missing? The inserts are basically the same. Any additional info i need to add let me know. Thanks

PS: the other TK(table Stranka) works fine and in table stranka we have 10 ids.

Answer 1

Look at your foreign key constraint:

alter table Avto
add(constraint tk_avto_id_stranka foreign key (tk_id_stranka) references 
Avto(id_avto));

You're putting a foreign key on table Avto to another column in table Avto.

Happens that the 2 columns involved in the foreign key are the first and last columns in your inserts. In inserts 1, 2, and 4, the id_avto and tk_id_stranka are the same.

insert into Avto values (1, 'LJ-123-12', 1123, 4, 1); 
insert into Avto values (2, 'LJ-A21-BP', 3039, 1, 2);
insert into Avto values (4, 'LJ-AT2-19', 2021, 2, 4);

Oracle is smart enough to see that these do not violate the constraint, since the row being inserted itself satisfies the constraint. Whereas on inserts 3 and 5, there is no row in the table that satisfies the foreign key constraint - no row in the table with id_avto 5 or 6.

insert into Avto values (3, 'MB-H62-06', 1562, 2, 5);
insert into Avto values (5, 'LJ-12S-23', 1784, 4, 6);

What you probably need to do is change your foreign key to point at the other table - given how you've named everything that seems like what you intended:

alter table Avto
add(constraint tk_avto_id_stranka foreign key (tk_id_stranka) references 
Stranka (id_stranka));