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

Question

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?

You might want to add which DBMS you are using so that you are getting examples that work for your DBMS. — , Sep 08 '13 at 07:33

score 130 · Accepted Answer · edited Oct 06 '18 at 07:18

130

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.

edited Oct 06 '18 at 07:18

Rohit Sharma

3,304
2
19
34

answered Sep 08 '13 at 06:06

mvsagar

1,998
2
17
19

its clear to me that d_id is primary key of department table. also d_id in employee table is foreign key. and also you mention that d_id can be primary key in same table(employee). my teacher told me that in case of self joining of employee table it can be possible. this point is not clear to me how? – AmanS Sep 09 '13 at 15:55
1

@AmanS, you said "you mention that d_id can be primary key in same table(employee)". But I did not; here is my statement "AmanS, in your example d_id in no circumstance can be a primary key in Employee table". Hope you get it. It is not possible even in self join case. Another column in a table can refer to primary key of the same table. E.g. create table employee(e_id int primary key, e_name varchar(30), e_mgr int, foreign key (e_mgr) references employee(e_id)). This is a self join case and e_mgr is a foreign key that refers to the primary key e_id. – mvsagar Sep 11 '13 at 14:39
can you tell me any practical example with query in which foreign can refer to primary key? but here also the person below (ryvantage) said that it can.... i am confused please help – AmanS Sep 11 '13 at 15:49
@AmanS, I already gave an example of employee table in my previous comment and ryvantage also gave an example of Person table with data. These are practical examples. You read any book on database systems and read these comments once again and I hope you will get it. There is nothing like a foreign key referring to a primary key in a query (DML statement). The reference will be present only in DDL statement CREATE TABLE or ALTER TABLE statement. And my DDL statement CREATE TABLE employee... in my earlier comment shows it. – mvsagar Sep 12 '13 at 12:57
2

@AmanS, this is what probably you are looking for. The select statement "SELECT DISTINCT e.e_id AS 'Manager Id', e.e_name AS 'Manager Name' FROM employee e, employee m WHERE e.e_id = m.e_mgr;" gives you all employees who are managers. Here primary key column e_id is compared with foreign key column e_mgr. – mvsagar Sep 13 '13 at 06:07

score 35 · Answer 2 · answered Jul 08 '19 at 08:05

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.

score 17 · Answer 3 · edited Oct 06 '18 at 06:46

17

Sure, why not? Let's say you have a Person table, with id, name, age, and parent_id, where parent_id is a foreign key to the same table. You wouldn't need to normalize the Person table to Parent and Child tables, that would be overkill.

Person
| id |  name | age | parent_id |
|----|-------|-----|-----------|
|  1 |   Tom |  50 |      null |
|  2 | Billy |  15 |         1 |

Something like this.

I suppose to maintain consistency, there would need to be at least 1 null value for parent_id, though. The one "alpha male" row.

EDIT: As the comments show, Sam found a good reason not to do this. It seems that in MySQL when you attempt to make edits to the primary key, even if you specify CASCADE ON UPDATE it won’t propagate the edit properly. Although primary keys are (usually) off-limits to editing in production, it is nevertheless a limitation not to be ignored. Thus I change my answer to:- you should probably avoid this practice unless you have pretty tight control over the production system (and can guarantee no one will implement a control that edits the PKs). I haven't tested it outside of MySQL.

edited Oct 06 '18 at 06:46

Rohit Sharma

3,304
2
19
34

answered Sep 08 '13 at 05:26

ryvantage

13,064
15
63
112

just see my edited question with example. is your answer is still yes? – AmanS Sep 11 '13 at 15:52
1

