Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 399
AmanS Avatar asked Sep 08 '13 05:09

AmanS


People also ask

Can foreign key refer to primary key?

Yes, foreign key has to be primary key of parent table. Yes, it may not be unique and may have duplicate entries in child table, but it must be unique and does not have any duplicate entries at the parent table (as it is a primary key).

Can a field be primary key and foreign key at the same time?

A Foreign Key is used for referential integrity, to make sure that a value exists in another table. The Foreign key needs to reference the primary key in another table. If you want to have a foreign key that is also unique, you could make a FK constraint and add a unique index/constraint to that same field.

Can two foreign keys in the same table reference the same primary key?

Yes, it is okay to have two fk to the same pk in one table.

Can a foreign key refer to its own relation?

A foreign key can be declared to reference either the primary key or a unique key of another table to establish a direct relationship between the two tables.


2 Answers

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.

like image 197
mvsagar Avatar answered Oct 23 '22 09:10

mvsagar


This may be a good explanation example

CREATE TABLE employees (
id INTEGER NOT NULL PRIMARY KEY,
managerId INTEGER REFERENCES employees(id), 
name VARCHAR(30) NOT NULL
);

INSERT INTO employees(id, managerId, name) VALUES(1, NULL, 'John');
INSERT INTO employees(id, managerId, name) VALUES(2, 1, 'Mike');

-- Explanation: -- In this example. -- John is Mike's manager. Mike does not manage anyone. -- Mike is the only employee who does not manage anyone.

like image 29
Γιώργος Τερζής Avatar answered Oct 23 '22 09:10

Γιώργος Τερζής