5. Beziehungen zwischen Tabellen

5.1. Fremdschlüssel

Eine relationale Datenbank ist eine Sammlung von Tabellen, die durch Beziehungen miteinander verknüpft sind. Nehmen wir ein Beispiel, das von der vorherigen [BIBLIO]-Tabelle inspiriert ist, die folgende Struktur hatte:

Ein Beispiel für den Inhalt sah wie folgt aus:

Möglicherweise benötigen Sie Informationen zu den verschiedenen Autoren dieser Werke, wie beispielsweise deren Vor- und Nachnamen, Geburtsdatum und Nationalität. Erstellen wir eine solche Tabelle. Klicken Sie mit der rechten Maustaste auf [DBBIBLIO / Tables] und wählen Sie die Option [New Table]:

Erstellen wir nun die folgende Tabelle [AUTHORS]:

`id`	Primärschlüssel der Tabelle – dient zur eindeutigen Identifizierung einer Zeile
`Nachname`	Nachname des Autors
`Vorname`	Vorname des Autors, falls zutreffend
`Geburtsdatum`	Geburtsdatum
`Staatsangehörigkeit`	Herkunftsland

Der Inhalt der Tabelle [AUTHORS] könnte wie folgt aussehen:

Kehren wir zur Tabelle [BIBLIO] und ihrem Inhalt zurück:

In der Spalte [AUTHOR] der Tabelle ist es nicht mehr notwendig, den Namen des Autors einzugeben. Stattdessen ist es vorzuziehen, die ID-Nummer einzugeben, die ihm in der Tabelle [AUTHORS] zugewiesen wurde. Erstellen wir eine neue Tabelle namens [BOOKS]. Dazu verwenden wir das in Abschnitt 3.14 erstellte Skript [biblio.sql]. Wir führen dieses Skript mit dem Tool [Script Executive, Strg-F12] aus:

Wir passen das Skript zum Erstellen der Tabelle BIBLIO an die Tabelle BOOKS an:

CREATE TABLE LIVRES (
ID          INTEGER NOT NULL,
TITRE       VARCHAR(30) NOT NULL,
AUTEUR      INTEGER,
GENRE       VARCHAR(30) NOT NULL,
ACHAT       DATE NOT NULL,
PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
DISPONIBLE  CHAR(1) NOT NULL
);

INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (2, 'Les fleurs du mal', 8, 'POèME', '1978-01-01', 120, 'n');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (3, 'Tintin au Tibet', 6, 'BD', '1990-11-10', 70, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (4, 'Du côté de chez Swann', 3, 'ROMAN', '1978-12-08', 220.5, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (5, 'La terre', 4, 'ROMAN', '1990-06-12', 55.13, 'n');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (6, 'Madame Bovary', 1, 'ROMAN', '1988-03-12', 143.33, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (7, 'Manhattan transfer', 7, 'ROMAN', '1987-08-30', 352.8, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (8, 'Tintin en Amérique', 6, 'BD', '1991-05-15', 70, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (10, 'Le père Goriot', 5, 'Roman', '1991-09-01', 210, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (11, 'Poèmes saturniens', 2, 'Poème', '1992-09-02', 200, 'o');

COMMIT WORK;



/******************************************************************************/
/****                          Unique Constraints                          ****/
/******************************************************************************/

ALTER TABLE LIVRES ADD CONSTRAINT UNQ1_LIVRES UNIQUE (TITRE);


/******************************************************************************/
/****                             Primary Keys                             ****/
/******************************************************************************/

ALTER TABLE LIVRES ADD CONSTRAINT PK_LIVRES PRIMARY KEY (ID);

Wir werden nur auf die Änderungen eingehen:

Zeile 4: Die Spalte [AUTHOR] in der Tabelle wird zu einem Integer-Wert. Diese Nummer verweist auf einen der Autoren in der zuvor erstellten Tabelle [AUTHORS].
Zeilen 11–19: Die Namen der Autoren wurden durch ihre Autoren-IDs ersetzt.
Zeile 29: Der Name der Einschränkung wurde geändert. Zuvor hieß sie [UNQ1_BIBLIO]. Jetzt heißt sie [UNQ1_LIVRES]. Dieser Name kann beliebig gewählt werden. Es ist jedoch vorzuziehen, dass er aussagekräftig ist. Hier wurde darauf nicht geachtet. Einschränkungen auf verschiedene Felder und verschiedene Tabellen innerhalb einer Datenbank müssen durch unterschiedliche Namen voneinander unterschieden werden. Zur Erinnerung: Die Einschränkung in Zeile 29 verlangt, dass ein Titel innerhalb der Tabelle eindeutig ist.
Zeile 36: Ändern Sie den Namen der Einschränkung für die Primärschlüssel-ID.

Führen wir dieses Skript aus. Wenn es erfolgreich ist, erhalten wir die folgende neue Tabelle [BOOKS]:

Man könnte sich fragen, ob wir am Ende tatsächlich etwas gewonnen haben. Tatsächlich listet die Tabelle [BOOKS] Autorennummern anstelle ihrer Namen auf. Da es Tausende von Autoren gibt, scheint es schwierig, ein Buch mit seinem Autor zu verknüpfen. Glücklicherweise hilft uns SQL dabei. Es ermöglicht uns, mehrere Tabellen gleichzeitig abzufragen. Für dieses Beispiel stellen wir die SQL-Abfrage vor, mit der wir die Titel der Bücher in der Bibliothek zusammen mit den Informationen zu ihren Autoren abrufen können. Verwenden wir den SQL-Editor (F12), um die folgende SQL-Anweisung auszuführen:

SQL> select LIVRES.titre, AUTEURS.nom, AUTEURS.prenom,AUTEURS.date_naissance
FROM LIVRES inner join AUTEURS on LIVRES.AUTEUR=AUTEURS.ID
ORDER BY AUTEURS.nom asc

Es ist noch zu früh, diese SQL-Abfrage zu erklären. Wir werden gleich darauf zurückkommen. Das Ergebnis dieser Abfrage lautet wie folgt:

Jedes Buch wurde korrekt seinem Autor und den zugehörigen Informationen zugeordnet.

Fassen wir zusammen, was wir gerade gemacht haben:

Wir haben zwei Tabellen, die unterschiedliche Arten von Informationen enthalten:
- Die Tabelle AUTHORS enthält Informationen über Autoren
- Die Tabelle BOOKS enthält Informationen über die von der Bibliothek erworbenen Bücher
Diese Tabellen sind miteinander verknüpft. Ein Buch muss einen Autor haben. Es kann sogar mehrere haben. Dieses Szenario wurde hier nicht berücksichtigt. Das Feld [AUTHOR] in der Tabelle [BOOKS] verweist auf eine Zeile in der Tabelle [AUTHORS]. Dies wird als Beziehung bezeichnet.

Die Beziehung zwischen der Tabelle [BOOKS] und der Tabelle [AUTHORS] ist eigentlich eine Art von Einschränkung: Eine Zeile in der Tabelle [BOOKS] muss immer eine Autoren-ID haben, die in der Tabelle [AUTHORS] vorhanden ist. Wenn eine Zeile in [BOOKS] eine Autoren-ID hätte, die in der Tabelle [AUTHORS] nicht existiert, befänden wir uns in einer abnormalen Situation, in der wir den Autor eines Buches nicht finden könnten.

Das DBMS ist in der Lage, sicherzustellen, dass diese Einschränkung stets erfüllt ist. Dazu fügen wir der Tabelle [BOOKS] eine Einschränkung hinzu:

Die Verknüpfung zwischen der Spalte [AUTHOR] in der Tabelle [BOOKS] und dem Feld [ID] in der Tabelle [AUTHORS] wird als Fremdschlüsselbeziehung bezeichnet. Das Feld [AUTHOR] in der Tabelle [BOOKS] wird im obigen Assistenten als „Fremdschlüssel“ bezeichnet. Die Definition eines Fremdschlüssels bedeutet, dass der Wert einer Spalte [c1] in einer Tabelle [T1] in der Spalte [c2] der Tabelle [T2] vorhanden sein muss. Die Spalte [c1] wird als „Fremdschlüssel“ der Tabelle T1 auf die Spalte [c2] der Tabelle [T2] bezeichnet. Die Spalte [c2] ist oft der Primärschlüssel der Tabelle [T2], dies ist jedoch nicht zwingend erforderlich.

Wir definieren den Fremdschlüssel [AUTHOR] der Tabelle [BOOKS] auf das Feld [ID] der Tabelle [AUTHORS] wie folgt:

Einschränkungsname: frei
„Fremdschlüssel“-Spalte, hier die Spalte [AUTHOR] der Tabelle [BOOKS]
Tabelle, auf die der Fremdschlüssel verweist. Hier muss die Spalte [AUTHOR] in der Tabelle [BOOKS] einen Wert in der Spalte [ID] der Tabelle [AUTHORS] haben. Daher ist die Tabelle [AUTHORS] die referenzierte Tabelle.
Spalte, auf die der Fremdschlüssel verweist. Hier die Spalte [ID] der Tabelle [AUTHORS].

Wir validieren diese Einschränkung:

Wenn alles gut läuft, wird es angenommen:

Was ist die Folge dieser neuen Fremdschlüsselbeschränkung? Versuchen wir mit dem SQL-Editor (F12), eine Zeile in die Tabelle BOOKS mit einer nicht existierenden Autoren-ID einzufügen:

Der obige [INSERT]-Befehl versuchte, ein Buch mit einer nicht existierenden Autoren-ID (100) einzufügen. Die Ausführung der Abfrage schlug fehl. Die zugehörige Fehlermeldung weist darauf hin, dass ein Verstoß gegen die Fremdschlüsselbeschränkung „FK_BOOKS_AUTHORS“ vorliegt. Dies ist diejenige, die wir gerade definiert haben.

5.2. Join-Operationen zwischen zwei Tabellen

Lassen Sie uns, immer noch in der Datenbank [DBBIBLIO] (oder einer beliebigen anderen Datenbank), zwei Testtabellen namens TA und TB erstellen und diese wie folgt definieren:

Tabelle TA

- ID: Primärschlüssel der Tabelle TA

- DATA: beliebige Daten

Tabelle TB

- ID: Primärschlüssel der TB-Tabelle

- IDTA: Fremdschlüssel der TB-Tabelle, der auf die ID-Spalte der TA-Tabelle verweist. Daher muss ein Wert aus der IDTA-Spalte der TA-Tabelle in der ID-Spalte der TA-Tabelle vorhanden sein

- VALUE: beliebige Daten

Im SQL-Editor (F12) führen wir SQL-Anweisungen aus, die gleichzeitig sowohl die TA- als auch die TB-Tabelle verwenden.

SQL>select * from TA,  TB

Die SQL-Anweisung verwendet das Schlüsselwort FROM, um auf die Tabellen TA und TB zu verweisen. Die Operation FROM TA, TB bewirkt, dass eine neue temporäre Tabelle erstellt wird, in der jede Zeile der Tabelle TA mit jeder Zeile der Tabelle TB verknüpft wird. Wenn die Tabelle TA also NA Zeilen und die Tabelle TB NB Zeilen enthält, hat die resultierende Tabelle NA x NB Zeilen. Dies ist im obigen Screenshot dargestellt. Darüber hinaus enthält jede Zeile die Spalten aus beiden Tabellen. Die in der Reihenfolge [SELECT col1, col2, ... FROM ...] angegebenen Spalten geben an, welche Spalten einbezogen werden sollen. Hier bedeutet das Schlüsselwort *, dass alle Spalten der resultierenden Tabelle angefordert werden. Die aus der vorherigen SQL-Anweisung resultierende Tabelle wird manchmal als kartesisches Produkt der Tabellen TA und TB bezeichnet.

Oben wurde jede Zeile der Tabelle TA mit jeder Zeile der Tabelle TB verknüpft. Im Allgemeinen möchten wir eine Zeile von TB mit einer Zeile von TA verknüpfen, die eine Beziehung zu ihr hat. Diese Beziehung nimmt oft die Form einer Fremdschlüsselbeschränkung an. Dies ist hier der Fall. Mit einer Zeile in der Tabelle TA können wir die Zeilen in der Tabelle TB verknüpfen, die die Beziehung TB.IDTA=TA.ID erfüllen. Es gibt mehrere Möglichkeiten, dies abzufragen:

SQL>select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA, TB where TA.ID=TB.IDTA

Die vorstehende SQL-Anweisung ähnelt der vorherigen, weist jedoch zwei Unterschiede auf:

Die Ergebniszeilen des kartesischen Produkts TA x TB werden durch eine WHERE-Klausel gefiltert, die einer Zeile in der Tabelle TA nur jene Zeilen in der Tabelle TB zuordnet, die die Beziehung TB.IDTA=TA.ID erfüllen
Es werden nur bestimmte Spalten mit der Syntax [T.col] abgefragt, wobei T der Name einer Tabelle und col der Name einer Spalte in dieser Tabelle ist. Diese Syntax beseitigt jegliche Mehrdeutigkeit, die entstehen könnte, wenn zwei Tabellen Spalten mit demselben Namen hätten. Wenn diese Mehrdeutigkeit nicht besteht, kann die Syntax [col] verwendet werden, ohne die Tabelle für diese Spalte anzugeben.

Das Ergebnis lautet wie folgt:

Das gleiche Ergebnis lässt sich mit der folgenden SQL-Anweisung erzielen:

SQL>select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA inner join TB on TA.ID=TB.idta

Der Begriff [inner join] führt zu der Bezeichnung „inner join“, die dieser Art von Operation zwischen zwei Tabellen gegeben wird. Wir werden sehen, dass es auch einen „outer join“ gibt. Bei einem inner join hat die Reihenfolge der Tabellen in der Abfrage keinen Einfluss auf das Ergebnis: FROM TA inner join TB ist gleichbedeutend mit FROM TB inner join TA.

Die vorstehende SQL-Reihenfolge nimmt in die Ergebnismenge nur jene Zeilen aus der Tabelle TA auf, auf die mindestens eine Zeile aus der Tabelle TB verweist. Daher erscheint die Zeile [3, data3] in TA nicht im Ergebnis, da sie von keiner Zeile in TB referenziert wird. Möglicherweise möchten Sie alle Zeilen aus TA, unabhängig davon, ob sie von einer Zeile in TB referenziert werden oder nicht. In diesem Fall verwenden Sie einen left outer join zwischen den beiden Tabellen:

SQL> select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA left outer join TB on TA.ID= TB.IDTA

Hier haben wir einen Left Outer Join. Um den Begriff „FROM TA left outer join TB“ zu verstehen, stellen Sie sich eine Verknüpfung vor, bei der die Tabelle TA links und die Tabelle TB rechts steht. Alle Zeilen aus der linken Tabelle erscheinen im Ergebnis eines Left Outer Joins, auch diejenigen, bei denen die Verknüpfungsbedingung nicht erfüllt ist. Diese Verknüpfungsbedingung ist nicht unbedingt eine Fremdschlüsselbeschränkung, obwohl dies der häufigste Fall ist.

In der folgenden Reihenfolge:

SQL> select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TB left outer join TA on TA.ID= TB.IDTA

Die TB-Tabelle befindet sich auf der „linken“ Seite des Outer Joins. Daher erscheinen alle Zeilen aus TB im Ergebnis:

Im Gegensatz zu einem Inner Join spielt hier die Reihenfolge der Tabellen eine Rolle. Es gibt auch Right Outer Joins:

FROM TA LEFT OUTER JOIN TB entspricht FROM TB RIGHT OUTER JOIN TA: Die Tabelle TA steht links
FROM TB LEFT OUTER JOIN TA entspricht FROM TA RIGHT OUTER JOIN TB: Die TB-Tabelle steht links

Nachdem wir nun die Grundlagen der gleichzeitigen Abfrage mehrerer Tabellen verstanden haben, können wir zu komplexeren Datenbankabfragen übergehen.