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?

You are definitely not using MySQL, but Oracle DB. Please confirm which RDBMS are you using ? — Madhur Bhaiya, Oct 22 '18 at 18:21
Possible duplicate of [Oracle (ORA-02270) : no matching unique or primary key for this column-list error](https://stackoverflow.com/questions/10802212/oracle-ora-02270-no-matching-unique-or-primary-key-for-this-column-list-erro) — Daniel W., Oct 22 '18 at 18:24
sql server?? sorry this is all new to me so still all a bit confusing! — betty, Oct 22 '18 at 18:24

Blue · Accepted Answer · 2018-10-22T18:58:33.560

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

edited Oct 22 '18 at 18:58

answered Oct 22 '18 at 18:24

Blue

22,608
7
62
92

i need to create 4 tables and all of them need to have a PK and FK. When I try to run your version of the grade table it won't create it since course table does not exist yet – betty Oct 22 '18 at 18:29
@betty Create the COURSE table first (It shouldn't have any references). – Blue Oct 22 '18 at 18:32
I created the COURSE table first, then STUDENTS, and when I tried to create the GRADE table I got the same error.. – betty Oct 22 '18 at 18:53
Thanks so much! IDK why but I assumed I had to create the STUDENTS table first?? But it worked just fine when I created the COURSE table first and STUDENTS second. – betty Oct 22 '18 at 19:16
The only order that matters is that GRADE comes after the other 2. The thing to remember: You should always be referencing the primary key from another table. The table with alternate column keys (In this case GRADE) should be the ones that have the references. If this answered all your questions, don't forget to leave the green checkmark. – Blue Oct 22 '18 at 19:23

ora-02270 How do i fix this?

1 Answers1