PostgreSQL error : there is no unique constraint matching given keys for referenced table

Question

create table instructor(
instructor_id varchar(15) not null,
instructor_name varchar(15) not null,
dept_name varchar(15) not null,
primary key(instructor_id),
unique(instructor_name));

create table time_slot(
time_slot_id varchar(15) not null,
day varchar(15) not null,
start_time varchar(15) not null,
end_time varchar(15) not null,
primary key(time_slot_id, day, start_time));

create table student(
student_id varchar(15) not null,
user_id varchar(15) not null,
password varchar(15) not null,
num_item int,
primary key(student_id, user_id));


create table section(
course_id varchar(15) not null,
sec_id varchar(15) not null,
semester varchar(15) not null,
year varchar(15) not null,
title varchar(15) not null,
building varchar(15),
room_no varchar(15),
time_slot_id varchar(15),
dept_name varchar(15),
credits varchar(15),
instructor_id varchar(15) not null,
primary key(course_id, sec_id, semester, year),
foreign key(time_slot_id) references time_slot(time_slot_id),
foreign key(instructor_id) references instructor(instructor_id));

create table evaluation(
user_id varchar(15) not null,
sec_id varchar(15) not null,
total_score int not null,
workload int not null,
difficulty int not null,
attendance int not null,
grade int not null,
accomplishment int not null,
comment varchar(200),
primary key(user_id, sec_id),
foreign key(user_id) references student(user_id),
foreign key(sec_id) references section(sec_id));


create table teaches(
instructor_id varchar(15) not null,
course_id varchar(15) not null,
sec_id varchar(15) not null,
semester varchar(15) not null,
year varchar(15) not null,
primary key(instructor_id, course_id, sec_id, semester, year),
foreign key(instructor_id) references instructor(instructor_id),
foreign key(course_id) references section(course_id),
foreign key(sec_id) references section(sec_id),
foreign key(semester) references section(semester),
foreign key(year) references section(year));

I tried to create tables using postgresql with these codes, but it's keep saying that there is no unique constraint matching given keys for referenced table. This message comes up when I tried to create table teaches, section, and evaluation.

Please help me.

+) I changed the order of create statement, and added unique key in table instructor, but still not working.

Solution 1

Basically, you need to make sure that the reference in a foreign key has a corresponding unique or primary key.

Your teaches table, has a reference of

foreign key(instructor_id) references instructor(instructor_id),

But there is no primary key on the instructor_id. instead, you have a primary key on instructor_id and instructor_name.

I suspect this is a mistake is it would mean that two instructors could have the same id as long as their name was different.

Replacing the single primary key on both fields with a primary key on the id, and a unique key on instructor name would allow you to make sure both fields are unique, while providing the constraint that the teaches table needs to link back to instructors.

create table instructor(
instructor_id varchar(15) not null,
instructor_name varchar(15) not null,
dept_name varchar(15) not null,
primary key(instructor_id),
unique(instructor_name));

This also applies to all the other primary keys you have defined that are multiple columns but being referenced by a foreign key with a single field.

In addition, you should reorder the create statements as mentioned by @sagi

EDIT. From sagi's comments below it seems my answer is confusing. To try and clarify.

A foreign key of REFERENCES (col1) requires that the table have on it a constraint that is one of:

 PRIMARY KEY (col1)
 UNIQUE (col1)

It will not match against PRIMARY KEY (col1, col2)

If the primary key is definitely supposed to be two columns, that means it can only be referenced by a foreign key that has both columns: REFERENCES (col1, col2)

Solution 2

The order of those CREATE DDL comands is not correct, you are trying to create references that doesn't exists yet.

Create the tables in the following order:

1 - instructor
2 - time_slot
3 - student
4 - section
5 - evaluation
6 - teaches