This is what I have:

create table Employee_T
(EmployeeID varchar2(10) NOT NULL,
EmployeeFirstName varchar2(10) NOT NULL,
EmployeeLastName varchar2(15) NOT NULL,
EmployeeAddress varchar2(20),
EmployeeCity varchar2(10),
EmployeeState char(2),
EmployeeZip number(5),
EmployeeDateHired date default sysdate,
EmployeeBirthDate date,
EmployeeSupervisorID varchar2(10),
Constraint employee_PK primary key (EmployeeID),
Constraint employee_FK foreign key (EmployeeSupervisorID)REFERENCES Employee_T(EmployeeID)
);

create table Skill_T
(SkillID varchar2(10) NOT NULL,
SkillDescription varchar2(20) NOT NULL,
Constraint skill_PK primary key (SkillID)
);

create table EmployeeSkills_T
(EmployeeID varchar2(10) NOT NULL,
SkillID varchar2(10) NOT NULL,
Constraint employeeskills_PK primary key (EmployeeID, SkillID),
Constraint employeeskills_FK1 foreign key (EmployeeID)REFERENCES Employee_T(EmployeeID),
Constraint employeeskills_FK2 foreign key (SkillID)REFERENCES Skill_T(SkillID)
);


INSERT INTO Employee_T (EmployeeID, EmployeeFirstName,EmployeeLastName, EmployeeAddress, EmployeeCity, EmployeeState, EmployeeZip, EmployeeDateHired, EmployeeBirthDate, EmployeeSupervisorID)
VALUES('123-44-345','Jim','Jason','2134 Hilltop Rd',NULL,'CA',NULL,'12/Jun/99',NULL,'454-56-768');
INSERT INTO Employee_T (EmployeeID, EmployeeFirstName,EmployeeLastName, EmployeeAddress, EmployeeCity, EmployeeState, EmployeeZip, EmployeeDateHired, EmployeeBirthDate, EmployeeSupervisorID)
VALUES('454-56-768','Robert','Lewis','17834 Deerfield Ln','Nashville','CA',NULL,'01/Jan/95',NULL,NULL);
INSERT INTO Employee_T (EmployeeID, EmployeeFirstName,EmployeeLastName, EmployeeAddress, EmployeeCity, EmployeeState, EmployeeZip, EmployeeDateHired, EmployeeBirthDate, EmployeeSupervisorID)
VALUES('454-56-324','Richard','Lewis','34 Washington St','Mansfield','MA',NULL,'01/Jan/99',NULL,'123-44-345');
INSERT INTO Employee_T (EmployeeID, EmployeeFirstName,EmployeeLastName, EmployeeAddress, EmployeeCity, EmployeeState, EmployeeZip, EmployeeDateHired, EmployeeBirthDate, EmployeeSupervisorID)
VALUES('454-56-377','Leon','Williams','55 Washington Bld','Alexandria','VA',NULL,'04/Jan/97',NULL,NULL);

INSERT INTO Skill_T (SkillID,SkillDescription)
VALUES('01','Java');
INSERT INTO Skill_T (SkillID,SkillDescription)
VALUES('02','C++');
INSERT INTO Skill_T (SkillID,SkillDescription)
VALUES('03','Perl');

INSERT INTO EmployeeSkills_T (EmployeeID,SkillID)
VALUES('123-44-345','01');
INSERT INTO EmployeeSkills_T (EmployeeID,SkillID)
VALUES('123-44-345','03');
INSERT INTO EmployeeSkills_T (EmployeeID,SkillID)
VALUES('454-56-768','01');

And when I run it, I get insertion errors like

"integrity constraint EMPLOYEE_FK 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."

It also gives the same error for EMPLOYEESKILLS_FK1 and EMPLOYEESKILLS_FK2.

I don't understand why it's saying that the foreign key value has no matching primary key value...Is my syntax wrong when I say what the foreign key references? Or am I missing something altogether?

Recommended Answers

All 4 Replies

The problem is supervisor id 454-56-768

YOu must insert that first
I have changed sequence of insert

INSERT INTO Employee_T (EmployeeID, EmployeeFirstName,EmployeeLastName, EmployeeAddress, EmployeeCity, EmployeeState, EmployeeZip, EmployeeDateHired, EmployeeBirthDate, EmployeeSupervisorID)
VALUES('454-56-768','Robert','Lewis','17834 Deerfield Ln','Nashville','CA',NULL,'01/Jan/95',NULL,NULL);

INSERT INTO Employee_T (EmployeeID, EmployeeFirstName,EmployeeLastName, EmployeeAddress, EmployeeCity, EmployeeState, EmployeeZip, EmployeeDateHired, EmployeeBirthDate, EmployeeSupervisorID)
VALUES('123-44-345','Jim','Jason','2134 Hilltop Rd',NULL,'CA',NULL,'12/Jun/99',NULL,'454-56-768');

INSERT INTO Employee_T (EmployeeID, EmployeeFirstName,EmployeeLastName, EmployeeAddress, EmployeeCity, EmployeeState, EmployeeZip, EmployeeDateHired, EmployeeBirthDate, EmployeeSupervisorID)
VALUES('454-56-324','Richard','Lewis','34 Washington St','Mansfield','MA',NULL,'01/Jan/99',NULL,'123-44-345');

INSERT INTO Employee_T (EmployeeID, EmployeeFirstName,EmployeeLastName, EmployeeAddress, EmployeeCity, EmployeeState, EmployeeZip, EmployeeDateHired, EmployeeBirthDate, EmployeeSupervisorID)
VALUES('454-56-377','Leon','Williams','55 Washington Bld','Alexandria','VA',NULL,'04/Jan/97',NULL,NULL);

Thank you so much, I really appreciate it

Could someone explain why the order of the insertion had to be changed?

The constraint

Constraint employee_FK foreign key (EmployeeSupervisorID)REFERENCES Employee_T(EmployeeID)

prevents you from adding a record unless EmployeeSupervisorID already exists. How, exactly, do you add the supervisor to the table? The constraint will prevent you from adding the supervisor until you add his/her supervisor first? Of course, it's 3:00 am and I may be missing something.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.