Search code examples
sqlforeign-keysprimary-key

Can a foreign key refer to a primary key in the same table?

I just think that the answer is false because the 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 a primary key. And explain something about join. What is its use?

Solution

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

    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.