there is no unique constraint matching given keys for referenced table in SQL

Question

CREATE TABLE TEST( course_number INTEGER NOT NULL,
                    semester INTEGER NOT NULL,
                    time INTEGER NOT NULL,
                    room INTEGER NOT NULL,
                    day INTEGER NOT NULL,
                    credit_points INTEGER NOT NULL,
                    UNIQUE(course_number,semester),
                    CHECK(course_number>0),
                    CHECK(credit_points>0),
                    CHECK(room>0));
                    
CREATE TABLE STUDENT (student_id INTEGER NOT NULL,
                    student_name text NOT NULL,
                    faculity text NOT NULL,
                    credit_points INTEGER NOT NULL,
                    UNIQUE(student_id),
                    CHECK(student_id>0),
                    CHECK(credit_points>=0));
                    
CREATE TABLE STUDENT_REG
                    (student_id INTEGER NOT NULL,
                    course_number INTEGER NOT NULL,
                    semester INTEGER NOT NULL, 
                    FOREIGN KEY (student_id) REFERENCES STUDENT(student_id),
                    FOREIGN KEY (course_number) REFERENCES TEST(course_number),
                    FOREIGN KEY (semester) REFERENCES TEST(semester));

I have three tables:

1. Test
2. Student
3. Student registration, it's purpose is to a student to a course.

I get this error when I compile the third table:

ERROR: there is no unique constraint matching given keys for referenced table "test"

I have no idea why, any help will be highly appreciated.

Answer 1

You want a compound foreign key rather than two distinct keys:

CREATE TABLE STUDENT_REG (
    student_id INTEGER NOT NULL,
    course_number INTEGER NOT NULL,
    semester INTEGER NOT NULL, 
    FOREIGN KEY (student_id) REFERENCES STUDENT(student_id),
    FOREIGN KEY (course_number semester) REFERENCES TEST(course_number, semester)
);

Why you need that is because table TEST as has compound unique key on these two columns:

UNIQUE(course_number,semester)

So for table STUDENT_REG to unambigously refer to a row in TEST, you need the combination of both columns, which means a 2-columns foreign key rather than two distinct foreign keys.