0

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?

3
Contributors
4
Replies
21
Views
3 Years
Discussion Span
Last Post by Reverend Jim
0

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);
0

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

0

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.

Edited by Reverend Jim

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.