Two primary keys in a table, databases, SQL, Cracking the Coding Interview Chapter 14

Question

I am reading Cracking the Coding Interview these days. Chapter 14 is about Databases and SQL. There is a paragraph called "SQL Statements". Please write in your answers if you have the book and read the paragraph. It introduces several tables, which will be used in an example. One of them "StudentCourses" has two columns which are both used as primary keys.

How can this be the case? How can a table have two primary keys? I use MySQL and tried to create all the tables with roles out of The Simpsons (the TV Series), but of cause this doesn't work for StudentCourses. Does anyone know what the author meant by that example or how I should go about this problem?

Thanks a lot :)

Answer 1

Okay, thanks for you comments and edits. The solution is that the keys are composite primary keys and foreign keys. So I had to reference this from the other tables. What helped me was the comment of Martin Smith and this video: https://www.youtube.com/watch?v=SydCFe5eF4o Thank you!

In my point of view the author should have made this more clear.

The solution to create the right table is:

create table StudentCourses

(CourseID int(11),

StudentID int(11),

primary key (CourseID, StudentID),

Foreign key (CourseID) references Courses(CourseID),

foreign key (StudentID) references Students(StudentID) );

Btw.: Did nobody of you guys read the book?!