Ok. So I know what a primary key in DB is. If you have a table in a database, a primary key is a single value that is unique to each row in your table. For example:
好。所以我知道DB中的主键是什么。如果数据库中有表，则主键是表中每行唯一的单个值。例如：
id | name | whatever
-------------------------
1 Alice ....
2 Bob ....
45 Eve ....
988 .... ....
So I need a good, simple example to explain what exactly a foreign key is. Because I just don't get it :)
所以我需要一个好的，简单的例子来解释究竟什么是外键。因为我只是不明白:)
Edit: OK it's pretty easy, I guess I was over-complicating the problem.
编辑：好的，这很容易，我想我的问题太复杂了。
So one final question, the only restriction on foreign keys is that it they are a valid primary key value in the table I am referring to?
所以最后一个问题，对外键的唯一限制是它们是我所指的表中的有效主键值吗？
8 个解决方案
#1
21
A foreign key is a field that points to a primary key of another table.
外键是指向另一个表的主键的字段。
Example:
例：
Table Name - Users
UserID UserName UserRoleID
1 JohnD 1
2 CourtneyC 1
3 Benjamin 2
Table Name - UserRoles
UserRoleID Desc
1 Admin
2 Moderator
You can see that Users.UserRoleID is a foreign key which points to the primary key UserRoles.UserRoleID
您可以看到Users.UserRoleID是指向主键UserRoles.UserRoleID的外键
The use of foreign keys makes setting up relationships on other tables simple, allowing you to link together the data of multiple tables in a nice way:
使用外键使得在其他表上建立关系变得简单，允许您以一种很好的方式将多个表的数据链接在一起：
Example:
例：
SELECT
a.UserID,
a.UserName,
b.Desc as [UserRole]
FROM
Users a INNER JOIN
UserRoles b ON a.UserRoleID = b.UserRoleID
Output would then be:
输出将是：
UserID UserName User Role
1 JohnD Admin
2 CourneyC Admin
3 Benjamin Moderator
#2
12
Let's say you have another field, which is the home city:
假设你有另一个领域，这是本土城市：
id | name | city
-------------------------
1 Alice San Francisco
2 Bob New York
45 Eve New York
988 Bill San Francisco
Now, it does not make sense to repeat the same cities in many rows. This could lead you to typos, excessive space usage, difficulties to bring up results among other problems. So you use a foreign key:
现在，在许多行中重复相同的城市是没有意义的。这可能会导致拼写错误，空间占用过多，难以在其他问题中提出结果。所以你使用外键：
id | name | fk_city
-------------------------
1 Alice 1
2 Bob 2
45 Eve 2
988 Bill 1
home city table:
家乡城市表：
id | name
-------------------------
1 | San Francisco
2 | New York
Hope it makes things clearer for you. :-)
希望它能让你更清楚。 :-)
Update: about your final question: Yes. :-)
更新：关于你的最后一个问题：是的。 :-)
#3
3
A foreign key is a column in one table that should uniquely identify something in another table. Thus, the values should correspond to primary keys in that other table.
外键是一个表中的列，应该唯一地标识另一个表中的内容。因此，值应该对应于该另一个表中的主键。
For example, if you have a table of students taking courses, every record would include a student id and a course id. These are foreign keys into a student table (where there is one record for each student id), and a courses table (where there is one record for each course id).
例如，如果您有一个学生参加课程表，则每个记录都会包含学生ID和课程ID。这些是学生表中的外键（每个学生ID有一个记录）和一个课程表（每个课程ID有一个记录）。
Referential integrity means that all your foreign keys actually correspond to primary keys in these target tables. For example, all the student ids and course ids in your registration table correspond to real student ids and course ids.
引用完整性意味着所有外键实际上都对应于这些目标表中的主键。例如，您的注册表中的所有学生ID和课程ID都与真实的学生ID和课程ID相对应。
#4
1
id | name | whatever | countryid
-------------------------------------
1 Alice .... 13
2 Bob .... 42
45 Eve .... 1
988 .... .... 2
id | countryid
----------------
1 Japan
2 Spain
13 Norway
42 Italy
The foreign key points from the person table (first) to a row in the country table (second)
外键指向人员表（第一个）到国家表中的一行（第二个）
#5
0
In a relational database a one-to-many relationship is implemented by having the child table reference the ID of the parent table. The parent ID in the Child table is called a Foreign Key as it references a primary key of another table.
在关系数据库中，通过让子表引用父表的ID来实现一对多关系。 Child表中的父ID称为外键，因为它引用另一个表的主键。
#6
0
A foreign key is a field that references another table in the database. For example, suppose you had 2 tables, PERSON and ADDRESS. There is a field in PERSON called ID and a field in ADDRESS called PERSON_ID. You would make PERSON_ID refer to PERSON.ID as a foreign key. What this means is that you can't have an address that is not connected to a person, since the value in the ADDRESS.PERSON_ID field must exist in the table PERSON.
外键是引用数据库中另一个表的字段。例如，假设您有2个表，PERSON和ADDRESS。 PERSON中有一个名为ID的字段，ADDRESS中有一个名为PERSON_ID的字段。您可以将PERSON_ID称为PERSON.ID作为外键。这意味着您不能拥有未连接到某个人的地址，因为ADDRESS.PERSON_ID字段中的值必须存在于表PERSON中。
#7
0
using your table example, assume you have another table:
使用您的表示例，假设您有另一个表：
cartid | id | itemid
-----------------------
100 1 abc
101 1 cde
in this table, the primary key is the cartid, the foreign key is the id, which would be linked to your first table. user 1 has two carts, each cart having one item each.
在此表中，主键是cartid，外键是id，它将链接到您的第一个表。用户1有两个推车，每个推车各有一个。
a foreign key is the what you use to link two or more tables that have related information to each other.
外键是用于链接两个或多个彼此具有相关信息的表的内容。
#8
0
A foreign key is the primary key from another table stored on your table. Say you have a table of customers and a table of orders. The CustomerId is likely the primary key on the customer table, and the OrderId is likely the primary key on the order table. But on the order table you need to know the customer for this order, no? Therefore you need to store the CustomerId on the order table. In this case the CustomerId on the order table is a foreign key.
外键是存储在表中的另一个表的主键。假设您有一张客户表和一张订单表。 CustomerId可能是customer表上的主键，OrderId可能是订单表上的主键。但是在订单表上你需要知道这个订单的客户，不是吗？因此，您需要将CustomerId存储在订单表中。在这种情况下，订单表上的CustomerId是外键。
I would point out that there is no requirement that a primary key (and therefore a foreign key) be a single column. It's simpler, sure. But I've worked on enterprise systems where the primary key was 11 columns long, and I'm sure there are examples longer than that. That is, you needed to know the value for 11 different columns before you can uniquely identify the row.
我想指出，不要求主键（因此是外键）是单个列。它更简单，更确定。但我已经在主键长11列的企业系统上工作过，而且我确信有更长的例子。也就是说，在唯一标识行之前，您需要知道11个不同列的值。
#1
21
A foreign key is a field that points to a primary key of another table.
外键是指向另一个表的主键的字段。
Example:
例：
Table Name - Users
UserID UserName UserRoleID
1 JohnD 1
2 CourtneyC 1
3 Benjamin 2
Table Name - UserRoles
UserRoleID Desc
1 Admin
2 Moderator
You can see that Users.UserRoleID is a foreign key which points to the primary key UserRoles.UserRoleID
您可以看到Users.UserRoleID是指向主键UserRoles.UserRoleID的外键
The use of foreign keys makes setting up relationships on other tables simple, allowing you to link together the data of multiple tables in a nice way:
使用外键使得在其他表上建立关系变得简单，允许您以一种很好的方式将多个表的数据链接在一起：
Example:
例：
SELECT
a.UserID,
a.UserName,
b.Desc as [UserRole]
FROM
Users a INNER JOIN
UserRoles b ON a.UserRoleID = b.UserRoleID
Output would then be:
输出将是：
UserID UserName User Role
1 JohnD Admin
2 CourneyC Admin
3 Benjamin Moderator
#2
12
Let's say you have another field, which is the home city:
假设你有另一个领域，这是本土城市：
id | name | city
-------------------------
1 Alice San Francisco
2 Bob New York
45 Eve New York
988 Bill San Francisco
Now, it does not make sense to repeat the same cities in many rows. This could lead you to typos, excessive space usage, difficulties to bring up results among other problems. So you use a foreign key:
现在，在许多行中重复相同的城市是没有意义的。这可能会导致拼写错误，空间占用过多，难以在其他问题中提出结果。所以你使用外键：
id | name | fk_city
-------------------------
1 Alice 1
2 Bob 2
45 Eve 2
988 Bill 1
home city table:
家乡城市表：
id | name
-------------------------
1 | San Francisco
2 | New York
Hope it makes things clearer for you. :-)
希望它能让你更清楚。 :-)
Update: about your final question: Yes. :-)
更新：关于你的最后一个问题：是的。 :-)
#3
3
A foreign key is a column in one table that should uniquely identify something in another table. Thus, the values should correspond to primary keys in that other table.
外键是一个表中的列，应该唯一地标识另一个表中的内容。因此，值应该对应于该另一个表中的主键。
For example, if you have a table of students taking courses, every record would include a student id and a course id. These are foreign keys into a student table (where there is one record for each student id), and a courses table (where there is one record for each course id).
例如，如果您有一个学生参加课程表，则每个记录都会包含学生ID和课程ID。这些是学生表中的外键（每个学生ID有一个记录）和一个课程表（每个课程ID有一个记录）。
Referential integrity means that all your foreign keys actually correspond to primary keys in these target tables. For example, all the student ids and course ids in your registration table correspond to real student ids and course ids.
引用完整性意味着所有外键实际上都对应于这些目标表中的主键。例如，您的注册表中的所有学生ID和课程ID都与真实的学生ID和课程ID相对应。
#4
1
id | name | whatever | countryid
-------------------------------------
1 Alice .... 13
2 Bob .... 42
45 Eve .... 1
988 .... .... 2
id | countryid
----------------
1 Japan
2 Spain
13 Norway
42 Italy
The foreign key points from the person table (first) to a row in the country table (second)
外键指向人员表（第一个）到国家表中的一行（第二个）
#5
0
In a relational database a one-to-many relationship is implemented by having the child table reference the ID of the parent table. The parent ID in the Child table is called a Foreign Key as it references a primary key of another table.
在关系数据库中，通过让子表引用父表的ID来实现一对多关系。 Child表中的父ID称为外键，因为它引用另一个表的主键。
#6
0
A foreign key is a field that references another table in the database. For example, suppose you had 2 tables, PERSON and ADDRESS. There is a field in PERSON called ID and a field in ADDRESS called PERSON_ID. You would make PERSON_ID refer to PERSON.ID as a foreign key. What this means is that you can't have an address that is not connected to a person, since the value in the ADDRESS.PERSON_ID field must exist in the table PERSON.
外键是引用数据库中另一个表的字段。例如，假设您有2个表，PERSON和ADDRESS。 PERSON中有一个名为ID的字段，ADDRESS中有一个名为PERSON_ID的字段。您可以将PERSON_ID称为PERSON.ID作为外键。这意味着您不能拥有未连接到某个人的地址，因为ADDRESS.PERSON_ID字段中的值必须存在于表PERSON中。
#7
0
using your table example, assume you have another table:
使用您的表示例，假设您有另一个表：
cartid | id | itemid
-----------------------
100 1 abc
101 1 cde
in this table, the primary key is the cartid, the foreign key is the id, which would be linked to your first table. user 1 has two carts, each cart having one item each.
在此表中，主键是cartid，外键是id，它将链接到您的第一个表。用户1有两个推车，每个推车各有一个。
a foreign key is the what you use to link two or more tables that have related information to each other.
外键是用于链接两个或多个彼此具有相关信息的表的内容。
#8
0
A foreign key is the primary key from another table stored on your table. Say you have a table of customers and a table of orders. The CustomerId is likely the primary key on the customer table, and the OrderId is likely the primary key on the order table. But on the order table you need to know the customer for this order, no? Therefore you need to store the CustomerId on the order table. In this case the CustomerId on the order table is a foreign key.
外键是存储在表中的另一个表的主键。假设您有一张客户表和一张订单表。 CustomerId可能是customer表上的主键，OrderId可能是订单表上的主键。但是在订单表上你需要知道这个订单的客户，不是吗？因此，您需要将CustomerId存储在订单表中。在这种情况下，订单表上的CustomerId是外键。
I would point out that there is no requirement that a primary key (and therefore a foreign key) be a single column. It's simpler, sure. But I've worked on enterprise systems where the primary key was 11 columns long, and I'm sure there are examples longer than that. That is, you needed to know the value for 11 different columns before you can uniquely identify the row.
我想指出，不要求主键（因此是外键）是单个列。它更简单，更确定。但我已经在主键长11列的企业系统上工作过，而且我确信有更长的例子。也就是说，在唯一标识行之前，您需要知道11个不同列的值。