外键可以引用同一张表中的主键吗? [英] Can a foreign key refer to a primary key in the same table?

问题描述

我只是认为答案是错误的，因为外键没有 uniqueness 属性.

I just think that the answer is false because the foreign key doesn't have uniqueness property.

但是有人说可以在自己加入表的情况下.我是 SQL 的新手.如果它是真的，请解释如何以及为什么?

But some people said that it can be in case of self joining the table. I am new to SQL. If its true please explain how and why?

Employee table
| e_id | e_name  | e_sala  |  d_id  |
|----  |-------  |-----    |--------|
|  1   |   Tom   |  50K    |    A   |
|  2   | Billy   |  15K    |    A   |
|  3   | Bucky   |  15K    |    B   |


department table
| d_id | d_name  |
|----  |-------  |
|  A   |   XXX   | 
|  B   |   YYY   | 

现在，d_id 是外键，所以它怎么可能是主键.并解释一下join.它有什么用?

Now, d_id is foreign key so how it can be a primary key. And explain something about join. What is its use?

推荐答案

我觉得这个问题有点混乱.

I think the question is a bit confusing.

如果您的意思是外键可以'引用'同一张表中的主键吗?"，答案是肯定的，正如有些人回答的那样.例如，在员工表中，员工的一行可能有一个用于存储经理的员工编号的列，其中经理也是员工，因此表中的一行与任何其他员工的行一样.

If you mean "can foreign key 'refer' to a primary key in the same table?", the answer is a firm yes as some replied. For example, in an employee table, a row for an employee may have a column for storing manager's employee number where the manager is also an employee and hence will have a row in the table like a row of any other employee.

如果您的意思是列(或列集)可以是同一个表中的主键和外键吗?"，在我看来，答案是否定的；似乎毫无意义.但是，下面的定义在 SQL Server 中成功了！

If you mean "can column(or set of columns) be a primary key as well as a foreign key in the same table?", the answer, in my view, is a no; it seems meaningless. However, the following definition succeeds in SQL Server!

create table t1(c1 int not null primary key foreign key references t1(c1))

但我认为除非有人想出一个实际的例子，否则有这样的限制是没有意义的.

But I think it is meaningless to have such a constraint unless somebody comes up with a practical example.

AmanS，在您的示例中，d_id 在任何情况下都不能是 Employee 表中的主键.一张表只能有一个主键.我希望这能消除你的疑问.d_id 是/只能是部门表中的主键.

AmanS, in your example d_id in no circumstance can be a primary key in Employee table. A table can have only one primary key. I hope this clears your doubt. d_id is/can be a primary key only in department table.

这篇关于外键可以引用同一张表中的主键吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！