SQL Error: ORA-02291: "integrity constraint (%s.%s) violated - parent key not found" issue

[Patricia Andersen]

Hi guys,

Im stuck working with my tables trying to add some values to three of the tables but im getting this error:

SQL Error: ORA-02291: "integrity constraint (%s.%s) violated - parent key not found" (V07PATTR.KONTOÄGARE_KNR_FK)
*Cause: A foreign key value has no matching primary key value.
*Action: Delete the foreign key or add a matching primary key.
commited.

I understand what this error means so that is not the problem, i just cannot find anything wrong within my tables, everything seems to be in order . So these are the tables i first created

CREATE TABLE BANKKUND
(
PNR VARCHAR2(11) not null,
FNAMN VARCHAR2(25) not null,
ENAMN VARCHAR2(25) not null,
PASSWD VARCHAR2(6) not null,
constraint BANKKUND_pnr_pk primary key(PNR));

CREATE TABLE KONTOTYP
(
KTNR NUMBER(6) not null,
KTNAMN VARCHAR2(20) not null,
RÄNTA NUMBER(5,2) not null,
constraint KONTOTYP_ktnr_pk primary key(KTNR));

CREATE TABLE KONTO(
KNR NUMBER(8) not null,
KTNR NUMBER(6) not null,
REGDATUM DATE not null,
SALDO NUMBER(10,2),
constraint KONTO_knr_pk primary key(KNR),
constraint KONTO_ktnr_fk foreign key(KTNR) references KONTOTYP(KTNR));

CREATE TABLE KONTOÄGARE(
RADNR NUMBER(9) not null,
PNR VARCHAR2(11) not null,
KNR NUMBER(8) not null,
constraint KONTOÄGARE_radnr_pk primary key(RADNR),
constraint KONTOÄGARE_pnr_fk foreign key(PNR) references BANKKUND(PNR),
constraint KONTOÄGARE_knr_fk foreign key(KNR) references KONTO(KNR));

CREATE TABLE UTTAG(
RADNR NUMBER(9) not null,
PNR VARCHAR2(11) not null,
KNR NUMBER(8) not null,
BELOPP NUMBER(10,2),
DATUM DATE not null,
constraint UTTAG_radnr_pk primary key(RADNR),
constraint UTTAG_knr_fk foreign key(KNR) references KONTO(KNR));

CREATE TABLE INSÄTTNING(
RADNR NUMBER(9) not null,
PNR VARCHAR2(11) not null,
KNR NUMBER(8) not null,
BELOPP NUMBER(10,2),
DATUM DATE not null,
constraint INSÄTTNING_radnr_pk primary key(RADNR),
constraint INSÄTTNING_knr_fk foreign key(KNR) references KONTO(KNR));

CREATE TABLE ÖVERFÖRING(
RADNR NUMBER(9) not null,
PNR VARCHAR2(11) not null,
FRÅN_KNR NUMBER(8) not null,
TILL_KNR NUMBER(8) not null,
BELOPP NUMBER(10,2),
DATUM DATE not null,
constraint ÖVERFÖRING_radnr_pk primary key(RADNR),
constraint ÖVERFÖRING_FRÅN_fk foreign key(FRÅN_KNR) references KONTO(KNR),
constraint ÖVERFÖRING_till_fk foreign key(TILL_KNR) references KONTO(KNR))


and these are the values im trying to add to my tables kontotyp, konto and kontoägare'

Create sequence radnr_seq
Start with 1
Increment by 1;

INSERT INTO kontotyp(ktnr,ktnamn,ränta)
VALUES(1,'bondkonto',3,4);
INSERT INTO kontotyp(ktnr,ktnamn,ränta)
VALUES(2,'potatiskonto',4,4);
INSERT INTO kontotyp(ktnr,ktnamn,ränta)
VALUES(3,'griskonto',2,4);
COMMIT;
INSERT INTO konto(knr,ktnr,regdatum,saldo)
VALUES(123,1,SYSDATE - 321,0);
INSERT INTO konto(knr,ktnr,regdatum,saldo)
VALUES(5899,2,SYSDATE - 2546,0);
INSERT INTO konto(knr,ktnr,regdatum,saldo)
VALUES(5587,3,SYSDATE - 10,0);
INSERT INTO konto(knr,ktnr,regdatum,saldo)
VALUES(8896,1,SYSDATE - 45,0);
COMMIT;
INSERT INTO kontoägare(radnr,pnr,knr)
VALUES(radnr_seq.NEXTVAL,'540126-1111',123);
INSERT INTO kontoägare(radnr,pnr,knr)
VALUES(radnr_seq.NEXTVAL,'691124-4478',123);
INSERT INTO kontoägare(radnr,pnr,knr)
VALUES(radnr_seq.NEXTVAL,'540126-1111',5899);
INSERT INTO kontoägare(radnr,pnr,knr)
VALUES(radnr_seq.NEXTVAL,'691124-4478',8896);
COMMIT;

