Problems when creating a table referencing other with FK

Question

I'm trying to create a database for a project that works like a school with some teachers,courses,classes etc.

I need to create a new Table involving 2 tables however I'm getting an error when creating the table Classes_Teachers.

There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'FK__Classes_T__Class__11007AA7'.

Here is my SQL Code:

CREATE TABLE Teachers(
    id INT,
    name varchar(40),
    email varchar(30) FOREIGN KEY REFERENCES Users(email),
    PRIMARY KEY(id)
);

CREATE TABLE Courses(
    name varchar(20),
    acr varchar(4),
    teacher int FOREIGN KEY REFERENCES Teachers(id),
    PRIMARY KEY(acr)
);

CREATE TABLE Classes(
    id varchar(2),
    courses_acronym varchar(4) FOREIGN KEY REFERENCES Courses(acr),
    year_Semesters varchar(5),
    PRIMARY KEY(id,courses_acronym),
);

CREATE TABLE Classes_Teacher(
    Classes_id varchar(2) FOREIGN KEY REFERENCES Classes(id),
    Teachers_id INT FOREIGN KEY REFERENCES Teachers(id),
    courses_acronym varchar(4) FOREIGN KEY REFERENCES Classes(courses_acronym),
    PRIMARY KEY(Classes_id,courses_acronym)
);

`Classes` has a primary key made of 2 columns, `id` and `courses_acronym`, however, when you make your foreign key, it references only `courses_acronym`; which isn't a valid references. You need to reference both columns if you are going to have 2 columns as a Primary Key, — Thom A, Mar 22 '19 at 23:03
But im getting both attributes on my new table are you saying writing like both attributes in just one line? Sorry really didnt understand :x — Joao Barata, Mar 22 '19 at 23:07
(Obviously) This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. Please in code questions give a [mcve]--cut & paste & runnable code plus desired output plus clear specification & explanation. Minimal means give minimal code that you show does what you expect & minimal code with the first place you go wrong. (Debugging fundamental.) — philipxy, Mar 23 '19 at 06:44
Possible duplicate of [There are no primary or candidate keys in the referenced table that match the referencing column list in the foreign key](https://stackoverflow.com/questions/17879735/there-are-no-primary-or-candidate-keys-in-the-referenced-table-that-match-the-re) — philipxy, Mar 23 '19 at 18:06

score 3 · Accepted Answer · answered Mar 22 '19 at 23:08

The error comes from the foreign keys declared in table Classes_Teacher. To relate to the Classes table, you want to use both primary columns of the referred tables, instead of twn separated foreign keys.

Consider:

CREATE TABLE Classes_Teacher(
    Classes_id varchar(2),
    Teachers_id INT FOREIGN KEY REFERENCES Teachers(id),
    courses_acronym varchar(4),
    FOREIGN KEY (Classes_id, courses_acronym) REFERENCES Classes(id, courses_acronym),
    PRIMARY KEY(Classes_id,courses_acronym)
);

Demo on DB Fiddle.

score 0 · Answer 2 · answered Mar 23 '19 at 04:06

In table Classes you define a Primary Key on two columns. This is probably not needed if courses_acronym depends on ID.

CREATE TABLE Classes(
    id varchar(2),
    courses_acronym varchar(4) FOREIGN KEY REFERENCES Courses(acr),
    year_Semesters varchar(5),
    PRIMARY KEY(id),
);

In table Classes_Teacher you refer Classes twice. Shouldn't the second Foreign Key refer Courses?

CREATE TABLE Classes_Teacher(
    Classes_id varchar(2) FOREIGN KEY REFERENCES Classes(id),
    Teachers_id INT FOREIGN KEY REFERENCES Teachers(id),
    courses_acronym varchar(4) FOREIGN KEY REFERENCES Courses(acr),
    PRIMARY KEY(Classes_id,courses_acronym)
);

Problems when creating a table referencing other with FK

2 Answers2