5 
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.

Improve this question
4
  • Create the tables first, then add the fk's. (alter table add constraint.) Or re-arrange the create statements. Commented Jun 7, 2016 at 8:12
  • I rearranged the create statements, but still not working... Commented Jun 7, 2016 at 9:43
  • section's fk foreign key(instructor_id) references instructor(instructor_id)) has no matching unique key in instructor. instructor's pk has 2 columns. Commented Jun 7, 2016 at 9:48
  • You need to review all the primary keys in all your tables. I think they should all be a single column. If every instructor has a different instructor_id, then you should have just the instructor_id in there. If every student has a different student_id the primary key should just be the student_id. Commented Jun 7, 2016 at 11:11

2 Answers 2

Reset to default
3

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)

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Then what is this line ? primary key(instructor_id, instructor_name));
@sagi that means that the combination of instructor_id & instructor_name is unique. You can duplicate both instructor_id and instructor_name as long as the same combination doesn't appear twice. So two instructors called Dave and Bob can both have ID1.
OK, there's still a primary key as opposed to what you said. Foreign key can reference only one of the keys .
@sagi I never said there wasn't a primary key, I said there wasn't a primary key on just instructor_id which there isn't. Foreign keys can reference as many columns as they need. You might want to check out postgresql.org/docs/current/static/ddl-constraints.html
I think you may are the one that need to check it out. You are answering your own question. Foreign keys can reference as many columns as they need, they can also reference only one! The PK is OK, and that is not the issue in his query! You might want to check my answer above.
|
1

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.