Kann ein Fremdschlüssel auf den Primärschlüssel seiner eigenen Tabelle verweisen?

Question

Ich erstelle eine MySQL-Mitarbeiterdatenbank für die Arbeit und möchte, dass sie den Vorgesetzten des Mitarbeiters speichert.

Angenommen, ich habe eine Tabelle namens 'employee' mit den Feldern 'id', 'first_name', 'last_name' und 'supv_id', wobei 'id' der Primärschlüssel ist und 'supv_id' ein Fremdschlüssel ist, der auf eine Mitarbeiter-ID verweist.

Derzeit habe ich 'supv_id' als Fremdschlüssel, der auf eine separate Tabelle 'supervisor' zeigt. Diese Tabelle besteht einfach aus 'id' und 'empl_id', das wieder auf die Mitarbeiter-Tabelle verweist. Wenn es jedoch eine Möglichkeit gibt, einfach 'supv_id' in 'employee' auf 'employee.id' zeigen zu lassen, würde dies die Notwendigkeit meiner 'supervisor'-Tabelle ganz eliminiert. Hier ist ein Beispiel:

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

Kurz gesagt, ich möchte, dass 'supv_id' auf einen anderen Mitarbeiter zeigt. Macht das Sinn? Wie gehe ich dabei vor?

Vielen Dank!

Bearbeitung: Tabelle korrigiert

Answer 1

Ja, verknüpfen Sie die Tabelle mit sich selbst. Hier ist eine von vielen Möglichkeiten:

SELECT a.l_name AS Mitarbeiter, b.l_name AS Vorgesetzter 
  FROM employee AS a, employee AS b 
  WHERE a.supv_id = b.id             -- Verknüpfung der Tabellen
    AND a.id = 2                     -- Mitarbeiter abrufen

Ergebnis:

Mitarbeiter  | Vorgesetzter
----------+-----------
Skywalker | Solo

Answer 2

Sie können eine solche Tabelle wie folgt erstellen:

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      -- Beispiel für eine Aktion
) ENGINE=INNODB;

Mein Beispiel setzt die Referenzoption auf SET NULL, weil ich denke, dass dies hier die logische ist. Wenn ein Mitarbeiter, der andere beaufsichtigt, geht, haben diese Mitarbeiter zunächst keinen Vorgesetzten. Eine andere Option wäre NO ACTION, weil Sie diese Mitarbeiter ohne gültigen Vorgesetzten leicht identifizieren und einen neuen Vorgesetzten für sie finden könnten. ON DELETE CASCADE wäre hier falsch, weil diese Mitarbeiter nicht zugleich gehen ...

Sie können Mitarbeiter einfügen mit

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

(zwei erfolgreiche Einfügungen), aber nicht mit

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

Diese Aussage wird scheitern, weil die Fremdschlüssel-Beschränkung fehlschlägt.

Das Löschen von Han Solo ändert die supv_id für Luke Skywalker auf NULL, aufgrund der Referenzoption ON DELETE SET NULL

DELETE FROM laya2 WHERE id = 1;    -- Dadurch wird die supv_id für Luke Skywalker auf NULL gesetzt

Answer 3

Ja, Sie können einen Fremdschlüssel definieren, der auf den Primärschlüssel seiner eigenen Tabelle verweist.

create table employee (id int(10),
                       f_name varchar(10),
                       l_name varchar(10),
                       supv_id int(10)) ENGINE=InnoDB;
alter table employee add primary key (id);
alter table employee add foreign key (supv_id) references employee (id);

Mitarbeiter ohne Vorgesetzten müssen NULL in der supv_id Spalte haben.