can anyone explain to me what primary keys actually do?

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 N开发者_如何学JAVAULL,
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?

"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!

The values in the primary key allow you to uniquely identify a single row of data in the table at all times.

If you have multiple candidate keys, then the values corresponding to each candidate key uniquely identify a single row in the table at all times. One candidate key is (can be) chosen to be the 'primary key', but the choice of which candidate key to nominate is largely arbitrary. The criteria are outside the realm of the relational model of data per se (typically, you choose the shortest key, or the most widely used key).

So, the primary key is the tuple-level or row-level addressing mechanism for a table.

The point of keys is data integrity. The key prevents any user entering duplicate rows - rows with the same set of values for the key attributes. This ensures that every row can be uniquely identified and therefore avoids redundancy, anomalies and inccorect results that might otherwise arise from duplicating information. Keys implement business rules, define the meaning of your table and protect the integrity of your data.

In your particular example, if it was possible to enter multiple rows per event and student then a single student could have more than one score per event. The key implements a rule that a student can have only one score per event.

A Primary Key is essentially it is a unique key with a not null constraint. (as already mentioned, it is used to enforce referential integrity of your data)

It is simply a different type of primary key:

surrogate key / artificial key = usually a sequential number

http://en.wikipedia.org/wiki/Surrogate_key

natural key = column(s) that make the row unique

http://en.wikipedia.org/wiki/Natural_key

and here http://www.geeksengine.com/database/design/primary-key-constraint.php is a really good explanation of the differences.