Im sorry if i made a mistake with the code, i usually post in this forum questions connected to java but never asked for SQL so i have no idea how to present the code in here. Anyhow i hope that someone can see my mistake because ive tried for 3 days now and i just cannot understand what is wrong.

Thanks in advance

[Stefan Evans]

From your error message, the constraint that is failing is: V07PATTR.KONTOÄGARE_KNR_FK
That points to being the Table: Kontoagare, knr column which references konto.knr

I presume this error comes when running an insert into kontoagare?

INSERT INTO kontoägare(radnr,pnr,knr) 
VALUES(radnr_seq.NEXTVAL,'540126-1111',123); 

Looking at your insert statements, you do appear to have created parent records appropriately.
For instance the knr value of 123 in the above statement was inserted:

INSERT INTO konto(knr,ktnr,regdatum,saldo) 
VALUES(123,1,SYSDATE - 321,0); 

Is it actually there?
If you select * from konto where knr=123, do you get that record back?

The other foreign key reference is the pnr field which references the Bankkund table.
You haven't shown any statements to populate the Bankkund table. Does the value '540126-1111' appear in that table?

[Patricia Andersen]

Stefan Evans wrote:From your error message, the constraint that is failing is: V07PATTR.KONTOÄGARE_KNR_FK
That points to being the Table: Kontoagare, knr column which references konto.knr

I presume this error comes when running an insert into kontoagare?

INSERT INTO kontoägare(radnr,pnr,knr) 
VALUES(radnr_seq.NEXTVAL,'540126-1111',123); 

thanks for replying. Yes it is

Looking at your insert statements, you do appear to have created parent records appropriately.
For instance the knr value of 123 in the above statement was inserted:

INSERT INTO konto(knr,ktnr,regdatum,saldo) 
VALUES(123,1,SYSDATE - 321,0); 

Is it actually there?
If you select * from konto where knr=123, do you get that record back?

No its not..thats the problem. Nothing gets inserted and i just get that error message which i find very odd

The other foreign key reference is the pnr field which references the Bankkund table.
You haven't shown any statements to populate the Bankkund table. Does the value '540126-1111' appear in that table?
there are no values added in the table whatsoever..thats why i am sooo confused. No values and primary key error.... ive been googling for days and went through my tables at least 50 times... cannot figure out the problem

[Stefan Evans]

Ok, so to insert a value into the kontoagare table, you need an entry in the konto table first.
That is not there, and thus explains the error message you posted.

Why is that value not there?
Presumably trying to insert the record into the konto gives you a similar message, complaining about the ktrn column?

so taking a further step back, are there any records in the kontotyp table?
If not, what happens when you execute just THAT insert statement?

My suggestion:
Execute those statements you have listed one by one. The first time you get an error message, stop and fix that one, because none of the others after it will work.

[Patricia Andersen]

Stefan Evans wrote:Ok, so to insert a value into the kontoagare table, you need an entry in the konto table first.
That is not there, and thus explains the error message you posted.

Why is that value not there?
Presumably trying to insert the record into the konto gives you a similar message, complaining about the ktrn column?

so taking a further step back, are there any records in the kontotyp table?
If not, what happens when you execute just THAT insert statement?

My suggestion:
Execute those statements you have listed one by one. The first time you get an error message, stop and fix that one, because none of the others after it will work.

Hi Stefan,

Im sorry i didnt reply, i had some issues yesterday with my network. SO anyway i managed to solve it, and to be honest i dont understand how it got solved since i just deleted everything and started from the beginning and suddenly it worked. I was thinking oracle had some problem or maybe i did everything in a different order (but i dont think so)
Anyway thanks a million for your efforts to help me.

[Dave Tolls]

Just so you know:

INSERT INTO kontotyp(ktnr,ktnamn,ränta) 
VALUES(1,'bondkonto',3,4); 

This was your problem.
Count the column names, then count the number of values supplied.