外键是否可以引用同一个表中的主键？ [英] can foreign key refer to primary key in same table?

问题描述

我只是觉得答案是错误的，因为外键没有唯一性属性。

有人说可以自己加入桌子。
我是新到 SQL 。如果真的请解释如何和为什么？

 员工表
 | e_id | e_name | e_sala | d_id | 
 | ---- | ------- | ----- | -------- | 
 | 1 |汤姆| 50K | A | 
 | 2 |比利| 15K | A | 
 | 3 | Bucky | 15K | B | 
 
 
部门表
 | d_id | d_name | 
 | ---- | ------- | 
 | A | XXX | 
 | B | YYY | 
  

现在d_id是外键，所以它可以作为主键。并解释加入的一些事情。它的用途是什么？

解决方案

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

如果你的意思是可以外键是指在同一个表中的主键？，答案是肯定的，有些答案是肯定的。例如，在员工表中，员工的某一行可能有一列用于存储管理者的员工编号，而管理者也是一名员工，因此在表中将有一行像其他任何员工一样。

如果您的意思是可以将列（或列集）作为主键还是同一个表中的外键？，在我看来，答案，是不是;这似乎毫无意义。但是下面的定义在SQL Server中是成功的！

pre code create table t1（c1 int not null主键外键引用t1（c1） ）

但是我觉得除非有人提出一个实际的例子，否则就没有这个约束。 amanS，在你的例子中，d_id在任何情况下都不能作为Employee表中的主键。一个表只能有一个主键。我希望这清除你的怀疑。 d_id是/只能是部门表中的主键。

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

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   | 

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

解决方案

I think the question is a bit confusing.

If you 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.

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, 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屋！