Referencing a foreign key in SQL

Question

The problem I am having is when I am trying to create a foreign key with one of the keys in the composite key of another table. SQL developer gives me the following error:

"There are no primary or candidate keys in the referenced table..."

For instance I have a studentInfo, studentRoom and some arbitrary module table:

CREATE TABLE module 
(
    id_module VARCHAR(45),
    module_name VARCHAR(45),
    PRIMARY KEY (id_module)
);

CREATE TABLE studentInfo 
( 
    id_student INT,
    id_module VARCHAR(45),
    PRIMARY KEY (id_student, id_module),
    FOREIGN KEY (id_module) REFERENCES module(id_module) 
);

CREATE TABLE studentRoom 
( 
    id_student INT,
    id_room VARCHAR(45),
    id_module VARCHAR(45),
    PRIMARY KEY (id_student, id_room),
    FOREIGN KEY (id_module) REFERENCES module(id_module),
    FOREIGN KEY (id_student) REFERENCES studentInfo(id_student) 
);

This is the line of code that gives me the error:

FOREIGN KEY (id_student) REFERENCES studentInfo(id_student)

Is there a way I can circumvent this error because I need to connect both these tables with id_student but also need id_student and id_module to be a composite primary key in the studentInfo table.

UPDATE

After reading comments I fixed my error, but I am also wondering how I could deal with the problem if id_module was not a variable in the studentRoom table as the following did not work when creating the table:

CREATE TABLE studentRoom (
id_student INT,
id_room VARCHAR(45),
PRIMARY KEY (id_student, id_room),
FOREIGN KEY (id_student,id_module) REFERENCES studentInfo(id_student,id_module) )

UPDATE AGAIN

So I have to add the module column to studentRoom in order for it to work.

Post your actual code, because as it is there are typos like FOREGIN instead of FOREIGN and module instead of id_module. — forpas, Dec 19 '20 at 07:55
Your primary key in `studentinfo` is `(id_student, id_module)` (assuming `module` is a typo). A foreign key needs to reference **all** columns of the primary key. This has been asked [many times before](https://stackoverflow.com/search?q=[sql]+"There+are+no+primary+or+candidate+keys+in+the+referenced+table") — , Dec 19 '20 at 08:03
@a_horse_with_no_name oh i see you mean something like this: FOREIGN KEY (id_student, id_module) REFERENCES studentInfo(id_student, id_module) — glockm15, Dec 19 '20 at 08:10

score 0 · Answer 1 · answered Dec 19 '20 at 08:15

Ok so after reading comments I think I should have done this for the studentRoom table:

CREATE TABLE studentRoom (
id_student INT,
id_room VARCHAR(45),
id_module VARCHAR(45),
PRIMARY KEY (id_student, id_room),
FOREIGN KEY (id_student,id_module) REFERENCES studentInfo(id_student,id_module) );

Which also resolved my error.

Referencing a foreign key in SQL

1 Answers1