究竟什么是外键?[英] What exactly is a foreign key?

本文是小编为大家收集整理的关于究竟什么是外键?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

好的.所以我知道DB中的主要键是什么.如果您的数据库中有一个表,则主键是表中每一行唯一的单个值.例如:

id   | name    | whatever
-------------------------
1      Alice     ....
2      Bob       ....
45     Eve       ....
988    ....      ....

因此,我需要一个很好的简单示例来解释什么是外键.因为我只是不明白:)


编辑:好吧,这很容易,我想我过于复杂问题.

所以最后一个问题,对外国钥匙的唯一限制是,它们是我所指的表中的有效主要钥匙值?

推荐答案

外键是指向另一个表的主要键的字段.

示例:

Table Name - Users

UserID    UserName    UserRoleID
1         JohnD       1
2         CourtneyC   1
3         Benjamin    2

Table Name - UserRoles

UserRoleID    Desc
1             Admin
2             Moderator

您可以看到users.userroleid是一个外键,它指向主密钥userroles.userroleid

使用外键使在其他表上建立关系简单,使您可以以一种很好的方式将多个表的数据链接在一起:

示例:

SELECT
    a.UserID, 
    a.UserName, 
    b.Desc as [UserRole]
FROM 
    Users a INNER JOIN 
        UserRoles b ON a.UserRoleID = b.UserRoleID

输出将是:

UserID    UserName    User Role
1         JohnD       Admin
2         CourneyC    Admin
3         Benjamin    Moderator

其他推荐答案

假设您还有另一个领域,这是家乡:

id   | name    | city
-------------------------
1      Alice     San Francisco
2      Bob       New York
45     Eve       New York
988    Bill      San Francisco

现在,在许多行中重复相同的城市是没有意义的.这可能会导致您遇到错别字,空间使用过多,难以提出结果等问题.因此,您使用外键:

id   | name    | fk_city
-------------------------
1      Alice     1
2      Bob       2
45     Eve       2
988    Bill      1

家乡桌子:

id   | name
-------------------------
1    | San Francisco
2    | New York

希望它使您更清楚. : - )

更新:关于您的最后一个问题:是的. : - )

其他推荐答案

外键是一个表中的一列,该列应该在另一表中唯一标识某些东西.因此,值应与其他表中的主要密钥对应.

例如,如果您有一张学生参加课程的表格,则每个记录都包括学生ID和课程ID.这些是学生表中的外键(每个学生ID都有一个记录)和一个课程表(每个课程的记录都有一个记录ID).

参考完整性意味着您的所有外键实际上都对应于这些目标表中的主要密钥.例如,注册表中的所有学生ID和课程ID对应于真实的学生ID和课程ID.

本文地址:https://www.itbaoku.cn/post/597664.html

问题描述

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:

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?

推荐答案

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

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

其他推荐答案

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. :-)

其他推荐答案

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).

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.