ora-02270 How do i fix this?

Question

This is what I have:

CREATE TABLE STUDENTS(
    stu_id NUMBER(9) NOT NULL,
    stu_name VARCHAR2(15) NOT NULL,
    stu_address VARCHAR2(15),
    stu_major VARCHAR2(6),
    CONSTRAINT students_pk PRIMARY KEY (stu_id)
);


CREATE TABLE GRADE(
    stu_id NUMBER(9) NOT NULL,
    course_id VARCHAR2(7),
    stu_crse_grade VARCHAR2(1),
    PRIMARY KEY (stu_id, course_id),
    FOREIGN KEY (stu_id) REFERENCES students(stu_id),
    CHECK (stu_crse_grade = 'A' 
    AND stu_crse_grade = 'B' 
    AND stu_crse_grade = 'C' 
    AND stu_crse_grade = 'D' 
    AND stu_crse_grade = 'F')
);


CREATE TABLE COURSE(
    course_id VARCHAR2(7),
    course_title VARCHAR2(20),
    instructor_id NUMBER(3),
    PRIMARY KEY (course_id, instructor_id),
    FOREIGN KEY (course_id) REFERENCES grade(course_id)
);

When I try to create the 3rd table I get this error. Is it because of the composite primary key in the grade table? How do I fix this?

Answer 1

Your primary key in GRADE covers two columns: stu_id and course_id. You cannot use course_id as a reference from the COURSE table, because it could potentially reference multiple rows.

Your foreign key should be in the GRADE table instead:

CREATE TABLE GRADE(
    stu_id NUMBER(9) NOT NULL,
    course_id VARCHAR2(7),
    stu_crse_grade VARCHAR2(1),
    PRIMARY KEY (stu_id, course_id),
    FOREIGN KEY (stu_id) REFERENCES students(stu_id),
    FOREIGN KEY (course_id) REFERENCES course(course_id),
    CHECK (stu_crse_grade = 'A' 
    AND stu_crse_grade = 'B' 
    AND stu_crse_grade = 'C' 
    AND stu_crse_grade = 'D' 
    AND stu_crse_grade = 'F')
);

Additionally, for your COURSE table, your primary key needs to be NOT NULL, and should only encompass the course_id field. If you could potentially have multiple course_id's for different instructors, how is GRADE.course_id going to distinguish which course I'm in, if I don't supply an instructor_id?

CREATE TABLE COURSE(
    course_id VARCHAR2(7) NOT NULL,
    course_title VARCHAR2(20),
    instructor_id NUMBER(3),
    PRIMARY KEY (course_id)
);