Can a foreign key refer to the primary key of its own table?

Question

I am creating a MySQL employee database for work and I want it to store the supervisor of the employee.

Suppose I have a table called 'employee' with the fields 'id', 'first_name', 'last_name', and 'supv_id' where 'id' is the primary key and 'supv_id' is a foreign key that refers to and employee ID.

Currently I have 'supv_id' as a foreign key that points to a separate table 'supervisor'. This table simply consists of 'id' and 'empl_id' which points back to the employee table. However, if there is a way to simply make 'supv_id' in 'employee' to point to 'employee.id', this would eliminate the need of my 'supervisor' table altogether. Here is an example:

+----+--------+-----------+---------+
| id | f_name | l_name    | supv_id |
+----+--------+-----------+---------+ 
|  1 | Han    | Solo      |    NULL | //Or 0?
|  2 | Luke   | Skywalker |       1 |
+----+--------+-----------+---------+

In short, I want 'supv_id' to point to another employee. Does this make sense? How would I go about doing this?

Thanks!

Edit: fixed table

Answer 1

You can create such a table as following:

CREATE TABLE laya2 (
    id INT NOT NULL PRIMARY KEY,
    f_name VARCHAR(20),
    l_name VARCHAR(20),
    supv_id INT,
    INDEX supv_id_idx (supv_id),
    FOREIGN KEY (supv_id)
        REFERENCES laya2(id) 
        ON DELETE SET NULL      -- example for an action
) ENGINE=INNODB;

My example sets the reference option to SET NULL, because I think it's the logical one here. If an employee who supervises others left, then those employees have no supervisor first. Another option would be to have NO ACTION because you could easily identify those employees without a valid supervisor and find a new supervisor for them. ON DELETE CASCADE would be wrong here, because those employees won't leave at the same time ...

You could insert employees with

INSERT INTO laya2 VALUES
(1, 'Han', 'Solo', NULL),
(2, 'Luke', 'Skywalker', 1);

(two successful inserts), but not with

INSERT INTO laya2 VALUES
(3, 'Anakin', 'Skywalker', 0);

This statement will fail because the foreign key constraint fails.

Deleting Han Solo will change the supv_id for Luke Skywalker to NULL, because of the reference option ON DELETE SET NULL

DELETE FROM laya2 WHERE id = 1;    -- this will set the supv_id for Luke Skywalker to NULL