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

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

​x
 
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:

Test
Student
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"

 
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

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

 
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)

 
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.

Advertisement

Answer