Why is parent key not found?

Question

When I insert data into the ASSIGNMENTS table it all works fine except for the HARDWARE and SOFTWARE values, which are coded exactly in the same way as the others. I just don't get it.

This works:

INSERT INTO ASSIGNMENTS (ASSIGNMENT_ID, PROJECT_ID, STAFF_ID, JOB_ID)
VALUES ('A0005','B0002','ST002','J0002');

But when I try to include values for HARDWARE or SOFTWARE, like this:

INSERT INTO ASSIGNMENTS (ASSIGNMENT_ID, PROJECT_ID, STAFF_ID, JOB_ID, HARDWARE_ID)
VALUES ('A0005','B0002','ST002','J0002','H0002');

I just get the following error: SQL Error: ORA-02291: integrity constraint (JAS1224.SYS_C0028418) 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.

Here is all the code, and all of the tables have been populated correctly (the hardware and software tables are coded in exactly the same format as the staff table):

CREATE TABLE PROJECT
(PROJECT_ID CHAR(5) NOT NULL,
PROJECT_NAME CHAR(20),
PROJECT_TYPE CHAR(20),
START_DATE DATE,
END_DATE DATE,
PRIMARY KEY (PROJECT_ID));

CREATE TABLE HARDWARE
(HARDWARE_ID CHAR(5) NOT NULL,
HARDWARE_NAME CHAR(20),
PRIMARY KEY (HARDWARE_ID));

CREATE TABLE SOFTWARE
(SOFTWARE_ID CHAR(5) NOT NULL,
SOFTWARE_NAME CHAR(20),
PRIMARY KEY (SOFTWARE_ID));

CREATE TABLE STAFF
(STAFF_ID CHAR(5) NOT NULL,
STAFF_NAME CHAR(20),
PRIMARY KEY (STAFF_ID));

CREATE TABLE JOB
(JOB_ID CHAR(5) NOT NULL,
JOB_TYPE CHAR(20),
JOB_GRADE CHAR(20),
PRIMARY KEY (JOB_ID));

CREATE TABLE ASSIGNMENTS
(ASSIGNMENT_ID CHAR(5) NOT NULL,
PROJECT_ID CHAR(5),
STAFF_ID CHAR(5),
JOB_ID CHAR(5),
HARDWARE_ID CHAR(5),
SOFTWARE_ID CHAR(5),
PRIMARY KEY (ASSIGNMENT_ID),
FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT(PROJECT_ID),
FOREIGN KEY (STAFF_ID) REFERENCES STAFF(STAFF_ID),
FOREIGN KEY (JOB_ID) REFERENCES JOB(JOB_ID),
FOREIGN KEY (HARDWARE_ID) REFERENCES HARDWARE(HARDWARE_ID),
FOREIGN KEY (SOFTWARE_ID) REFERENCES SOFTWARE(SOFTWARE_ID));

Do you, in fact, have a row in the HARDWARE table, where HARDWARE_ID = 'H0002'? Do you understand how primary keys and foreign keys referencing primary keys work? — mathguy
I do, and when I asked this question I was sure the data was already inserted and that everything was there - but I must have made a mistake and not had the rows populated like I was so sure I had. All the code was written and ready I just must have not executed it though I thought I did. — JadstaSeven

Gordon Linoff Gordon Linoff · Accepted Answer · 2016-12-10T14:08:57

The error message may be a bit cryptic at first, but it is pretty clear.

The only difference between the two INSERTs is for HARDWARE_ID. Hence 'H0002' is not a valid HARDWARE_ID. It is not in the HARDWARE table.

Why is parent key not found?

1 Answers