This is the error I get when I insert the data:
INSERT INTO "44581114"."TEACHING_INFORMATION" (STAFFID, COURSEID, SEMESTER, YEAR)
VALUES ('11223344', 'ISYS224', 'S3', '2014')
error:
ORA-02291: integrity constraint (44581114.FK_TEACHING_RELATIONS_COURSE) violated - parent key not found
ORA-06512: at line 1
One error saving changes to table 44581114"."TEACHING_INFORMATION:
Row 1:
ORA-02291: integrity constraint (44581114.FK_TEACHING_RELATIONS_COURSE) violated - parent key not found
ORA-06512: at line 1
If it helps i have attached the DDL script here:
/*==============================================================*/
/* DBMS name: ORACLE Version 11g */
/* Created on: 11/10/2016 3:47:29 PM */
/*==============================================================*/
alter table ACADEMIC_REC
drop constraint FK_ACADEMIC_RELATIONS_STUDENT;
alter table ACADEMIC_REC
drop constraint FK_ACADEMIC_RELATIONS_COURSE;
alter table COURSE
drop constraint FK_COURSE_OFFERS_DEPARTME;
alter table ENROLS
drop constraint FK_ENROLS_ENROLS_STUDENT;
alter table ENROLS
drop constraint FK_ENROLS_ENROLS2_TUT_PRAC;
alter table HAS_PREREQUISITE
drop constraint FK_HAS_PRER_HAS_PRERE_COURSE;
alter table HAS_PREREQUISITE
drop constraint FK_HAS_PRER_HAS_PRERE_COURSE;
alter table STAFF
drop constraint FK_STAFF_RELATIONS_DEPARTME;
alter table TEACHING_INFORMATION
drop constraint FK_TEACHING_RELATIONS_STAFF;
alter table TEACHING_INFORMATION
drop constraint FK_TEACHING_RELATIONS_COURSE;
alter table TUT_PRAC
drop constraint FK_TUT_PRAC_HAS_COURSE;
alter table TUT_PRAC
drop constraint FK_TUT_PRAC_RELATIONS_STAFF;
drop table ACADEMIC_REC cascade constraints;
drop table COURSE cascade constraints;
drop table ENROLS cascade constraints;
drop table HAS_PREREQUISITE cascade constraints;
drop table STAFF cascade constraints;
drop table STUDENT cascade constraints;
drop table TEACHING_INFORMATION cascade constraints;
drop table TUT_PRAC cascade constraints;
/*==============================================================*/
/* Table: ACADEMIC_REC */
/*==============================================================*/
create table ACADEMIC_REC
(
STUID CHAR(8) not null,
COURSEID CHAR(8) not null,
STATUS VARCHAR2(5),
YEAR NUMBER(4),
SEMESTER CHAR(2),
GRADE VARCHAR2(2)
constraint CKC_GRADE_ACADEMIC check (GRADE is null or (GRADE in ('HD','D','CR','P','F'))),
constraint PK_ACADEMIC_REC primary key (STUID, COURSEID)
);
/*==============================================================*/
/* Table: COURSE */
/*==============================================================*/
create table COURSE
(
COURSEID CHAR(8) not null,
DEPTID CHAR(8) not null,
COURSENAME VARCHAR2(20),
TEXTBOOK VARCHAR2(20),
CREDITHOUR NUMBER(2),
MAX_ENROL NUMBER(4),
ACTUAL_ENROL NUMBER(4),
AVAILABILITY NUMBER(4),
COURSE_TIME CHAR(7),
constraint PK_COURSE primary key (COURSEID)
);
/*==============================================================*/
/* Table: DEPARTMENT */
/*==============================================================*/
create table DEPARTMENT
(
DEPTID CHAR(8) not null,
DEPTNAME VARCHAR2(20),
DEPTCONTACTNO NUMBER(10),
BUILDING VARCHAR2(5),
constraint PK_DEPARTMENT primary key (DEPTID)
);
/*==============================================================*/
/* Table: ENROLS */
/*==============================================================*/
create table ENROLS
(
STUID CHAR(8) not null,
CLASSID CHAR(8) not null,
constraint PK_ENROLS primary key (STUID, CLASSID)
);
/*==============================================================*/
/* Table: HAS_PREREQUISITE */
/*==============================================================*/
create table HAS_PREREQUISITE
(
COURSEID CHAR(8) not null,
PREREQUISITEID CHAR(8) not null,
constraint PK_HAS_PREREQUISITE primary key (COURSEID, PREREQUISITEID)
);
/*==============================================================*/
/* Table: STAFF */
/*==============================================================*/
create table STAFF
(
STAFFID CHAR(8) not null,
DEPARTMENTID CHAR(8) not null,
STAFFNAME VARCHAR2(50),
STAFFADDRESS VARCHAR2(70),
STAFFCONTACTNO NUMBER(10),
STAFFEMAIL VARCHAR2(50),
OFFICENO NUMBER(5),
ROLE VARCHAR2(10),
constraint PK_STAFF primary key (STAFFID)
);
/*==============================================================*/
/* Table: STUDENT */
/*==============================================================*/
create table STUDENT
(
STUID CHAR(8) not null,
DEGREE VARCHAR2(10),
MAJOR VARCHAR2(10),
STU_NAME VARCHAR2(50),
STU_ADDRESS VARCHAR2(70),
CONTACTNO NUMBER(10),
EMAIL VARCHAR2(50),
constraint PK_STUDENT primary key (STUID)
);
/*==============================================================*/
/* Table: TEACHING_INFORMATION */
/*==============================================================*/
create table TEACHING_INFORMATION
(
STAFFID CHAR(8) not null,
COURSEID CHAR(8) not null,
SEMESTER CHAR(2) not null,
YEAR NUMBER(4) not null,
constraint PK_TEACHING_INFORMATION primary key (STAFFID, COURSEID, SEMESTER, YEAR)
);
/*==============================================================*/
/* Table: TUT_PRAC */
/*==============================================================*/
create table TUT_PRAC
(
CLASSID CHAR(8) not null,
COURSEID CHAR(8) not null,
STAFFID CHAR(8) not null,
TYPE VARCHAR2(5),
DAY VARCHAR2(10),
TIME DATE,
ROOMID CHAR(10),
NO_OF_SEATS NUMBER(2),
constraint PK_TUT_PRAC primary key (CLASSID)
);
alter table ACADEMIC_REC
add constraint FK_ACADEMIC_RELATIONS_STUDENT foreign key (STUID)
references STUDENT (STUID);
alter table ACADEMIC_REC
add constraint FK_ACADEMIC_RELATIONS_COURSE foreign key (COURSEID)
references COURSE (COURSEID);
alter table COURSE
add constraint FK_COURSE_OFFERS_DEPARTME foreign key (DEPTID)
references DEPARTMENT (DEPTID);
alter table ENROLS
add constraint FK_ENROLS_ENROLS_STUDENT foreign key (STUID)
references STUDENT (STUID);
alter table ENROLS
add constraint FK_ENROLS_ENROLS2_TUT_PRAC foreign key (CLASSID)
references TUT_PRAC (CLASSID);
alter table HAS_PREREQUISITE
add constraint FK_HAS_PRER_HAS_PRERE_COURSE foreign key (COURSEID)
references COURSE (COURSEID);
alter table HAS_PREREQUISITE
add constraint FK_HAS_PRER_HAS_PRERE_COURSE foreign key (PREREQUISITEID)
references COURSE (COURSEID);
alter table STAFF
add constraint FK_STAFF_RELATIONS_DEPARTME foreign key (DEPARTMENTID)
references DEPARTMENT (DEPTID);
alter table TEACHING_INFORMATION
add constraint FK_TEACHING_RELATIONS_STAFF foreign key (STAFFID)
references STAFF (STAFFID);
alter table TEACHING_INFORMATION
add constraint FK_TEACHING_RELATIONS_COURSE foreign key (COURSEID)
references COURSE (COURSEID);
alter table TUT_PRAC
add constraint FK_TUT_PRAC_HAS_COURSE foreign key (COURSEID)
references COURSE (COURSEID);
alter table TUT_PRAC
add constraint FK_TUT_PRAC_RELATIONS_STAFF foreign key (STAFFID)
references STAFF (STAFFID);
I'm doing this in sql developer and fairly new at using it hope that helps in figuring the solution
Also in teaching information table the staffid and courseid are taken from their parent tables and (semester) and (year) are the attributes of the associative entity between staff & course with many to many cardinalities.
course- teaching information-staff
Insert the record in to the Course table first, after that insert into teaching information with same course id that exist in the course table.