It doesn't work for `update`. check http://sqlfiddle.com/#!9/445052/1/0 and then try adding `Update menus set id = 6 WHERE id = 1;` you will get `#1451 - Cannot delete or update a parent row` – Sam Nov 12 '17 at 05:41
@Sam two things: 1) I see that it doesn't work, but I disagree that it should fail. With a cascading update, there doesn't seem to be any logical reason why the update should fail. 2) Who changes PKs in practical situations? People who are asking for trouble lol – ryvantage Nov 12 '17 at 21:42
@ryvantage Completely agree with you that in practical situations PK is not updated. Just wanted to add this comment to mention that this is a known [documented](https://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html) limitation (or [bug](https://bugs.mysql.com/bug.php?id=24668) ) in mysql. – Sam Nov 13 '17 at 11:04
I see `ON UPDATE CASCADE` is mentioned in the edit. I would advise against using `[ON UPDATE CASCADE] [ON DELETE CASCADE]` in general as the cascading action [doesn't trigger triggers](https://mariadb.com/kb/en/foreign-keys/#constraints). If you have triggers that should be triggered on an update in table A, and you do an update in table B which leads to a cascading change in A, triggers on table A are not triggered. Use `ON UPDATE RESTRICT` instead, and handle the necessary deleting manually in the application logic. – Adrian Wiik Oct 30 '20 at 14:14

Nimya V · Answer 4 · 2017-06-29T06:36:47.010

9

Eg: n sub-category level for categories .Below table primary-key id is referred by foreign-key sub_category_id

edited Jun 29 '17 at 06:36

answered Jun 28 '17 at 06:47

Nimya V

336
3
5

score 2 · Answer 5 · edited Apr 14 '18 at 22:13

2

A good example of using ids of other rows in the same table as foreign keys is nested lists.

Deleting a row that has children (i.e., rows, which refer to parent's id), which also have children (i.e., referencing ids of children) will delete a cascade of rows.

This will save a lot of pain (and a lot of code of what to do with orphans - i.e., rows, that refer to non-existing ids).

edited Apr 14 '18 at 22:13

Andre Silva

4,782
9
52
65

answered Feb 09 '18 at 09:38

bbe

344
3
16

score 1 · Answer 6 · answered Jan 21 '21 at 11:18

Other answers have given clear enough examples of a record referencing another record in the same table.

There are even valid use cases for a record referencing itself in the same table. For example, a point of sale system accepting many tenders may need to know which tender to use for change when the payment is not the exact value of the sale. For many tenders that's the same tender, for others that's domestic cash, for yet other tenders, no form of change is allowed.

All this can be pretty elegantly represented with a single tender attribute which is a foreign key referencing the primary key of the same table, and whose values sometimes match the respective primary key of same record. In this example, the absence of value (also known as NULL value) might be needed to represent an unrelated meaning: this tender can only be used at its full value.

Popular relational database management systems support this use case smoothly.

Take-aways:

When inserting a record, the foreign key reference is verified to be present after the insert, rather than before the insert.
When inserting multiple records with a single statement, the order in which the records are inserted matters. The constraints are checked for each record separately.
Certain other data patterns, such as those involving circular dependences on record level going through two or more tables, cannot be purely inserted at all, or at least not with all the foreign keys enabled, and they have to be established using a combination of inserts and updates (if they are truly necessary).

score 0 · Answer 7 · answered Sep 16 '21 at 12:02

Adding to the answer by @mysagar the way to do the same in MySQL is demonstrated below -

CREATE TABLE t1 (
    -> c1 INT NOT NULL,
    -> PRIMARY KEY (c1),
    -> CONSTRAINT fk FOREIGN KEY (c1)
    -> REFERENCES t1 (c1)
    -> ON UPDATE RESTRICT
    -> ON DELETE RESTRICT
    -> );

would give error -

ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'fk' in the referenced table 't1'

The correct way to do it is -

CREATE TABLE t1 (
    -> c1 INT NOT NULL,
    -> PRIMARY KEY (c1),
    -> KEY i (c1),
    -> CONSTRAINT fk FOREIGN KEY (c1)
    -> REFERENCES t1 (c1)
    -> ON UPDATE RESTRICT
    -> ON DELETE RESTRICT
    -> );

One practical utility I can think of is a quick-fix to ensure that after a value is entered in the PRIMARY KEY column, it can neither be updated, nor deleted.

For example, over here let's populate table t1 -

INSERT INTO t1 (c1) VALUES
    -> (1),
    -> (2),
    -> (3),
    -> (4),
    -> (5);

SELECT * FROM t1;
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+

Now, let's try updating row1 -

UPDATE t1
    -> SET c1 = 6 WHERE c1 = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`constraints`.`t1`, CONSTRAINT `fk` FOREIGN KEY (`c1`) REFERENCES `t1` (`c1`) ON DELETE RESTRICT ON UPDATE RESTRICT)

Now, let's try deleting row1 -

DELETE FROM t1
    -> WHERE c1 = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`constraints`.`t1`, CONSTRAINT `fk` FOREIGN KEY (`c1`) REFERENCES `t1` (`c1`) ON DELETE RESTRICT ON UPDATE RESTRICT)

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

7 Answers7

Linked