Index oder Primary Key?

[2nd]

Moin,

ich brauch mal Hilfe fürs Grundverständnis in MySQL Datenbanken:

Ich habe ja die Möglichkeit, folgende Schlüssel zu definieren: Primary Key, Index und diese dann noch Unique oder nicht. Ist es eine dumme Frage zu fragen, wo der Sinn hinter beiden Schlüsseln steckt?

Ich habe 2 Tabellen in meiner Datenbank, in der einen (Tabelle 1) habe ich einen Auto_Increment Index, auf den sich aus Tabelle 2 bezogen wird (dort wird nur der Bezug auf den Index in Tabelle 1 gespeichert).

Macht das Sinn so? Oder brauche ich einen Primary Key? Oder einen Unique Index?

Danke,

2nd

 

[goddess]

Ein Index in einer relationalen Datenbank ist abstrakt gesehen genauso wie der Index in einem Buch. D.h. du hast die Suchbegriffe in geordneter Reihenfolge, was das suchen erleichtert und die Geschwindigkeit, zu einem Ergebnis zu kommen, wesentlich erhöht. Wenn du eine Spalte in einer DB-Tabelle mit einem Index versiehst, baut die Datenbank im Hintergrund ein Indexverzeichnis auf, in welchem sie dann suchen kann (d.h. nach Index kann man schneller suchen). Suchst du ein nicht-indexiertes Feld, dann muss die Datenbank-Engine sequentiell suchen, d.h. jeden Datensatz durchgehen und vergleichen --> irre langsam.

Unique bedeutet, dass du ein Feld einzigartig machen kannst. Das macht z.B. Sinn wenn du eine eindeutige Email-Adresse eintragen willst (wie in der meisten Forums-Software).

Mit einem Primärschlüssel kann man einen Datensatz eindeutig (unique) kennzeichnen. Eindeutig bedeutet, dass es kein Feld in der Spalte geben kann, welches ident ist, es besteht also keine Verwechslungsgefahr. Gleichzeitig wird über eine Spalte, die ein Primärschlüssel ist, ein Index gelegt (siehe oben). Das tolle ist, dass du den Primärschlüssel aus mehreren Feldern zusammenbauen kannst. D.h. der eindeutige Schlüssel ist die Kombination aus den Feldern.

Wenn ich dich richtig verstanden habe ist in Tabelle2 der Index aus Tabelle1 der Fremdschlüssel. Es hängt jetzt davon ab, ob das Feld Index aus Tabelle1 einen mehrfachen Bezug (1:n) zu Tabelle2 hat, sprich es kann mehrfach vorkommen. Wenn ja, dann bau dir zusätzlich zum Fremdschlüssel-index einen eindeutigen Bezeichner für Tabelle2. Über beide Felder legst du dann den Primary Key.

CREATE TABLE `test` (
`tab1_index` INT NOT NULL ,
`tab2_index` INT NOT NULL ,
PRIMARY KEY ( `tab1_index` , `tab2_index` )
);

Wenn es jeweils nur einen Bezug zu Tabelle1 geben soll (1:1), also für jeden Datensatz in Tabelle1 nur e i n e Entsprechung in Tabelle2, dann genügt es, wenn du den Primarykey über den Fremdschlüssel legst.

Ich hoffe, das war jetzt nicht zu verwirrend. Einfacher kann ichs leider nicht mehr erklären

 

[2nd]

Uff, habe ich mir doch gedacht, dass das Ganze nicht ganz trivial ist Danke für Deine ausführliche Antwort, leider verstehe ich die Zusammenhänge noch nicht so ganz.

Gibt es da gute Literatur im Netz oder in Buchform? Ich muss mich unbedingt damit beschäftigen, das scheint Sinn zu machen.

Wenn es jeweils nur einen Bezug zu Tabelle1 geben soll (1:1), also für jeden Datensatz in Tabelle1 nur e i n e Entsprechung in Tabelle2, dann genügt es, wenn du den Primarykey über den Fremdschlüssel legst.

Also in der Tabelle 1 sind z. B. Bilder gespeichert und in Tabelle 2 die Kommentare dazu. Deswegen gehe ich davon aus, das es für die Datensätze in Tabelle1 m e h r e r e Entsprechungen in Tabelle 2 gibt oder?

Was nun? Das hier?

Wenn ja, dann bau dir zusätzlich zum Fremdschlüssel-index einen eindeutigen Bezeichner für Tabelle2. Über beide Felder legst du dann den Primary Key.

Nach meiner beschränkten Logik müsste es doch so funktionieren:

Tabelle 1:

content_id <- das ist der Index (AutoIncrement)___________BildURL ___________Fotograf

12_________________________________________________http://www....._______Müller-Meier
13
..



Tabelle 2:

comment_id <- das ist der Index (AutoIncrement)___________Kommentar___________Autor___________ID

50 ______________________________________________________Blablabla___________Lehmann________12
51
52
..


