我正在读 Paul DuBois 写的一本名为“MySQL 开发人员库”的书，他在书中说道：
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;
我们将两列的组合设为主键。
这可以确保我们不会为学生提供重复的分数
对于给定的测验或测试。请注意，它是以下组合
事件ID
和student_id是唯一的。在分数表中，这两个值都没有
是独一无二的
靠它自己。每个 event_id 将有多个分数行
价值
（每个学生一个），每个 Student_id 值有多行
（每人一份
学生参加的测验和测试
我不清楚的是，将两列合并为一个主键...意思是，我很难想象幕后实际发生的情况...它正在收集一个集合例如，数字 [95, 210] 并将其用作“键”...将主键视为哈希的键是否合适？
以前我一直认为主键只不过是表的唯一 ID。但现在在这种情况下，我发现主键实际上在做什么。谁能告诉我 SQL 键的真相吗？
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?
发布评论
评论（4）
“主键”是关系数据库中的基本概念。它们（应该是）识别表中行的方法。
主键绝对不仅仅是一个“唯一 ID”。
是的，“主键”可能（并且通常会）包含多个列。
这是“多对多”关系表的一个很好的链接：
http://www.tekstenuitleg.net/en/articles/software/database-design-tutorial/many-to-many.html
PS：
要回答您的具体问题：
您的示例几乎肯定会有至少两个其他表：一个用于“学生”，另一个用于“事件”。
在此示例中，“分数”表表示特定学生在特定活动中获得的分数。 “复合主键”由“student id”（“student”表中的“外键”）和“event id”（events 表中的外键）组成。
如果您想要的只是分数，那么“分数”表就足够了。
但是，如果您想打印出学生的姓名及其分数，则需要“连接”学生表和分数表（对“student_id”执行“内部连接”）
类似地，如果您想打印事件名称或位置以及分数，您可能需要在“event_id”上对分数和事件进行内部连接。
'希望有帮助！
"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:
Your example one will almost certainly have at least two other tables: one for "students", another for "events".
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).
If all you wanted was the scores, the "scores" table would suffice.
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")
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.
主键本质上是一个具有非空约束的唯一键。
（如前所述，它用于强制数据的引用完整性）
它只是一种不同类型的主键：
代理键/人工键=通常是一个序列号
http://en.wikipedia.org/wiki/Surrogate_key
自然键 = 使行唯一的列
http://en.wikipedia.org/wiki/Natural_key
和此处 http://www.geeksengine.com/database/design/primary-key-constraint.php这是对差异的一个很好的解释。
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.