can anyone explain to me what primary keys actually do?

Question

I am reading a book called "MySQL developer's library" by Paul DuBois and in the book he says:

CREATE TABLE score
(
student_id INT UNSIGNED NOT NULL,
event_id INT UNSIGNED NOT NULL,
score INT NOT NULL,
PRIMARY KEY (event_id, student_id),
INDEX (student_id),
FOREIGN KEY (event_id) REFERENCES grade_event (event_id),
FOREIGN KEY (student_id) REFERENCES student (student_id)
) ENGINE = InnoDB;

We made the combination of the two columns a PRIMARY KEY. This ensures that we won't have duplicate scores for a student for a given quiz or test. Note that it's the combination of event_id and student_id that is unique. In the score table, neither value is unique by itself. There will be multiple score rows for each event_id value (one per student), and multiple rows for each student_id value (one for each quiz and test) taken by the student

What is not clear to me is the combining two columns as one primary key... Meaning, I am having a hard time visualizing what is actually going on under the hood... It's taking a collection of numbers [95, 210] for example and using that as a "key"... Is it appropriate to think of a primary key as a key to a hash?

Previously I always thought of primary keys as nothing more than unique ids for a table.. but now in this context, I am finding myself what a primary key actually is doing. Can anyone give me the low down on SQL keys?

Answer 1

"Primary keys" are a fundamental concept in relational databases. They are (meant to be) the method of identifying a row in a table.

Primary keys are very definitely not just a "unique id".

And yes, a "primary key" may (and often will) consist of more than just one column.

Here is a good link on "many-to-many" relationship tables:

http://www.tekstenuitleg.net/en/articles/software/database-design-tutorial/many-to-many.html

PS: To answer your specific question:

1. Your example one will almost certainly have at least two other tables: one for "students", another for "events".

2. In this example, the "scores" table represents the scores a particular student got at a particular event. The "composite primary key" consists of both "student id" (a "foreign key" into the "student" table), and an "event id" (a foreign key into the events table).

3. If all you wanted was the scores, the "scores" table would suffice.

4. If you wanted to print out the student's name along with his score, however, you would need to "join" the student table and the scores table (do an "inner join" on "student_id")

5. Similarly, if you wanted to print the event name or location along with the scores, you'd probably need to do an inner join of Scores and Events, on "event_id".

'Hope that helps!