✔ Fremdschlüssel auf Primärschlüssel der eigenen Tabelle

[tklustig]

Hallo, folgender SQL-Code erstellt u.a. einen Fremdschlüssel auf den Primärschlüssel der eigenen Tabelle. Was soll das bringen? Was haben sich die Entwickler dabei gedacht? Kenne die Referenzierung nur auf die Primärschlüssel anderer Tabellen.

CREATE TABLE `product` (
  `id` binary(16) NOT NULL,
  `version_id` binary(16) NOT NULL,
  `parent_id` binary(16) DEFAULT NULL,
  `parent_version_id` binary(16) DEFAULT NULL);

ALTER TABLE `product`
  ADD PRIMARY KEY (`id`,`version_id`),
  ADD KEY `fk.product.parent_id` (`parent_id`,`parent_version_id`);
 
  ALTER TABLE `product`
  ADD CONSTRAINT `fk.product.parent_id` FOREIGN KEY (`parent_id`,`parent_version_id`) REFERENCES `product` (`id`, `version_id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

 

[Zvoni]

Wieso steht der Thread auf "erledigt"? *gg*

Zum Thema: Ja, sowas gibt es tatsächlich, wobei ich aber die Constraints sehr gefährlich finde (ON DELETE CASCADE vor allem). Ausserdem muss klar sein, dass in so einem Design maximal nur eine "1:m"-Beziehung möglich ist (nicht "m:m")

Stell dir vor, du hast ein Geschäft, welches Gewürze verkauft.

Du hast eine Produkt-Tabelle, welche so Sachen beinhaltet wie:
ID - Beschreibung
1 - Pfeffer
2 - Salz
3 - Zimt
4 - Oregano

Und jetzt stell dir vor, du führst ein neues Produkt ein mit ID 10, welches heisst "Gewürzpaket für Weihnachten", welches aus Pfeffer, Zimt und Oregano besteht (jeweils Fremdschlüssel auf ID 10)

In so einem Beispiel sieht man deutlich warum ich den ON DELETE CASCADE so gefährlich finde.
Lösche das "Gewürzpaket"......

Ist jetzt natürlich nur rudimentär erklärt.

Ein anderes Beispiel für so ein Szenario (Fremdschlüssel auf eigenen Primär-Schlüssel), wäre zum Beispiel eine Personal-Tabelle für Angestellte.
ID - AngestelltenName - VorgesetztenID

Du hast deinen ganz normalen Eintrag in so einer Tabelle, dein Vorgesetzter ist aber auch Bestandteil dieser Tabelle

EDIT: Mir ist jetzt doch ein Beispiel eingefallen, wo so ein ON DELETE CASCADE "Sinn" macht.
Stell dir vor du hast ein Programm, welches dynamisch Auswahl-Menüs ("Datei", "Bearbeiten" usw.) erstellt.
Du hast ne Tabelle in deiner DB namens "tbl_menues", mit Feldern wie
ID - Caption - Kind - Parent_ID
1 - Datei - NULL - NULL
2 - Speichern unter - NULL - 1 (!!!)
3 - Datei sperren - CheckBox - 1

in so einem Fall macht ON DELETE CASCADE durchaus Sinn.
Du willst das Menü "Datei" löschen, und es nimmt alle seine Kinder mit ins Grab...

 

[tklustig]

Wieso steht der Thread auf "erledigt"? *gg*

Habe ich soeben rückgängig gemacht. Sorry!

Ein anderes Beispiel für so ein Szenario (Fremdschlüssel auf eigenen Primär-Schlüssel), wäre zum Beispiel eine Personal-Tabelle für Angestellte.
ID - AngestelltenName - VorgesetztenID

Du hast deinen ganz normalen Eintrag in so einer Tabelle, dein Vorgesetzter ist aber auch Bestandteil dieser Tabelle

Das verstehe ich nicht, soll heißen, meine Frage ist nach wie vor nicht verständlich beantwortet. Wenn ich eine Angestelltentabelle und eine Vorgesetztentabelle habe referenziere ich in der Angestelltentabelle auf die Vorgesetztentabelle mittels eines Fremdschlüssels. Wozu brauche ich da eine Referenzierung auf die eigene Tabelle?

 

[Zvoni]

Hab nochmal meinen Post erweitert (Siehe EDIT)

 

[tklustig]

Häh? In so einem Fall würde ich ein

]ALTER TABLE Menu
DROP COLUMN Datei_loschen;

abfeuern
Es ist das Prinzip, was ich nicht verstehe. Ich kann doch jeden Fremdschlüssel von DELETE RESTRICT auf DELETE UPDATE umstellen, ohne dass ich dazu einen FK auf den eigenen PK benötige....

 

[Zvoni]

Habe ich soeben rückgängig gemacht. Sorry!

Das verstehe ich nicht, soll heißen, meine Frage ist nach wie vor nicht verständlich beantwortet. Wenn ich eine Angestelltentabelle und eine Vorgesetztentabelle habe referenziere ich in der Angestelltentabelle auf die Vorgesetztentabelle mittels eines Fremdschlüssels. Wozu brauche ich da eine Referenzierung auf die eigene Tabelle?

Weil der Vorgesetzte AUCH ein Angestellter der Firma ist, und somit BEREITS in der Angestellten-Tabelle vorhanden ist.

Du wirst angestellt (Eintrag in Angestellten-Tabelle), aber dir wird noch kein Vorgesetzter zugeordnet, weil du noch in der Probezeit bist usw....
Dein späterer Vorgesetzter ist auch in der Angestellten-Tabelle enthalten.

Klar, macht natürlich nur Sinn, wenn du (später) auch nur EINEN Vorgesetzten hast (und nicht zwei oder mehr haben könntest)

Vielleicht wird es mit einem "Mutter- Kind"-Szenario besser
(z.B. Geburtsstation in einem Krankenhaus. Es werden nur "Mutter" und "Kind" erfasst. Was ineterssiert schon der Vater... *gg*... der war nur Produktionshelfer....)
Ein "Kind" kann nur eine "Mutter" haben, eine "Mutter" kann aber viele Kinder haben
Beide sind in der gleichen Tabelle erfasst, weil beide die gleichen Attribute haben
(Geschlecht, Geburtsdatum, Wohnort, usw.)

Das ungewöhnliche ist NICHT, dass ein Fremdschlüssel auf den Primär-Schlüssel der gleichen Tabelle zeigt.
Das ungewöhnliche mMn sind die Constraints (insbesondere ON DELETE CASCADE)

 

[tklustig]

Ich habe mal gelernt, dass der Hauptzweck der Normalisierung die Vermeidung von Redundanzen und Anomalien ist. Das sehe ich in der Vorgehensweise FK auf eigenen PK nicht gegeben.

 

[Zvoni]

Häh? In so einem Fall würde ich ein

]ALTER TABLE Menu
DROP COLUMN Datei_loschen;

abfeuern
Es ist das Prinzip, was ich nicht verstehe. Ich kann doch jeden Fremdschlüssel von DELETE RESTRICT auf DELETE UPDATE umstellen, ohne dass ich dazu einen FK auf den eigenen PK benötige....

Um das gehts hier nicht.
Es geht darum, eine Produkt-Struktur abzubilden.

Hab darüber nachgedacht, und vielleicht wirds mit folgendem besser verständlich:
Du hast ein Geschäft für Computer-Hardware.
Du hast "products"
ID - Description - EinForeignKey
1 - Laptop DELL Latitude 5510 - NULL

Jetzt gibt es ein Motherboard, welches absolut und einzig NUR in deinem Produkt ID=1 verbaut wird
(Dieses Motherboard wird in keinem anderen Laptop/PC verbaut)
Da du das Motherboard aber auch einzeln beim Hersteller bestellen musst, führst du es AUCH in deiner "products"-Tabelle, weil du dieses Motherboard ggfs. auch einzeln direkt an einen Kunden verkaufen willst (Ersatzteil, weil Kunden-Motherboard wurde gegrillt)

Also trägst du es in deine "products" ein, machst aber gleich den Verweis, dass es im Laptop verbaut wird
ID - Description - EinForeignKey
1 - Laptop DELL Latitude 5510 - NULL
2 - Motherboard - 1 (<-- Verweis auf ID=1, der Laptop)

auf diese Weise kannst du im Rahmen einer Statistischen Analyse dann sehen:
So und so viele fertige Laptops habe ich verkauft, soundsoviele Motherboards habe ich verbaut und so und so viele habe ich direkt verkauft

Wenn du dich in 2-3 Jahren entscheidest "OK, der Laptop ist nicht mehr aktuell. Ich nehme ihn aus dem Sortiment", und du dann ID=1 in der Tabelle löschst, fliegt automatisch das Motherboard mit raus, weil wozu bräuchtest du es denn? Nur um es als Ersatzteil zu verkaufen?

Das ist der Hintergedanke bei dem Szenario

 

[Zvoni]

Ich habe mal gelernt, dass der Hauptzweck der Normalisierung die Vermeidung von Redundanzen und Anomalien ist. Das sehe ich in der Vorgehensweise FK auf eigenen PK nicht gegeben.

Muss ich widersprechen.
Ist kein Verstoss gegen Normalform.
Wie gesagt: Das ungewöhnliche ist NICHT FK auf PK derselben Tabelle.
Das Ungewöhnliche ist der ON DELETE CASCADE

Stell dir eine Cocktail-Bar vor.
Du kannst dort O-Saft, Jack Daniels, Wodka, Rum, Maracuja-Saft etc. alles einzeln bestellen ("Produkte")
Es gibt aber dort auch "Produkte", welche aus einzelnen Komponeten bestehen ("Gin Tonic", "Whisky Cola"....)

Es ist durchaus üblich sowas alles zusammen in einer Tabelle zu haben

 

[tklustig]

Wie soll das mittels FK auf eigenen PK gehen?(siehe Fragezeichen auf dem Screenshot)