Was versteht man unter einen Fremdschlüssel (englisch "Foreign Key" oder kurz FK) und welche verschiedenen Arten davon gibt es? Was sind dann denn PK und AK?

Grundlagen

Primärschlüssel - Primary Key (PK)

Mit einem Primärschlüssel werden alle Datensätze eindeutig identifizierbar. Ein Primärschlüssel besteht aus einer oder mehreren Spalten. Beim Erstellen von Datensätzen muss der Primärschlüssel eindeutig gefüllt werden. NULL-Werte sind hier nicht zulässig.

Beispiel

Gegeben sei eine Relation Parent. Für diese verwenden wir einen künstlichen Schlüssel (Surrogate Key) mit dem Namen Parent_ID. Das Feld Parent_ID identifiziert also eindeutig alle Sätze in der Tabelle und kann nicht NULL sein.

Alternativer Eindeutiger Schlüssel - Alternate Key (AK)

Gelegentlich kommt es vor, dass in einer Tabelle ein weiteres Feld ausser dem Primärschlüssel ebenfalls eindeutig sein soll. Dies kann aber auch für die Kombination von mehreren Feldern zutreffen. Diese Eindeutigkeit bildet man über einen Alternate Key (AK) ab.

Beispiel

Jede Person in der Tabelle Parent bekommt eine Sozialversicherungsnummer (Social_ID) als eindeutiges Identifikationsmerkmal. Das von mir verwendete Tool (CA Erwin) zeigt zu den Alternate Key Feldern an, zu welchem AK sie gehören, und an welcher Stelle sie im Key vorkommen. AK1.1 bedeutet also "Erster Alternate Key.Erstes Feld". AK1.2 ist das zweite Feld usw.

Fremdschlüssel - Foreign Key (FK)

Fremdschlüssel dienen zur Verknüpfung von Tabellen und beziehen sich auf den PK oder AK einer anderen Tabelle oder derselben Tabelle. Dadurch werden die zwei Relationen voneinander abhängig. Wird ein Fremdschlüssel angelegt, können in die untergeordnete Tabelle nur noch Werte eingesetzt werden, für die bereits in der übergeordneten Relation Sätze mit diesem PK oder AK vorhanden sind.

Hierbei unterscheidet man zwischen identifizierenden Beziehungen (identifying relationship) und nicht identifizierenden Beziehungen (non identifying relationship).

Beispiel mit identifying relationship

Es gibt eine weitere Tabelle Child, die von der Tabelle Parent deren Primärschlüssel als eigenen Primärschlüssel erhält. Damit man aber nicht nur einen Satz dort unterbringen kann, wird ein weiteres Feld zur Nummerierung der Kinder eingeführt. Gemeinsam bilden diese beiden Felder den Primärschlüssel der Relation Child.

Beispiel mit non identifying relationship

Falls wir weiterhin auf Surrogatschlüssel setzen, würde die Relation Child also eine Spalte Child_ID erhalten. Dann würden wir die Beziehung zur Relation Parent über eine non identifying relationship abbilden. Die Kombination von Parent_ID und Child_Number muss aber auch eindeutig sein, so dass wir hierüber einen Alternate Key definieren.

Beispiel mit non identifying relationship über Alternate Key

Wir wollen eine weitere Relation Gender für das Geschlecht einführen und dort ebenfalls einen Surrogatschlüssel als Primärschlüssel verwenden. Dieser könnte z. B. die Werte 1 (männlich), 2 (weiblich), 3 (unbekannt) annehmen. Die Spalte Gender würde aber direkt eine lesbare Abkürzung enthalten (m, w, n). Nun wollen wir nicht in allen abhängigen Tabellen die ID abspeichern, sondern direkt die lesbare Abkürzung. Hierzu definieren wir das Feld Gender als Alternate Key in der Tabelle Gender. Dann bilden wir eine Beziehung anhand dieses Alternate Keys auf die anderen Tabellen und reichen somit nicht die ID, sondern bereits die Abkürzung weiter. Immer wiederkehrende Joins bei Auswertungen können damit entfallen.

Constraints

Die oben beschriebenen Beziehungen werden in der Datenbank als Constraints angelegt (ALTER TABLE <table> ADD CONSTRAINT ...). Es gibt entsprechend Constraints für

• Primärschlüssel ( <name> PRIMARY KEY <column(s)> )
• Alternative Schlüssel ( <name> UNIQUE <column(s)> )
• Fremdschlüssel ( <name> FOREIGN KEY <column(s)> REFERENCES <table><column(s)> ).