Damit bezieht sich der Kommentar von Lehmann doch auf das Bild von Müller-Meier mit dem Index 12 in Tabelle 1. Geht das nicht? Was ist der Nachteil, fernab von Primary Keys? Die Indicies in beiden Tabellen sind doch auch "unique", da bei jedem neuen Datensatz hochgezählt wird?!?

2nd

 

[dpr]

Gibt es da gute Literatur im Netz oder in Buchform?

Jedes Datenbank-Lehrbuch sollte die notwendigen Grundlagen vermitteln können. Achte darauf, daß auch die Entwurfsphase abgedeckt ist, anderenfalls endet die Geschichte als "Datenbankentwurf aus der Praxis".

Also in der Tabelle 1 sind z. B. Bilder gespeichert und in Tabelle 2 die Kommentare dazu. Deswegen gehe ich davon aus, das es für die Datensätze in Tabelle1 m e h r e r e Entsprechungen in Tabelle 2 gibt oder?

Was nun?

Das ist noch immer eine 1:n-Beziehung, die aus Richtung der Kommentare betrachtet zwingend ist: Einem Kommentar wird genau ein Bild, einem Bild können (d.h. optional) mehrere Bilder zugeordnet werden. Ein Fremdschlüssel in den Kommentaren bezüglich der Bilder setzt das um.

Apropos, um nocheinmal die Ausgangsfrage aufzugreifen:

Primär- und Fremdschlüssel sind im Relationenmodell enthaltene Integritätsbedingungen und spielen damit auf der konzeptuellen Ebene. Eine Stufe tiefer befindet sich die interne Ebene, dort gibt es Dateiorganisationen und Zugriffspfade und damit die Möglichkeit, diese Strukturen zu beeinflussen. Du hast also Begriffe zweier unterschiedlicher Ebenen gebraucht: Ein (Primär-)Schlüssel identifiziert ein Tupel, ein Index ist in erster Linie ein Zugriffspfad, der auch "identifizierend" sein kann, aber nicht muß.

 

[2nd]

Auch an Dich Danke dpr

Also kann ich mein Modell der Kommentare und Bilder mit Index und ohne Primary Key erstmal so umsetzen ohne Sorge haben zu müssen, dass ich in ein paar Monaten die komplette Datenbankstruktur in die Tonne kloppen muss?

2nd

 

[dpr]

Also kann ich mein Modell der Kommentare und Bilder mit Index und ohne Primary Key erstmal so umsetzen

Nimm Dir pro Relation einen Primärschlüssel und in den Kommentaren einen Fremdschlüssel bezüglich der Bilder. Den physischen Entwurf (Stichwort Index) kannst Du später immernoch verfeinern.

 

[2nd]

Sorry wenn ich noch mal doof nachfragen muss:

Der Primärschlüssel soll in die Tabelle mit den Bildern, z. B. kann ich den über das Datum und den Fotografen legen?

Und als Fremdschlüssel in der Kommentartabelle kann ich wie oben in meinem Modell einfach die ID mit Bezug auf den Index in Tabelle 1 speichern?

2nd

 

[dpr]

Der Primärschlüssel soll in die Tabelle mit den Bildern, z. B. kann ich den über das Datum und den Fotografen legen?

Und als Fremdschlüssel in der Kommentartabelle kann ich wie oben in meinem Modell einfach die ID mit Bezug auf den Index in Tabelle 1 speichern?

Kandidaten für den Primärschlüssel hast Du ja bereits: content_id in der einen, comment_id in der anderen Relation. Jetzt brauchst Du sie eigentlich nur noch als solche auszeichnen:

create table Bilder (
  content_id ...,
  ...,
  primary key (content_id)
);

create table Kommentar (
  comment_id ...,
  ...,
  id ...,
  primary key (comment_id),
  foreign key (id) references Bilder(content_id)
);

Damit das mit mysql funktioniert, wirst Du (sofern sich das nicht zwischenzeitlich geändert haben sollte) als Tabellentypen InnoDB verwenden müssen.

EDIT: ich habe mal der Vollständigkeit wegen das fehlende Klammernpaar der FK-Klausel ergänzt.

 

[2nd]

Ok, das mit den Primarys habe ich jetzt hinbekommen und ich denke auch verstanden, zumindest das mit einfachen Primary Keys.

Jetzt komme ich aber mit der Foreign Keys noch nicht ganz klar, genauer gesagt mit der SQL Syntax. Ich habe meine beiden Tabellen in InnoDB-Tabellen geändert, jedoch sagt der SQL Parser einen Fehler an bei folgender Syntax:

alter table comments (
  foreign key ID references content(content_id)
)

Also meine Kommentartabelle heisst "comments" und die Spalte heisst "ID", die Bildertabelle heisse "content" und der Primary Key dort "content_id".

Was ist in meinem SQL Statement falsch? Es gibt bei phpMyAdmin leider keinen Button für einen Foreign Key


Noch eine Frage: Was ist von dem kurzen Text hinter folgender URL zu halten?

http://www.little-idiot.de/mysql/mysql-45.html

Bis jetzt konnte ich immer meine Datenbanken dumpen (Online & Offline) und hatte nie ein Problem mit dem Wiedereinspielen. Geht das durch die Foreign Keys verloren?

2nd

 

[dpr]

Was ist in meinem SQL Statement falsch?

Innerhalb der create-table-Anweisung klappt es. BTW: Ich habe oben wohl die Klammern um das Attribut vergessen, also foreign key (id) references...

Noch eine Frage: Was ist von dem kurzen Text hinter folgender URL zu halten?

Exakt nichts. Ich habe noch nie solch eine sinnfreie Begründung gelesen, auf integritätssichernde Maßnahmen zu verzichten.

 

[goddess]

Noch eine Frage: Was ist von dem kurzen Text hinter folgender URL zu halten?

Also mach dir da keinen Kopf. Der Text ist Schwachsinn, aber der Domänenname sagt ja schon einiges aus...

Also Fremdschlüssel kann man in richtig fetten Datenbanken wie Oracle, DB2 usw (in MySql seit version 5 glaub ich auch...) wirklich definieren, da gibt es etwas, das sich referenzielle Integrität nennt, was schlicht und einfach bedeutet, dass die Schlüssel in Tabellenbeziehungen immer zusammenpassen müssen. D.h. du kannst dann keinen Fremdschlüssel in Tabelle 2 haben, wenns den Schlüssel in Tabelle 1 nicht gibt.

 

[2nd]

Oh Leute, Ihr seid super Danke für die Infos!

Ich habe es jetzt mit dieser Syntax hinbekommen:

create table kommentare (
 comment_id INT, ID INT,
 PRIMARY KEY (comment_id),
 FOREIGN KEY (ID) REFERENCES content(content_id)
) TYPE = InnoDB;

Konkret bedeutet ein referenzierter Fremdschlüssel, dass sich bei Änderung des Index in Tabelle 1 (auf den sich bezogen wird) die Referenzierung mitändert? Also z. B. Bild Nr. 10 kriegt einen neuen Index, z . B. 109 - dann beziehen sich auch automatisch alle Kommentare auf Index 109 statt 10?

2nd <- hat dann noch eine Frage und dann ist gut

 

[dpr]

Konkret bedeutet ein referenzierter Fremdschlüssel, dass sich bei Änderung des Index in Tabelle 1 (auf den sich bezogen wird) die Referenzierung mitändert?

Kurz zur Terminolgie: ein Fremdschlüssel referenziert, der beteiligte Primärschlüssel wird referenziert. (Du kannst es Dir als Zeiger vorstellen, der Pfeil geht vom Fremd- zum Primärschlüssel.)

Zur Deiner Frage: Nein. Grundsätzlich ist eine Fremdschlüsselbedingung eine Maßnahme zur Integritätssicherung. In der Regel werden integritätsverletzende Operationen zurückgewiesen, d.h. wenn Du etwa einen Primärschlüssel, der referenziert wird so änderst, daß mindestens eine Referenz "in die Leere" läuft, wird diese Operation zurückgewiesen. Andersrum werden solche Operationen zurückgewiesen, die Werte auf Fremdschlüsseln einführen, zu denen es keine passenden (referenzierten) Primärschlüssel gibt.

Kurz und knapp: die Fremdschlüsselbedingung fordert (und stellt sicher), daß die Menge der Fremdschlüsselattributwerte eine Teilmenge der referenzierten Attributwerte des Primärschlüssels ist (das ist übrigens eine Inklusionsabhängigkeit).

 

[2nd]

Das mit dem Pointer habe ich mir schon gedacht.

Ok, also wenn ich einen Datensatz mit Primärschlüssel löschen will (also z. B. ein Bild in Tabelle 1) funktioniert das nicht, da sonst die Fremdschlüssel ins Leere laufen.

Ich muss praktisch Bild und Kommentare in beiden Tabellen gleichzeitig löschen? Richtig verstanden?

Sorry fürs Rumnerven

2nd

 

[dpr]

Ok, also wenn ich einen Datensatz mit Primärschlüssel löschen will (also z. B. ein Bild in Tabelle 1) funktioniert das nicht, da sonst die Fremdschlüssel ins Leere laufen.

Korrekt.

Ich muss praktisch Bild und Kommentare in beiden Tabellen gleichzeitig löschen?

Zuerst entfernst Du den abhängigen Kommentar, dann das Bild. Macht ja auch Sinn, denn den Kommentar wirst Du ohne zugehöriges Bild kaum verwenden können.

Eine unschöne Alternative, die man sich erst gar nicht einprägen sollte: Fremdschlüssel können NULL-Werte annehmen.

Eine andere Möglichkeit wäre ein kaskadierendes Löschen, d.h. bei Löschen eines Bildes werden alle abhängigen Kommentare ebenfalls gelöscht. Oracle kann das z.B. mit

... foo references bar(foo) on delete cascade

Wie die Sache bei mysql steht, kann ich im Moment nicht sagen.

 

[2nd]

Ok, alles klar, vielen Dank, damit kann ich viel anfangen

2nd