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

问题描述

好的。所以我知道DB中的主键是什么。如果在数据库中有表，则主键是表中每行唯一的一个值。例如：

  id |名称|无论
 ------------------------- 
 1 Alice .... 
 2 Bob .... 
 45 Eve .... 
 988 .... .... 
  

所以我需要一个好的，简单的例子来解释一个外键是什么。因为我只是没有得到它：）

---

编辑我想我过于复杂的问题。

所以一个最后一个问题，外键的唯一限制是它是一个有效的主键值在表I外键是一个指向另一个表的主键的字段。

$

$

b
$ b

示例：

 表名 - 用户
 
 UserID UserName UserRoleID 
 1 JohnD 1 
 2 CourtneyC 1 
 3 Benjamin 2 
 
表名 -  UserRoles 
 
 UserRoleID Desc 
 1管理员
 2主持人
  

您可以看到Users.UserRoleID是一个外键，主键UserRoles.UserRoleID

使用外键使得在其他表上设置关系变得简单，允许您以一种很好的方式将多个表的数据链接在一起： / p>

示例：

  SELECT 
 a.UserID，
 a.UserName，
 b.Desc as [UserRole] 
 FROM 
用户a INNER JOIN 
 UserRoles b ON a.UserRoleID = b.UserRoleID 
  

输出将是：

  UserID UserName用户角色
 1 JohnD Admin 
 2 CourneyC Admin 
 3 Benjamin Moderator 
  

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

这篇关于什么是外键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！