A foreign key value has no matching primary key

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

A foreign key value has no matching primary key

Author

Topic

AngusMQ
Starting Member

2 Posts

Posted - 2014-09-15 : 09:02:56

Hi, I'm currently using Oracle and receiving this error:

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

When inserting this value:

INSERT INTO ATHLETE VALUES ('A001','TOM','HANKS','03-FEB-90','USA','C001');

With Tables:

CREATE TABLE ATHLETE(
ATHLETEID CHAR(4),
ATHLETEFIRSTNAME VARCHAR2(20),
ATHLETELASTNAME VARCHAR(20),
ATHLETEDOB DATE,
REPCOUNTRY VARCHAR2(12),
COACHID CHAR(4),
CONSTRAINT ATHLETE_PK PRIMARY KEY(ATHLETEID),
CONSTRAINT ATHLETE_FK1 FOREIGN KEY(COACHID) REFERENCES COACH(COACHID));

CREATE TABLE COACH(
COACHID CHAR (4),
COACHFIRSTNAME VARCHAR2(20),
COACHLASTNAME VARCHAR(20),
PAYPERHOUR NUMBER(5,2),
CONSTRAINT COACH_PK PRIMARY KEY (COACHID));

CREATE TABLE EVENTSCHEDULE(
EVENTID CHAR(4),
EVENTNAME VARCHAR2(20),
EVENTDATE VARCHAR2(20),
CONSTRAINT EVENTSCHEDULE_PK PRIMARY KEY(EVENTID));

CREATE TABLE RESULTS(
EVENTID CHAR(4),
ATHLETEID CHAR(4),
RANK NUMBER(1),
CONSTRAINT RESULTS_PK PRIMARY KEY(EVENTID,ATHLETEID),
CONSTRAINT RESULTS_FK1 FOREIGN KEY(EVENTID) REFERENCES EVENTSCHEDULE(EVENTID),
CONSTRAINT RESULTS_FK2 FOREIGN KEY (ATHLETEID) REFERENCES ATHLETE(ATHLETEID));

I think the error is to do with the Athletes table (primary/foreign key)

How would I fix this in order to insert values without error? Thanks in advance!

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-09-15 : 09:30:19

You can do a left join from the table with the foreign key to the table with the missing primary key where the missing primary key column is null. This will give you a result set that contains the records that have missing primary keys. you will then need to insert the primary key and values for other required fields into the table that has missing primary keys.

AngusMQ
Starting Member

2 Posts

Posted - 2014-09-15 : 12:06:33

Hi, thanks for your quick reply, sorry I'm still a little confused with your solution, is there a way I can edit the Athlete table as there is AthleteID and CoachID in that table and I want AthleteID as Primary key and CoachID as a foreign key?

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-09-15 : 13:10:22

-- these coaches will need to be inserted into : this is assuming there are any
SELECT C.COACHID
FROM COACH C
LEFT JOIN ATHLETE A ON C.COACHID = A.COACHID
WHERE A.COACHID IS NULL

INSERT INTO COACH(COACHID ,COACHFIRSTNAME ,COACHLASTNAME ,PAYPERHOUR)
VALUES (<COACHID From Above> ,'COACHFIRSTNAME' ,'COACHLASTNAME' 0.00)

if you are not missing any existing coaches, then you are just missing the coach being used in your current insert

you could do the following an substitute the appropriate values
INSERT INTO COACH(COACHID ,COACHFIRSTNAME ,COACHLASTNAME ,PAYPERHOUR)
VALUES ('A001' ,'COACHisMissing' ,'COACHLASTNAME' 0.00)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources