General SQL error

Toad for Oracle
1

Hey guys wondering if you could help im trying to implement a database and it all goes in well with the below script but when I try to insert data i get an error telling me ORA-02291 and im not sure where ive gone wrong here if anyone could help would be awesome thanks :)!

CREATE TABLE subject_area (
subject_id number(2),
subject_name varchar2(30),
CONSTRAINT pk_subject_area PRIMARY KEY (subject_id)
);

CREATE TABLE company (
company_id number(2),
company_name varchar2(15),
city varchar2(12),
industry varchar2(15),
no_employees number(5),
revenue number(10),
training_budget number(6),
CONSTRAINT pk_company PRIMARY KEY (company_id)
);

CREATE TABLE course (
course_id number(3),
title varchar2(30),
duration number(2),
cost number(5),
subject_id number(2),
CONSTRAINT pk_course PRIMARY KEY (course_id),
CONSTRAINT fk_subject
FOREIGN KEY (subject_id)
REFERENCES subject_area(subject_id)
);

CREATE TABLE instructor (
instructor_id number(3),
instructor_name varchar2(15),
salary number(6,2),
commission number(6,2),
mentor_id number(3) ,
date_hired date,
CONSTRAINT pk_instructor PRIMARY KEY (instructor_id),
CONSTRAINT fk_mentor
FOREIGN KEY (mentor_id)
REFERENCES instructor(instructor_id)
);

CREATE TABLE site (
site_id number(2),
location varchar2(12),
CONSTRAINT pk_site PRIMARY KEY (site_id)
);

CREATE TABLE student (
student_id number(4),
student_fname varchar2(10),
student_lname varchar2(12),
tel_no varchar2(15),
fax_no number(15),
gender varchar2(1),
date_of_birth date,
student_desc varchar2(25),
preferred_language varchar2(2),
passport_program varchar2(1),
company_id number(2) not null,
CONSTRAINT pk_student PRIMARY KEY (student_id),
CONSTRAINT fk_company
FOREIGN KEY (company_id)
REFERENCES company (company_id)
);

CREATE TABLE auth (
instructor_id number(3) not null,
course_id number(3) not null,
auth_date date,
CONSTRAINT pk_auth PRIMARY KEY (instructor_id,course_id),
CONSTRAINT fk_instructor
FOREIGN KEY (instructor_id)
REFERENCES instructor(instructor_id),
CONSTRAINT fk_course
FOREIGN KEY (course_id)
REFERENCES course(course_id)
);

CREATE TABLE offering (
offering_id number(5),
course_id number(3) not null,
instructor_id number(3) not null,
site_id number(2) not null,
start_date date,
max_no_students number(2),
CONSTRAINT pk_offering PRIMARY KEY (offering_id),
CONSTRAINT fk_auth
FOREIGN KEY (instructor_id, course_id)
REFERENCES auth (instructor_id, course_id),
CONSTRAINT fk_site
FOREIGN KEY (site_id)
REFERENCES site (site_id)
);

CREATE TABLE attendance (
offering_id number(5) not null,
student_id number(4) not null,
evaluation varchar2(1),
amount_paid number(6,2),
CONSTRAINT pk_attendance PRIMARY KEY (offering_id,student_id),
CONSTRAINT fk_offering
FOREIGN KEY (offering_id)
REFERENCES offering (offering_id),
CONSTRAINT fk_student
FOREIGN KEY (student_id)
REFERENCES student(student_id)
);

commit;

2

The problem is in foreign key created for instructor table:

CREATE TABLE instructor (

  instructor_id   number(3),

  instructor_name varchar2(15),

  salary          number(6,2),

  commission      number(6,2),

  mentor_id       number(3) ,

  date_hired      date,

  CONSTRAINT pk_instructor PRIMARY KEY (instructor_id),

  CONSTRAINT fk_mentor

        FOREIGN KEY (mentor_id)

        REFERENCES instructor(instructor_id)

  );

The possible solution is to have this key deferrable or enforce this rule differently.

Boris

3

Thanks! :slight_smile: how would you suggest I enforce this key differently? sorry abit of an oracle newbie - thanks.

4

Thanks for the help :slight_smile:

Message was edited by: Eulogix_74

5

hey :slight_smile: ok so basically one of the things I want to do with these tables is to link a list of courses to a subject the user types in I tried using a variable and I cant seem to get one for this working what I’ve got so far is

Code sql:
SELECT subject_area.subject_name, course.course_id
FROM Subject_area, Course,
WHERE subject_area.subject_name = &subject;

it’s driving me insane! i’m quite new to SQL so sorry for the nooby question lol

help much appreciated! :slight_smile: