A A A

Datenbanktabellen - Indizes, Fremdschlüssel und Reservierungen

Vorbemerkungen

Damit konkurrierende Aktionen auf Datensätzen und Datenbanktabellen sich zeitlich nicht überschneiden können, müssen Reservierungen für die zu bearbeitenden Datensätze und Datenbanktabellen eingetragen werden.

Datenbanktabellen, die einen PRIMARY-Index oder mindestens einen UNIQUE-Index besitzen, besitzen einen Reservierungs-Index, der mit der Funktion tabelle_index_infos_ermitteln() aus der Datei "__zac4web/__funktionen/db_tabellen_allgemein.php" ermittelt werden kann. Wie der Reservierungs-Index ausgewählt wird und wie er für Reservierungen von Datensätzen verwendet wird, wird auf der Seite Sperren und reservieren im Abschnitt "Befehle zum Erzeugen von Reservierungen" beschrieben.

Wenn Veränderungen an Personen-, Adress- und Organisationskonten erfolgen sollen, sind außerdem die Regeln aus der Datei Personen-, Adress- und Organisationskonten unbedingt anzuwenden.

zum Anfang

Indizes

Indizes werden für Datenbanktabellen definiert, damit das Datenbankmanagementsystem Datensätze schneller finden kann und/oder die Informationen zu einem Objekt für die Speicherung auf verschiedene Datensätze aufgeteilt werden könnem. Einem Index kann genau eine Spalte einer Datenbanktabelle T zugeordnet sein oder mehrere oder sogar sämtliche Spalten der Datenbanktabelle T.

Das Aufteilen der Informationen ist dann sinnvoll, wenn die gleichen Informationen für mehrere Datensätze gelten oder bestimmte Informationen Zusatzinformationen sind, die nicht für alle Datensätze erfasst werden müssen. Durch das Aufteilen kann die Pflege der Daten erleichtert und/oder Speicherplatz eingespart werden. Das Aufteilen setzt voraus, dass in beiden Tabellen ein Index vorhanden ist und diese per Fremdschlüsselbeziehung miteinander verknüpft werden. Der Index aus der Tabelle A, die anweist einem Datensatz aus der Tabelle A einen Datensatz aus der referenzierten Tabelle B zuzuordnen, kann eindeutig oder einfach sein. Der Index in der referenzierten Tabelle B muss dagegen eindeutig sein.

Für jeden Index aus der Tabelle T erzeugt das Datenbank-Managementsystem eine Indextabelle, die genau so viele Spalten enthält, wie dem Index zugeordnet sind. Wenn ein Datensatz in die Tabelle T eingefügt wird, werden die Werte aus den Index-Spalten der Tabelle T zusammen mit dem Hinweis, zu welchem Datensatz aus der Tabelle T sie gehören auch in der Index-Tabelle gespeichert.
Allerdings kann jede Spalte einer MySQL-Indextabelle, die zu einer Datenbank-Tabelle vom Typ InnoDB gehört, maximal 767 Bytes aufnehmen. Bei anderen Tabellen-Typen sind es sogar 1000 Bytes.
Werte aus Tabellenspalten mit einem numerischen Datentyp können immer vollständig in der Indextabelle gespeichert werden. Bei Tabellenspalten, die Zeichenketten aufnehmen können, hängt es dagegen von der verwendeten Codierung für die Tabellenspalte ab, ob der Wert vollständig oder nur der Beginn der Zeichenketten in der Indextabelle gespeichert werden kann. Bei einer Codierung, die nur ein Byte Speicherplatz pro Zeichen benötigt (z.B. "ascii" und die "latin-1"-Varianten), können also 767 bzw. 1000 Zeichen in den Spalten der Index-Tabelle aufgenommen werden. Wird dagegen eine Zeichenkodierung vom Typ "utf8" für die Spalte verwendet, die je Zeichen entweder ein, zwei oder drei Bytes belegt, können in den Spalten der Index-Tabelle beim Tabellen-Typ InnoDB nur maximal 255 Zeichen gespeichert werden, da MySQL von dem ungünstigen Fall ausgehen muss, dass jedes Zeichen eines Wertes, der in einer Zelle dieser Spalte gespeichert wird, 3 Bytes Speicherplatz benötigt.
Für Spalten mit den Datentypen "CHAR" und "VARCHAR" hängt es also von der Länge der in der Spalte zugelassen Werte ab, ob die Werte vollständig in die Indextabelle aufgenommen werden können oder nicht. Es ist allerdings speichertechnisch ungünstig, wenn tatsächlich jeweils die maximal mögliche Anzahl an Bytes für die Indizierung ausgeschöpft werden soll. Oftmals reicht es für die Optimierung von Suchvorgängen aus, nur die ersten X Zeichen der Werte zu berücksichtigen und in der Indextabelle zu notieren. Je geringer der Wert X ist, desto geringer ist der Speicherbedarf. Nur den Beginn der Werte in der Indextabelle zu speichern, ist die einzige Möglichkeit, jene Spalten zu indizieren, die längere Bytefolgen aufnehmen können, als in der Indextabelle gespeichert werden können. Auch für Spalten mit einer Variante der Datentypen "BLOB" und "TEXT" ist nur diese Art der Indizierung möglich.

Wird nur der Beginn des Wertes in der Index-Tabelle gespeichert, kann mit dem Index nicht gewährleistet werden, dass in der indizierten Spalte keine Duplikate enthalten sind. Daher können Spalten, von deren Werten nur der Beginn in der Index-Tabelle gespeichert werden, niemals einem eindeutigen Index sondern immer nur einem einfachen Index zugeordnet werden. Bei einem einspaltigen, eindeutigen Index dagegen muss jeder Datensatz der Tabelle in der Indexspalte einen anderen Wert enthalten. Und bei mehrspaltigen, eindeutigen Indizes muss jeder Datensatz eine andere Wertekombination in den Indexspalten aufweisen.

Für eine Datenbanktabelle können beliebig viele Indizes definiert werden. Für jede Spaltenkombination können in MySQL beliebig viele eindeutigen Indizes und beliebig viele einfachen Indizes erzeugt werden. Sinnvoll ist es allerdings, zu jeder Spaltenmenge, die zu einer Datenbanktabelle gebildet werden kann, höchstens jeweils einen Index zu bilden. Denn jede Indextabellen belegt Speicherplatz. Und wenn mehrere strukturell identischen Indizes vorhanden sind, kann dieses u.U. bei Aktionen zu Warnmeldungen oder sogar Fehlern führen. Ob der Index über die Spalten einfach oder eindeutig sein soll, hängt davon ab, ob in den Indexspalten Duplikate erlaubt sind oder nicht.

zum Anfang

Fremdschlüsselbeziehungen

Informationen, die zu einem Vorgang/Objekt der realen Welt gehören, werden für die Datenverarbeitung oftmals aufgesplittet und auf Datensätze in mehreren Datenbanktabellen verteilt. Dadurch ist es möglich, dass ein Informationsteil, der bei mehrern Objekten identisch ist (z.B. die Adresse von allen Mitgliedern einer Familie), nur einmal gespeichert wird. Dies spart zum einen Speicherplatz und zum anderen erleichtert es die Pflege der Information, da z.B. bei einem Umzug der Familie nur ein Adressdatensatz zu ändern ist und nicht für jedes Familienmitglied die Adressänderung einzeln eingetragen werden muss. Außerdem wird so sichergestellt, dass für die Familienmitglieder nicht versehentlich unterschiedliche Adressinformationen gespeichert sind. Das Aufspalten der Information setzt aber voraus, dass die Datensätze aus den verschiedenen Datenbanktabellen miteinander verknüpft werden können. Dazu dienen die Fremschlüsselbeziehungen.

An einer normalen Fremdschlüsselbeziehung sind immer genau zwei Datenbanktabellen (TI und TV) und in TI und TV die gleiche Anzahl Spalten beteiligt. Es können auch Fremschlüsselbeziehungen innerhalb einer Datenbanktabelle defniert werden, TI kann also auch gleichzeitig TV sein.

Über identische Werte in einer Spalte von TI und TV bei einer einspaltigen Verknüpfung (bzw. identischen Wertekombinationen in den beteiligten Spalten bei einer mehrspaltigen Verknüpfung) können die Informationsteile aus den Datensätzen der zwei Datenbanktabellen TI und TV wieder zu einem Informationsobjekt zusammengefügt werden. TI und TV müssen die selbe ENGINE haben und in TI und TV müssen zueinander passende Indizes definiert worden sein, mit denen gewährleistet ist, dss nur identische Werte verknüpft werden. Daher sind für Fremdschlüsselbeziehungen nur solche Indizes erlaubt, die jeweils den gesamten Zelleninhalt berücksichtigen und nicht nur die erstesn ax Zeichen einer Zelle.

Damit eine Fremdschlüsselbeziehung zwischen TI und TV hergestellt werden kann, muus in der Datenbanktabelle TI zuerst ein Index definiert werden. Dazu wird mindestens eine Spalte aus TI ausgewählt, der INDEX-Typ passend zu dem Spaltentyp (bei mehrspaltigen Indizes zu den Typen der ausgewählten Spalten) festgelegt und dem Index ein Name zugewiesen. Weil eine Fremdschlüsselbeziehung voraussetzt, dass identische Wertekombinationen in den verknüpften Datensätzen vorhanden sind und über den Index die Suche gesteuert wird, darf für jeden Index, der für einen Fremdschlüssel verwendet werden soll, nicht festgelegt werden, dass nur die ersten X Zeichen für den Index berücksichtigt werden sollen. Der Index kann eindeutig sein, muss es aber nicht. In den meisten Fällen dürfte aber ein eindeutiger Index sinnvoll sein.

Nachdem der Index in TI definiert wurde, kann in der Datenbanktabelle TV die Fremdschlüsselbeziehung definiert werden. Dazu werden in TV mindestens eine und maximal so viele Spalten für den Fremdschlüssel ausgewählt, wie der Index in TI enthält. Die Spalte 1 des Fremdshlüssels (in TV) muss hinsichtlich des Datentyps mit dem Datentyp der ersten Spalte im Index (aus TI), der für die Fremdschlüsselbeziehung verwendet werden soll, übereinstimmen. Die erforderliche Übereinstimmung betrifft nicht nur den Wertetyp (int, varschar, ...) sondern auch die Wertetyp möglichen/nötigen ergänzenden Eigenschaften (z.B. bei varchar: Länge der Zeichenkette und Zeichnkodierung, bei int-Typen: mit oder ohne Vorzeichen) Falls eine mehrspaltige Verknüpfung erfolgen soll, muss auch der Datentyp der zweiten Spalte im Fremdschlüssel mit dem Datentyp der zweiten Spalte im Index übereinstimmen. Analoges gilt auch für sämtliche weitere Spalten des Fremdschlüssels. Wenn in der Datenbanktabelle TV ein Index enthalten ist, welcher genau die gleiche Spaltenkombination aufweist, wie der Fremdschlüssel, der definiert werden soll, wird dieser Index dem Fremdschlüssel zugeordnet. Ist kein passender Index in TV vorhanden, erzeugt MySQL automatisch einen Index für TV und weist diesem Index den Namen zu, welcher auch für die Fremschlüsselbeziehung eingetragen wird. Der automatisch erzeugte Index ist ein einfacher Index.

Es ist meist sinnvoll, zwischen zwei Tabellen A und B nur eine Fremdschlüsselbeziehung zu definieren. Es dürfen zwar auch mehr definiert werden, die sich ergänzen, doch deutet die Verwendung mehrerer Fremdschlüsssel zwischen zwei Tabellen A und B darauf hin, dass die Struktur der Datenbanktabelle nicht optimal definiert ist. Damit sich bei mehreren Fremsdschlüsselbeziehungen zwischen A und B die Beziehungen nicht gegenseitig behindern, sollte immer nur entweder A als TI und B als TV verwendet werden oder umgekehrt. Wenn sowohl TI-A mit TV-B und TI-B mit TV-A gelten würde, läge ein Zirkelschluss vor, der dazu führen kann, dass die Fremdschlüsselbeziehungen das Einfügen, Verändern und/oder Löschen von Datensätzen in den Tabellen A und B verhindern.
Auch bei der Verknüpfung von mehr als zwei Tabellen ist darauf zu achten, dass kein Zirkelschluss entsteht.

zum Anfang

Steuerung der Änderungen an Datensätzen durch Fremdschlüsselbeziehungen

Zur sprachlichen Vereinfachung der Aussagen wird nachfolgend nur das Verhalten bei einem einspaltigen Index/Fremdschlüssel beschrieben, das gleiche Verhalten tritt auch bei mehrspaltigen Fremdschlüsselbeziehungen auf. Die Indexspalte aus der Datenbanktabelle TI wird nachfolgend mit TIS bezeichnet, jene aus der Datenbanktabelle TV als TVS.

Es ist dabei zu unterscheiden, ob es sich umen einen INSERT-, UPDATE- oder DELETE-Vorgang handelt und ob der Vorgang für einen Datensatz in TI oder TV ausgeführt werden soll.

• INSERT-Vorgang
  • in TI
    • Die Fremdschlüsselbeziehung hat keine Auswirkung auf das Einfügen von Datensätzen in TI.
  • in TV
    • Wenn in TVS keine NULL-Werte erlaubt sind, kann ein Datensatz in TV nur dann eingefügt werden, wenn für TVS ein Wert gewählt wird, welcher mindestens einem Datensatz aus TI in der Spalte TIS zugeordnet ist.
    • Falls in TVS auch der NULL-Wert zulässig, kann ein Datensatz in TV außerdem dann eingefügt werden, wenn für TVS der NULL-Wert gwählt wird.
• UPDATE-Vorgang
  Ein UPDATE-Befehl enthält immer eine SET-Anweisung und kann eine WHERE-Bedingung enthalten, wenn der UPDATE-Befehl nicht für alle Datensätze der Tabelle gelten soll.
  • in TI
    • Die Fremdschlüsselbeziehung hat keine Auswirkung auf das Einfügen von Datensätzen in TI.
  • in TV
    • Wenn TVS in der SET-Anweisung oder der WHERE-Bedingung des UPDATE-Vorgangs enthalten ist, wird der UPDATE-Befehl nur ausgeführt, wenn für TVS ein Wert gewählt wurde, welcher mindestens einem Datensatz aus TI in der Spalte TIS zugeordnet ist oder für TVS der NULL-Wert zulässig und verwendet wird.
• DELETE-Vorgang
  • aus TI
    • Die Fremdschlüsselbeziehung hat keine Auswirkung auf das Einfügen von Datensätzen in TI.
  • aus TV
    • Jeder Datensatz kann aus TV gelöscht werden.

Während UPDATE-Vorgänge über die Für INSERT-Vorgänge

Bei jeder Fremdschlüsselbedingung muss immer festgelegt werden, wie sich die verknüpften Datensätze in den Tabelle TI und TV verhalten sollen, wenn der verknüpfte Datensatz aus der Tabelle TI gelöscht (DELETE_RULE) oder dessen Wert in der Indexspalte der Beziehung verändert (UPDATE_RULE) werden soll.

Es gibt folgende vier Regeln, mit denen sowohl UPDATE- als auch DELETE-Vorgänge beeinflusst werden können: CASCADE, RESTRICT, SET NULL, NO ACTION

CASCADE führt dazu, dass die Änderung an dem Indexwert des Datensatzes in der Tabelle TI sich auf die verknüpften Datensätze in der Tabelle TV überträgt. Bei einem UPDATE-Vorgang wird daher der Wert für alle verknüpften Datensätze aus TV in der Fremdschlüsselspalte ebenfalls verändert, und bei einem DELETE-Vorgang werden dann auch die verknüpften Datensätze aus der Tabelle TV gelöscht.

RESTRICT führt dazu, dass die Änderung an dem Indexwert des Datensatzes in der Tabelle TI nicht möglich ist bzw. der Datensatz aus der Tabelle TI nicht gelöscht werden kann, wenn einer oder mehrere Datensätze aus der Tabelle TV in der Fremdschlüsselspalte den gleichen Wert aufweist, wie der Datensatz in der Tabelle TI, der verändert oder gelöscht werden soll.

SET NULL führt dazu, dass für die verkünpften Datensätze der Tabelle TV der Wert in der Fremschlüsselspalte zu NULL (bzw. 0) verändert wird, wenn der verknüpfte Datensatz aus der Tabelle TI gelöscht wird oder dessen Index-Wert verändert wird. Dies ist allerdings nur möglich, wenn die Fremdschlüsselspalte in der Tabelle TV die Eigenschaft "IS_NULLABLE" (= Null-Wert erlaubt) besitzt.

NO ACTION führt in MySQL-Datenbanken zum gleichen Verhalten wie das Schlüsselwort RESTRICT, da MySQL die Fremdschlüsselbeziehungen immer sofort prüft, wenn eine Aktion auf einen Datensatz ausgeführt werden soll.
Nach Standard-SQL soll mit NO ACTION bei jenen Datenbanken, welche Fremdschlüsselbeziehungen erst nach einer Aktion auswerten, ermöglicht werden, dass die Änderung am Datensatz der Tabelle TI bzw. das Löschen des Datensatzes aus TI zu keinerlei Veränderungen an den zuvor verknüpften Datensätzen der Tabelle TV führt. Nach dem Löschen oder dem Ändern des Datensatzes in TI kann es daher in der Tabelle TV Datensätze geben, welche in der Fremdschlüsselspalte einen Wert enthalten, der in der Indexspalte der Tabelle TI nicht mehr vorkommt. Die Regel verhindert bei diesen Datenbanken aber, dass in die Tabelle TV neue Datensätze eingefügt werden können, bei denen in der Fremdschlüsselspalte ein Wert eingetragen werden soll, der zu keinem Datensatz aus TI in der Indexspalte gehört.

zum Anfang

zum Anfang

zum Anfang