In der Folge kümmert sich die Datenbank um die Einhaltung dieser Beziehungen. Änderungen, Einfügungen oder Löschungen, die gegen diese Beziehungen verstoßen würden, werden abgewiesen. Falls in der Software die Datenprüfung nicht hundertprozentig funktioniert (wo tut sie das schon), dann hat man hier noch einen Rückhalt, der inkonsistente Daten verhindert.

Kardinalität von Beziehungen

Hierüber wird beschrieben, wieviel Sätze der einen Tabellen mit welcher Anzahl von Sätzen in der anderen Tabelle in Beziehung stehen. Hier kommt nur eine kleine Auswahl der häufigsten Kardinalitäten. Mehr dazu findet man u. a. in diesem Artikel über Datenmodellierung

1:0,1

Zu jedem Satz der Parent-Tabelle kann es 0 oder 1 Satz in der Child-Tabelle geben. Man würde dies ggf. einsetzen, wenn man nur für wenige Sätze eine Reihe von weiteren beschreibenden Feldern füllen möchte, aber die Parent-Tabelle damit nicht unbedingt aufblähen möchte, da der Großteil der Sätze nicht über diese Informationen verfügt.

1:n

Das ist die gängige Praxis. Zu einem Satz in der Parent-Tabelle gibt es eine Reihe von Sätzen in der Child-Tabelle. Ein typisches Beispiel ist ein Auftragskopf mit Positionen. Falls man auch Aufträge ohne Positionen zulassen möchte, verwendet man eine 1:0,n Kardinalität.

n:m

Dies ist keine direkte Beziehung zwischen Sätzen in Tabellen, sondern muss zuerst abstrakt als Beziehung zwischen Objekten gesehen werden. Ein Artikel kann von mehreren Lieferanten geliefert werden. Jeder Lieferant kann mehrere Artikel liefern.

Um dies abzubilden, verwendet man eine zusätzliche Verknüpfungstabelle zwischen Artikel und Lieferant.

Muss man Fremdschlüssel oder UNIQUE-Constraints definieren?

Ich habe immer wieder mit Software-Entwicklern zu tun, die sagen: Wir brauchen so etwas nicht in der Datenbank, wir haben alles in der Applikationsschicht. Business-Logic soll nicht ausserhalb der Software implementiert werden.

Nun, meiner Meinung nach gehören die Tabellen auch mit zur Software-Entwicklung und stehen nicht isoliert im Raum. Ausserdem gibt es immer wieder Fälle, in denen die Business Logic doch nicht an allen Stellen sauber und einheitlich implementiert wird. Und dann kommen früher oder später auch noch Tools von Drittanbietern ins Spiel, die zum einen froh sind anhand der Beziehungen dem Anwender Unterstützung für z. B. Reporting zu geben, zum anderen aber auch sich überhaupt nicht um die in der Software implementierte Business Logic scheren und ggf. die Datenkonsistenz beschädigen, wenn keine Constraints auf der Datenbank dies im letzten Moment verhindern.

Was sollte man bei Fremdschlüsseln beachten?

Foreign-Key-Constraints sind erst mal keine Indizes, sondern sagen nur etwas über die Beziehungen zwischen Relationen aus.

Datenmodellierungstools schlagen in der Regel vor, Indizes für diese Fremdschlüsselbeziehungen anzulegen und das hat seinen guten Grund. Wir stellen uns beispielsweise mal ein Adressverzeichnis mit 5.000 Orten vor. Zu jedem Ort gibt es in einer weiteren Tabelle die Informationen zu den Strassen. Das sind dann vielleicht 500.000 Strassen. Nun soll ein Ort gelöscht werden, da er nach einer Eingemeindung nicht mehr existiert. Die Strassen wurden bereits alle auf den neuen Ort umgebogen. Eine Löschung dieses Ortes würde ein Full Table Scan auf der Strassentabelle auslösen, um herauszufinden ob der Ort noch irgendwo verwendet wird, da der Foreign-Key-Constraint eingehalten werden muss.

Hat man nun aber einen Index über das (Foreign-Key-)Feld Ortsnummer in der Tabelle Strassen angelegt, kann die Datenbank-Engine in dem Index nachschauen, ob es noch Einträge zu diesem Ort gibt. Wir haben also nur noch eine Suche im Index. Diese ist nicht nur wesentlich schneller, sondern verhindert ausserdem einen Haufen unnötiger Sperren auf der Strassentabelle.

Schlusswort

Dieser Artikel ist kein vollständiger Überblick über Datenmodellierung und auch die Beispiele sind mehr zur Veranschaulichung gedacht. Ziel ist es etwas zur Begriffsklärung beizutragen, da es in diesem Bereich immer wieder Missverständnisse gibt.

Ich möchte auch noch auf zwei weitere Artikel hinweisen, die sich ebenfalls mit Primärschlüsseln und Fremdschlüssel-Beziehungen beschäftigen.