4Datendefinition in SQL

4
Datendefinition in SQL
SQL hat sich als Standardabfragesprache für relationale Datenbanken etabliert.
SQL steht ursprünglich für »Structured Query Language«. Die ersten Versuche
dazu wurden in den IBM-Labors vorgenommen und daraus ist die Vorläufersprache SEQUEL entstanden. SQL stellt die Schnittstelle zwischen der relationalen
Datenbank und dem Anwendungsprogramm dar. Die Sprache ist in erster Linie
nicht für Endanwender gedacht, sondern für Systementwickler. Mit SQL lassen
sich alle Operationen der Relationenalgebra realisieren, die in Kapitel 3 eingeführt
worden sind.
4.1 SQL und SQL-Standard
4.1.1 Ein kleiner Überblick über die Historie von SQL
1974
D. Chamberlain et. al. definieren SEQUEL.
1977
Revision: SEQUEL/2; IBM-Prototyp System R.
1977
Oracle beginnt mit der Implementierung von SEQUEL auf Mainframes.
1979
Oracle liefert ein erstes relationales Datenbanksystem mit SQL aus.
1981
IBM liefert SQL/DS aus.
1983
IBM stellt DB2 vor.
1985
Ingres stellt auf SQL um.
Informix stellt auf SQL um.
1986
Sybase wird ausgeliefert.
1986
X/OPEN entscheidet sich für SQL.
1987
Der Standard ISO 9075 Database Language SQL (SQL-86) wird veröffentlicht.
1988
dBASE IV erhält SQL als Abfrage-Sprache.
Adabas erhält SQL.
1989
ISO 9075 Database Language SQL with Integrity Enhancement (SQL-89)
wird veröffentlicht.
SESAM und UDS erhalten SQL.
151
4 Datendefinition in SQL
1992
ISO 9075 Database Language SQL (SQL2 oder SQL-92) wird veröffentlicht.
1993
RDA-Standard (Remote Data Access) der Database Access Group (z.B.
Microsoft ODBC) wird veröffentlicht.
1996
SQL/PSM (Persistent Stored Modules); Datenbankprozeduren werden
normiert (vgl. Kapitel 7.3).
1999
SQL:1999 wird veröffentlicht und löst damit SQL-92 ab.
2003
Eine abermals überarbeitete und teilweise neu strukturierte Version
SQL:2003 löst SQL:1999 ab. Zum Zeitpunkt der Drucklegung dieses
Buchs befindet sich die neue Norm aber noch im Entwurfsstadium und
wird von Pessimisten (oder Realisten) als SQL:200n bezeichnet.
Zwischen dem SQL-Standard und den in kommerziell verfügbaren Datenbanksystemen angebotenen SQL-Implementierungen gibt es Differenzen in beiden Richtungen. Teilweise übersteigt der verfügbare Sprachumfang die Forderungen der
Norm, teilweise werden Forderungen der SQL-Norm nicht erfüllt. Es ist aber
grundsätzlich zu erkennen, dass die Hersteller sich mehr und mehr um die Einhaltung des SQL-Standards bemühen.
Die Standardisierung von SQL ist aus folgenden Gründen wichtig:
왘 Verschiedene RDBMs können nebeneinander im Betrieb existieren. Die Anwen-
dungsentwicklung und Datenbankadministration sollten möglichst wenig voneinander abweichen.
왘 Anwendungen sollen portabel sein, damit man beispielsweise auf ein leis-
tungsfähigeres DBMS umsteigen kann oder damit die Anwendungsentwicklung in einer anderen Umgebung erfolgen kann als der produktive Einsatz.
왘 Herstellerunabhängigkeit ist gefordert.
왘 Die Verfügbarkeit von »Front-Endsystemen«, also Programmen, die hersteller-
unabhängig auf Datenbanken zugreifen können, nimmt zu.
왘 In Client-Server-Umgebungen kommuniziert die Anwendersoftware über
Schnittstellen mit dem DBMS.
왘 Bei der Anwendungsentwicklung mit CASE-Tools erfolgt die Festlegung von
Daten- und Programmstrukturen über weite Strecken unabhängig vom eingesetzten DBMS.
4.1.2 Elemente von SQL
Die Sprachelemente von SQL lassen sich in zwei Kategorien unterteilen, die allerdings im Standard nicht festgeschrieben sind:
152
SQL und SQL-Standard
DDL (Data Definition Language)
Hierzu gehören:
왘 Anweisungen zur Anlage und Verwaltung von Schemata (Zusammenfassung
von Tabellen, Datensichten etc.), für die ein bestimmter Benutzer zuständig ist
왘 Anweisungen zur Definition von Domänen
왘 Anweisungen zur Definition von Relationen einschließlich der dazugehörigen
Konsistenzbedingungen
왘 Anweisungen zur Anlage von Datensichten (Views)
왘 Die Verwaltung von Benutzern und deren Datenzugriffsrechten
DML (Data Manipulation Language)
Die DML enthält Anweisungen zur:
왘 Eingabe von Daten in eine vorhandene Tabelle
왘 Änderung von Daten in einer Tabelle
왘 Löschung von Daten in einer Tabelle
왘 Abfrage von Daten, die auch mehrere Tabellen umfassen können
und Anweisungen zur:
왘 Definition und Steuerung von Transaktionen
Weiterhin gehören zum Sprachumfang kommerziell verfügbarer relationaler
Datenbanksysteme Anweisungen, die die interne Organisation der Datenbanken
steuern. Sie sind syntaktisch meist an SQL-Anweisungen angelehnt, unterliegen
aber nicht der Norm, da diese sich nicht auf das interne Schema einer Datenbank
bezieht. Dazu gehören:
왘 Anweisungen zur Definition von Indexen
왘 Erweiterungen der CREATE TABLE-Anweisung im Hinblick auf Varianten der
Ablage auf dem Speichermedium (beispielsweise in »Clustern«)1
SQL ist nicht-prozedural
Die gewünschten Daten werden unter SQL durch Prädikate (logische Bedingungen) charakterisiert. Entscheidend für das Auffinden der Daten, die man ansehen
oder verändern will, ist ihre Beschreibung über Attributwerte – wie kompliziert
diese auch immer sein mag. Ein Beispiel: Zeige von allen Kunden, deren Wohnort
Kayhude ist und die zugleich Stammkunden sind, Kunden_nr, Name und Ort an.
Der »Weg« zu den Daten in Form von Suchalgorithmen etc. wird bei SQL nicht
angegeben. Deshalb ist der Befehl zu obigem Beispiel relativ nahe an der
umgangssprachlichen Formulierung:
1 Das beispielsweise bei ORACLE vorhandene Cluster-Konzept sieht die Speicherung von Tupeln verschiedener Tabellen, die häufig miteinander verbunden werden, physikalisch benachbart auf den Plattensektoren vor, um damit die Anzahl der Zugriffe zu reduzieren.
153
4 Datendefinition in SQL
SELECTkunden_nr, name, ort
FROM kunde
WHERE ort = 'Kayhude' AND status = 'S';
Auch höchst komplexe Abfragen können so gestellt und beantwortet werden, z.B.:
»Zeige alle Kunden, die zwischen dem 1.3.2003 und dem 31.3.2003 einen Umsatz von
mehr als 100 Euro gemacht haben, wobei sie aber keinen Tee gekauft haben dürfen und im
Vorjahr auch schon einmal etwas bestellt haben müssen.«2
SQL verarbeitet grundsätzlich Mengen von Tupeln. Eine Abfrage wie die obige hat
eine Tabelle mit keiner, einer oder beliebig vielen Zeilen als Ergebnis.3 Dies unterscheidet SQL von imperativen Programmiersprachen, die satzweise arbeiten, d.h.
mit einer Anweisung immer nur einen Datensatz zugleich lesen, schreiben, löschen
oder vergleichen können. SQL orientiert sich an der Relationenalgebra. Es muss
aber deutlich festgehalten werden, dass SQL nicht mit Relationen, sondern mit
Tabellen arbeitet. Relationen sind nurmehr ein Spezialfall von Tabellen. Wir haben
in Kapitel 3 gezeigt, dass die Relationenalgebra mit wenigen Modifikationen auf
Tabellen anwendbar ist.4 Es ist in SQL auch jederzeit möglich, eine Datenabfrage so
zu formulieren, dass das Ergebnis eine Relation ist, also keine mehrfach vorkommenden identischen Tupel enthält. Seit 1999 verarbeitet SQL außer Tabellen auch
Strukturen, die als objektrelational bezeichnet werden und für die einige Einschränkungen, wie wir sie in Kapitel 3 formuliert haben, nicht gelten. Mehr dazu
findet sich in Kapitel 9.
Wir beziehen uns im Wesentlichen auf SQL im Sinne des Standards SQL:200n auf
dem Stand von August 2002 (Draft Version). Da zum Zeitpunkt der Manuskripterstellung kein DBMS verfügbar ist, das den Standard vollkommen erfüllt, wird
gegebenenfalls auf Abweichungen und Besonderheiten bei existierenden DBMS
(Sybase, ORACLE, etc.) hingewiesen.
4.2 Schemadefinition
Ein SQL-Schema enthält Datenbankobjekte eines einzelnen Benutzers5 innerhalb
einer Datenbank. Dazu gehören unter anderem von ihm erzeugte Basistabellen,
2 Wir empfehlen dieses Beispiel hier nicht als Übung.
3 Zur Unterscheidung der Begriffe Tupel, Zeile, Attribut, Spalte, Relation und Tabelle vgl. Abschnitt 2.1.
4 Näheres findet sich in Kapitel 2.6.
5 Unter einem Benutzer verstehen wir einen in der Datenbank namentlich eingetragenen, mit bestimmten Zugriffsrechten ausgestatteten Anwender der Datenbank. Benutzer und Person sind
nicht unbedingt identisch. Einer Person können je nach Anwendungszusammenhang verschiedene Benutzernamen zugeordnet sein, beispielsweise einer für die Rolle als Datenbankadministrator und ein anderer für die normale Nutzung der Datenbank. Umgekehrt kann es
manchmal organisatorisch sinnvoll sein, einen Benutzernamen (z.B. GAST) einzurichten, unter dem sich verschiedene Personen anmelden können, um allgemein zugängliche Informationen abzufragen (Auskunftsysteme).
154
DDL: Datendefinition mit SQL
Datensichten und Integritätsbedingungen.6 Jedes Datenbankobjekt gehört zu
genau einem Schema, und jedes Schema gehört genau einem Benutzer. Ein Benutzer kann aber Eigentümer mehrerer Schemata sein. Datenbankobjekte müssen
innerhalb eines Schemas eindeutige Namen haben. Das bedeutet, dass in einer
Datenbank mehrere Schemata gleichzeitig existieren können und dass gleichnamige Datenbankobjekte in verschiedenen Schemata auftreten können.
Die Anweisung zur Anlage eines Schemas lautet in etwas vereinfachter Form:
CREATE SCHEMA schema AUTHORIZATION benutzer
Es folgen dann die Definitionsanweisungen zur Erzeugung beliebig vieler Datenbankobjekte innerhalb des neuen Schemas, das sind beispielsweise CREATE DOMAIN,
CREATE TABLE, CREATE VIEW in beliebiger Anzahl und Reihenfolge. Diese Anweisungen werden in den folgenden Abschnitten beschrieben.
Zur Schemadefinition muss gesagt werden, dass zurzeit die meisten Datenbanksysteme diese Anweisung nicht zur Verfügung stellen. Oft wird gar nicht logisch
zwischen Datenbank und Schema unterschieden, und die Anlage einer Datenbank
erfolgt nicht über eine SQL-Anweisung, sondern über spezielle Hilfsprogramme,
mit denen dann auch gleich noch Details der internen Ebene wie Name und Größe
der Plattendatei festgelegt werden.
4.3 DDL: Datendefinition mit SQL
Eine zentrale Forderung an eine relationale Datendefinitionssprache ist die Unterstützung von Integritätsbedingungen. SQL unterstützt Schlüssel (Primär- und
Kandidatenschlüssel), Fremdschlüssel und so genannte »Geschäftsregeln« – das
sind Integritätsregeln, die sich durch logische Bezüge von Daten auf andere Daten
ergeben. Für die Zuverlässigkeit der Daten ist beispielsweise die Eineindeutigkeit
aller Primär- und Kandidatenschlüsselwerte unverzichtbar – wie sollen wir sonst
einen bestimmten Kunden identifizieren? Wir stellen die entsprechenden Sprachelemente in diesem Kapitel vor. Seit 1992 wird eine rudimentäre Form von benutzerdefinierten Datentypen (»Domänen«7) unterstützt. SQL:2003 enthält auch
objektorientierte Elemente, die die so genannten »objektrelationalen Datenbanken« charakterisieren sollen. Dazu gehört die Möglichkeit, »echte« benutzerdefinierte Datentypen inklusive Zugriffsmethoden und Vergleichsoperatoren zu
erzeugen. Wir kommen auf diese Erweiterungen in Kapitel 9 zurück.
Allgemein gilt, dass die Erzeugung neuer Datenbankobjekte mit dem Schlüsselwort CREATE eingeleitet wird. Anweisungen zur Entfernung von Datenobjekten
beginnen mit dem Schlüsselwort DROP8.
6 Der Begriff »Datenbankobjekt« darf nicht mit dem Objektbegriff der objektorientierten Datenbankmodelle verwechselt werden (vgl. Kapitel 9).
7 Sie erfüllen aber nur einenTeil des im Abschnitt 2.1.2 eingeführten Domänenkonzeptes.
8 Wird in 4.3.7 kurz behandelt.
155
4 Datendefinition in SQL
4.3.1 Datentypen und Domänen
Eine Domäne ist vom theoretischen Konzept her die Menge zulässiger Werte, die
ein Attribut in einer Relation annehmen kann, verbunden mit einer Menge an
Operationen, die innerhalb der Wertemenge oder in Verbindung mit anderen Wertemengen ausgeführt werden kann.9
CREATE DOMAIN gehört zum Datendefinitionsteil von SQL. Die Syntax lautet in der
Grundform:10
CREATE DOMAIN domänenname [AS] datentyp
[default]
[bedingung]
Die Domänendefinition wird im Datenwörterbuch abgelegt. Jede Domäne muss
einen eindeutigen Namen erhalten. Die Bedingung, die den Wertebereich einschränkt, wird durch eine CHECK-Klausel definiert, in der eine Suchbedingung wie in der WHERE-Klausel der SELECT-Anweisung angegeben wird.
Dabei kann beispielsweise auch festgelegt werden, ob Nullmarken zulässig
sind. Wir sind allerdings der Auffassung, dass diese Festlegung gar nicht der
Domäne selbst zukommt, wenn diese eine Wertemenge beschreibt. NULL ist kein
Wert und kann daher auch nicht Element einer Wertemenge sein. Nullmarken
zuzulassen ist eine Frage der Attributdefinition. Dabei können für Attribute, die
sich auf dieselbe Domäne beziehen, auch unterschiedliche Festlegungen getroffen werden. Eine Kundennummer als Primärschlüssel darf keine Nullmarke
enthalten, dasselbe ist für Fremdschlüssel aber in bestimmten Fällen durchaus
denkbar.
Aus einem ähnlichen Grund gehört auch die Default-Klausel eigentlich nicht in
die Domänendefinition, sondern in die Spaltendefinition einer Tabelle mit CREATE
TABLE. Verschiedene Spalten können derselben Domäne angehören, aber unterschiedliche Vorgabewerte haben. Der SQL-Standard trifft unseres Erachtens den
Sinn der Sache hier nicht. Domänen sind hier letztlich nicht mehr als eine Abtrennung von Elementen der Spaltendefinition (CHECK), auf die von mehreren Spalten aus Bezug genommen werden kann.11
Das Problem der erlaubten und verbotenen Operationen ist nicht Bestandteil der
CREATE DOMAIN-Anweisung, sondern wird im Standard unter dem Titel »Abstrakte Datentypen« behandelt. Wir erläutern dieses Konzept in Kapitel 9.
9 Vergleiche Abschnitt 2.1.2.
10 Die formale Syntaxbeschreibung ist im Anhang A beschrieben.
11 Melton, Mitglied des Standardisierungskomitees, sagt dazu: »'We were once fans of SQL's
domain capabilities. However, they have proved to be less useful than originally hoped, and
future editions of the SQL standard may actually delete the facility entirely.« [Melt02 S. 98]
156
DDL: Datendefinition mit SQL
Beispiele für Domänendefinitionen: 12
CREATE DOMAIN Kunden_key
AS INTEGER
CHECK (VALUE > 100);
CREATE DOMAIN Kunden_status
AS CHAR(1)
CHECK (VALUE IN ('W', 'G', 'S'));
-'W': Werbemaßname - noch nicht als Kunde aufgetreten
-'G': Gelegenheitskunde
-'S': Stammkunde
CREATE DOMAIN Zahlungsart
AS CHAR(1)
CHECK (VALUE IN ('R', 'B', 'N', 'V', 'K'))
DEFAULT 'N';
-'R': Rechnung
-'B': Bankeinzug
-'N': Nachnahme
-'V': Vorkasse
-'K': Kreditkarte
Auf diese Festlegung kann bei der Tabellendefinition zurückgegriffen werden. Für
eine Spalte, die ihre Werte aus einer definierten Domäne bezieht, ersetzt die Nennung der Domäne dann die Typangabe.
Beispiel:
CREATE TABLE
kunden_nr
status
zahlung
...
);
kunde (
Kunden_Key NOT NULL
Kunden_Status,
Zahlungsart,
Sybase Adaptive Server Anywhere realisiert Domänen entsprechend dem Standard.
Statt des Schlüsselworts VALUE, das beim Standard für den Zugriff auf den Wert
anzuwenden ist, muss man dort einen beliebigen Bezeichner mit vorangestelltem @
einsetzen. Wir verwenden im Folgenden in Anlehnung an den Standard jeweils
den Bezeichner @VALUE.
12 Im SQL-Standard ist nicht geregelt, ob SQL-Anweisungen durch ein Satzzeichen abgeschlossen werden müssen. In den verschiedenen interaktiven SQL-Systemen (ISQL) hat sich das Semikolon als abschließendes oder als trennendes Satzzeichen etabliert. Wir werden im Folgenden SQL-Anweisungen jeweils durch ein Semikolon abschließen. Die Zeichen -- bedeuten,
dass der Rest der Zeile ein Kommentar ist.
157
4 Datendefinition in SQL
-- Domaindefinition in Sybase SQL
CREATE DOMAIN Kunden_status
AS CHAR(1)
CHECK (@VALUE IN ('W', 'G', 'S'));
Die Anweisung CREATE DISTINCT TYPE erreicht, dass Attribute nur dann direkt vergleichbar sind, wenn sie demselben DISTINCT TYPE angehören. Beispielsweise ist
ein Vergleich von Kundennummern mit Bestellnummern dann nicht ohne weiteres
möglich. Dies macht ja auch in der Regel keinen Sinn und ist meistens das Ergebnis
einer Verwechslung von Attribut-Bezeichnern. Die Definition eines DISTINCT
TYPE schließt aber wiederum keine Einschränkung eines Wertebereichs ein.
Beispiel:
CREATE DISTINCT TYPE typ_kunden_nr AS INTEGER
Falls doch ausnahmsweise so ein Vergleich vorgenommen werden soll, ist das
durch explizite Konvertierung auf den Ursprungstyp zu formulieren, was mit der
CAST-Anweisung geschehen kann.
Beispiel:
CAST(kunden_nr AS INTEGER)
DB2 implementiert dieses Konzept.
4.3.2 Datentypen in SQL
Es gibt die im Folgenden angegebenen Datentypen in SQL. Die Skalierungsgrößen
in der Klammer hinter dem Datentyp können in der Regel weggelassen werden. Es
werden dann Standardwerte eingesetzt, die bei numerischen Datentypen implementationsabhängig sind, bei Zeichen- und Bitketten jeweils 1, bei TIME 0 (d.h.
keine Unterteilung der Sekunde) und bei TIMESTAMP 6 (d.h. Genauigkeit auf Mikrosekunden).
Exakt numerisch
INTEGER
üblicherweise vier Byte
SMALLINT
üblicherweise zwei Byte
BIGINT
mindestens so groß wie INTEGER
NUMERIC(p,q)
Dezimalzahlen mit genau p Stellen, davon q hinter dem Dezimalpunkt
DECIMAL(p,q)
Dezimalzahlen mit mindestens p Stellen, davon q hinter dem Dezimalpunkt
158
DDL: Datendefinition mit SQL
Angenähert numerisch
REAL
Gleitpunktzahlen, einfache Genauigkeit
DOUBLE PRECISION
Gleitpunktzahlen, doppelte Genauigkeit
FLOAT(p)
Gleitpunktzahlen, mindestens p Stellen Genauigkeit
Zeichenketten
CHARACTER(n)
Zeichenketten mit genau n Zeichen
CHARACTER VARYING(n)
Zeichenketten mit höchstens n Zeichen
VARCHAR
Synonym zu CHARACTER VARYING
NATIONAL CHARACTER(n)
Zeichenketten mit genau n Zeichen mit nationalen Besonderheiten wie z.B. Zeichensatz, Sortierreihenfolge
NATIONAL CHARACTER
VARYING(n)
Zeichenketten mit höchstens n Zeichen mit nationalen
Besonderheiten
CHARACTER LARGE
OBJECT (n)
Große Textobjekte. Die maximal mögliche Länge ist implementationsabhängig. Zur Spezifikation großer Längen
können die Abkürzungen K (Kilo), M (Mega), G (Giga)
verwendet werden.
CLOB
Synonym zu CHARACTER LARGE OBJECT
Jede Zeichenkette hat eine bestimmte Länge, wobei diese Länge nicht immer die
Anzahl der Bytes sein muss. Bei nationalen Zeichensätzen tritt eine Reihe von Problemen auf, zum Beispiel:
왘 Im Deutschen bilden ä, ö, ü jeweils einen Buchstaben, der aber bei der Sortie-
rung in Namensverzeichnissen jeweils wie ae, oe, ue zu behandeln ist.
왘 Im Spanischen gelten ch und ll (wie auch ñ) jeweils als ein Zeichen, das nach c, l
(bzw. n) einzusortieren ist.
Bitketten
Bitketten dienen in erster Linie als Behälter für Objekte, die nicht vom DBMS interpretiert werden (wie Bilddaten, Zeichnungselemente, Font-Daten). Die Typen BIT
und BIT VARYING sind aus dem Standard entfernt worden. Es gibt nurmehr einen
Typ für große Bitmuster wie beispielsweise Bilder, Töne, Zeichnungen, deren
Bedeutung dem DBMS nicht bekannt ist, die es also als »unstrukturierte Daten«
behandelt.
159
4 Datendefinition in SQL
BINARY LARGE OBJECT
(n)
Binäre Daten mit einer maximalen Länge entsprechend n Zeichen
BLOB(n)
Synonym zu BINARY LARGE OBJECT
Datum und Uhrzeit:
DATE
Kalenderdaten vom Jahr 1 bis zum Jahr 9999
TIME(p)
Uhrzeit in Stunden, Minuten, Sekunden, wobei die Sekunden
noch p Stellen nach dem Komma haben
TIMESTAMP(p)
Datum und Uhrzeit
TIME(p) WITH TIME ZONE
Die Angabe einer Zeitzone ist die Abweichung von der UCT
(Zeitzone von London, früher »GMT« genannt). Wegen der
Sommerzeit kann sie zwischen -11:59 und +13:00 liegen.
MEZ ist UCT +1:00, MESZ = UCT + 2:00.
TIMESTAMP(p) WITH TIME
ZONE
Datum und Uhrzeit mit Zeitzone
INTERVAL YEAR
Datumsdifferenz in Jahren
INTERVAL YEAR TO MONTH
Datumsdifferenz in Jahren und Monaten
INTERVAL DAY
Zeitdifferenz in Tagen
INTERVAL DAY TO HOUR
Zeitdifferenz in Tagen und Stunden
INTERVAL DAY TO
MINUTE
Zeitdifferenz in Tagen, Stunden, Minuten
INTERVAL MINUTE TO
SECONDS (6)
Zeitdifferenz in Minuten und Mikrosekunden
Logischer Datentyp
In der Praxis sind boolesche Variablen unverzichtbar. Jedes Prädikat (WHEREKlausel) ist schließlich ein boolescher Wert. In Programmiersprachen werden boolesche Ausdrücke benutzt, um Verzweigungen zu bearbeiten (IF ... THEN ... ELSE).
Einige, aber längst nicht alle Datenbanksysteme bieten einen solchen Typ für logische Daten bereits an. Umso erstaunlicher ist es, dass es bis 1999 gedauert hat, bis
der Datentyp BOOLEAN in SQL Einzug gehalten hat.
BOOLEAN
160
Wahrheitswerte TRUE und FALSE sowie UNKNOWN
DDL: Datendefinition mit SQL
Leider fehlt bei den Datenbanksystemen häufig der boolesche Datentyp. In diesem
Fall kann man sich notdürftig mit CHAR oder INTEGER behelfen, wobei beispielsweise 0 als FALSE und 1 als TRUE interpretiert wird. Diese Interpretation
sollte dann dokumentiert und durchgehend eingehalten werden. Wir können auf
diese Weise auch eine Domäne mit dem Befehl CREATE DOMAIN boolean definieren.
Die booleschen Operationen sind damit aber in keiner Weise verfügbar.
4.3.3 Operationen mit Datentypen
Mit den Datentypen sind bestimmte erlaubte und sinnvolle Operationen und
Funktionen verbunden. Wir stellen dies hier nur beispielhaft dar und verzichten
auf eine vollständige Aufzählung.
Operationen mit Zahlen
Für numerische Datentypen sind dies zunächst einmal die arithmetischen Operatoren:
+
Addition
-
Subtraktion
*
Multiplikation
/
Division
Weitere mathematische Operationen wie Potenzierung können mit speziellen
Funktionen ermöglicht werden oder sind selbst zu definieren (vgl. Kapitel 7.2).
Bei arithmetischen Operationen gelten die üblichen Hierarchieregeln
왘 Punktrechnung geht vor Strichrechnung.
왘 Bei gleicher Hierarchiestufe wird von links nach rechts gerechnet.
왘 Um davon abzuweichen, sind Klammern zu setzen.
Operationen mit Zeichenketten
Eine ganze Ansammlung von Operatoren und Funktionen für Zeichenketten steht
ebenfalls zur Verfügung. Dazu gehören Funktionen zur Ermittlung der Länge
einer Zeichenkette, zur Extraktion bestimmter Teile, zum Entfernen von Leerzeichen am Anfang und Ende, zur Konvertierung in Großbuchstaben etc. Wir kommen auf diese Funktionen in Kapitel 5 zurück.
Der Operator für die Zeichenverkettung ist:13
||
13 Zwei senkrechte Striche, im ASCII-Code durch den Wert 124 wiedergegeben.
161
4 Datendefinition in SQL
Durch Verkettung von Zeichenketten können zwei oder mehr Spalten virtuell wie
eine ausgegeben werden. Beispielsweise können wir Vornamen und Nachnamen
(unterstellt, diese wären in verschiedenen Spalten enthalten) für ein Adressetikett
so zusammenfügen:
vorname ||' '|| nachname
Operationen mit Datums- und Zeitdaten
Eine Anzahl von Operationen, die sich mit Datums- und Zeitwerten befassen, ist
im Standard definiert. Dazu gehören Differenzen zwischen Datums- und Zeitwerten mit dem Ergebnis eines Intervalls (Tage, Stunden etc.). Auf der anderen Seite
können durch Addition oder Subtraktion von Intervallwerten zu Werten vom Typ
TIMESTAMP neue Werte vom Typ TIMESTAMP gewonnen werden.
Operationen mit booleschen Werten
Für Wahrheitswerte gibt es die üblichen Operatoren AND, OR und NOT. Zum Vergleich von Wahrheitswerten benutzt man nicht das Gleichheitszeichen, sondern
den Operator IS. Das Ergebnis des Vergleichs ist wieder ein boolescher Wert, der
aber nur die Werte TRUE oder FALSE annehmen kann. Wenn wir bei der Artikeltabelle das Attribut kann_wegfallen mit dem Datentyp BOOLEAN definieren,
könnte eine Abfrage beispielsweise die Bedingung enthalten:
WHERE kann_wegfallen IS TRUE
oder auch nur:
WHERE kann_wegfallen
In beiden Fällen steht hinter WHERE ein Wahrheitswert. Im ersten Fall kann nur
TRUE oder FALSE herauskommen, der zweite Ausdruck kann die Werte TRUE,
FALSE oder UNKNOWN annehmen, falls für die Spalte kann_wegfallen Nullmarken erlaubt sind.
Operationen zur Typkonvertierung
Die CAST-Funktion konvertiert skalare Datentypen, sofern der infrage stehende
Wert überhaupt in einen anderen Datentyp konvertiert werden kann. Die allgemeine Syntax ist:
CAST (Skalarer Ausdruck AS [datentyp | domäne])
Beispielsweise kann mit
CAST ('333' AS INTEGER)
die Zeichenfolge ‘333’ in die Zahl 333 umgewandelt werden. Die Zeichenfolge
muss natürlich der Konvention für die Darstellung von Zahlen genügen (vgl.
Abschnitt 4.3.4).
162
DDL: Datendefinition mit SQL
Diese Operation kann auch für Datums- und Uhrzeitwerte angewandt werden;
z.B. ergibt
CAST ('1997-04-01' AS DATE)
das Datum DATE'1997-04-01' (also den 1. April 1997).
Vergleiche von Daten
Die folgende Tabelle listet die Operatoren für Vergleiche auf.
=
gleich
<>
ungleich. In einigen DBMS wird stattdessen der Operator != verwendet.
>
größer als
>=
größer/gleich
<
kleiner
<=
kleiner/gleich
Diese Vergleichsoperatoren können immer angewandt werden, wenn auf beiden
Seiten kompatible Daten stehen. Kompatibel sind zum einen alle Daten desselben
Datentyps, aber darüber hinaus alle numerischen Datentypen und alle Zeichenketten-Datentypen. Somit können natürlich SMALLINT-Größen mit REAL-Größen
verglichen werden oder Zeichenketten der Länge 10 mit Zeichenketten der Länge
5. Der Vergleich verschiedener Datums- und Zeitintervalle ist nicht immer möglich. Ist ein Intervall von 30 Tagen identisch mit einem Monatsintervall? Intervalle
vom Typ YEAR TO MONTH sind beispielsweise nicht mit Intervallen vom Typ
DAY TO SECOND vergleichbar.
Beim Vergleich von Zeichenketten wird diejenige Zeichenkette als »kleiner«
behandelt, deren Anfangszeichen aufgrund der Zeichensatzordnung weiter vorn
im Alphabet stehen, wobei ggf. nationale Besonderheiten (z.B. dass »ß« in der Sortierung äquivalent zu »ss« ist) berücksichtigt werden müssen. 14
4.3.4 Repräsentation der Datentypen
Im Folgenden werden jeweils Beispiele der festgelegten Repräsentation der Daten
für die entsprechenden Datentypen angegeben:
14 Vgl. [DaDa93 Kapitel 19].
163
4 Datendefinition in SQL
INTEGER
123456
–635480
+1
SMALLINT
1234
–6354
+1
NUMERIC(p,q)
DECIMAL(p,q)
1234.56
-1234.67
REAL
DOUBLE PRECISION
FLOAT(p)
1234.56
-1234.67
2.96E+8
3.14E00
-9.8E-3
CHARACTER(n)
CHARACTER VARYING(n)
'Bremerhaven'
'Lübeck'
BIT(n)
BIT VARYING(n)
B'0110'
X'03F4'
DATE
DATE'1995-06-22'
TIME(p)
TIME'09:18:05.23'
TIME'09:15'
TIMESTAMP(p)
TIMESTAMP'1995-06-06 10:00'
TIME(p) WITH TIME ZONE
TIME'09:00+1:00'
TIMESTAMP(p) WITH TIME ZONE
TIMESTAMP'1995-06-06 10:00+1:00'
INTERVAL
INTERVAL
INTERVAL
INTERVAL
INTERVAL
INTERVAL
'3' DAY
'10:30' HOUR TO MINUTE
'10:30' MINUTE TO SECOND
'1-6' YEAR TO MONTH
'3 12:00' DAY TO MINUTE
Die Schreibweise ist noch nicht in allen Systemen systematisch eingeführt. So entfallen insbesondere in einigen Systemen die datenspezifischen Präfixe wie DATE,
TIME, so dass zum Beispiel der 21. Juni 2004 als '2004-06-21' zu schreiben ist. Eine
Unterscheidung zu dem entsprechenden String erfolgt jeweils aus dem Zusammenhang.
164
DDL: Datendefinition mit SQL
4.3.5 CREATE TABLE
Die Anweisung CREATE TABLE dient zur Anlage von Tabellen in einer Datenbank.
Die Struktur der neuen Tabelle wird in Form von Spalten mit jeweils spezifischer
Domäne oder ersatzweise mit Datentyp und Länge festgelegt. Es wird eine leere
Basistabelle erzeugt und gewisse Daten werden in Systemtabellen eingetragen.
Folgende Konsistenzbedingungen können für eine Tabelle festgelegt werden:
왘 Primärschlüssel der Tabelle
왘 Weitere Schlüssel (Kandidatenschlüssel) der Tabelle
왘 Fremdschlüssel mit Bezugstabelle und Verfahrensregeln für die Gewährleis-
tung der referenziellen Integrität
왘 Einschränkungen des Wertebereichs der Spalten
왘 Verbot von Nullmarken in Spalten
왘 Spaltenübergreifende Integritätsbedingungen
왘 Tabellenübergreifende Integritätsbedingungen
Eine CREATE TABLE-Anweisung kann im konkreten Fall beliebig kompliziert
aussehen. Sie setzt sich wie folgt zusammen:
CREATE TABLE tabellenname
(spaltendefinitionsliste
[,tabellenintegritätsregelliste])
Jede Tabelle muss mindestens eine Spalte haben.
spaltendefinition ::=
spaltenname typangabe [vorgabewert]
[spaltenintegritätsregelliste]
Hierbei ist die Typangabe jeweils ein Datentyp oder eine vorher in der Datenbank
definierte Domäne, wie in Abschnitt 4.3.1 eingeführt.
Vorgabewert, Tabellen- und Spaltenintegritätsregeln werden im Verlauf dieses
Kapitels erklärt. Eine bekannte Spaltenintegritätsregel ist die Festlegung NOT NULL.
Der Name der Tabelle muss innerhalb des Schemas eindeutig sein. Innerhalb einer
Tabellendefinition sind die Spaltennamen ebenfalls eindeutig.
Das folgende Beispiel enthält nur eine Spaltendefinitionsliste und keine Tabellenintegritätsregeln. Als einzige Spaltenintegritätsregel wird NOT NULL festgesetzt:
CREATE TABLE
kunden_nr
name
strasse
plz
ort
kunde (
INTEGER NOT NULL,
CHAR(30) NOT NULL,
CHAR(30),
CHAR(5),
CHAR(30) NOT NULL)
165
4 Datendefinition in SQL
Die Integritätsregeln, die nach dem Standard für Spalten und/oder Tabellen in die
Datendefinition eingebracht werden können, werden durch Zusatzklauseln in der
CREATE TABLE-Anweisung formuliert. Diese können in unterschiedlicher Form
in Tabellen- und Spaltenintegritätsregeln verwendet werden. Es sind folgende
Klauseln verfügbar, die in den anschließenden Abschnitten erläutert werden:
tabellenintegritätsregel ::=
check-klausel |
primary_key-klausel |
unique-klausel |
foreign_key-Klausel
Jeder Integritätsregel kann ein Name gegeben werden, was sich bei Verstößen im
Zuge von Dateneingaben oder Änderungen positiv auswirkt, da das DBMS dann
häufig den Namen der verletzten Regel mit ausgibt. Die Syntax dazu:
CONSTRAINT regelname regel
Wir machen in den folgenden Beispielen teilweise davon Gebrauch, verzichten
aber manchmal aus Platzgründen auf die Benennung.
Vorgabewert
Der Vorgabewert in der Spaltendefinition enthält alternativ eine DEFAULT-Klausel, eine IDENTITY-Spezifikation oder eine GENERATION-Klausel. In allen Fällen
geht es darum, Spaltenwerte als Konstante oder als Ergebnis irgendeiner Berechnungsfunktion vorzugeben.
DEFAULT
Die DEFAULT-Klausel in der Spaltendefinition ermöglicht die ausdrückliche
Angabe von Vorgabewerten für den Fall, dass bei der Erfassung oder Änderung
kein Spaltenwert explizit angegeben wird. Ohne diese Klausel ist der Vorgabewert
NULL.
default-klausel ::=
DEFAULT NULL | DEFAULT systemvariable | DEFAULT literal
왘 NULL: Standardmäßig wird eine Nullmarke eingesetzt.
왘 Als Systemvariablen können unter anderem die Folgenden eingesetzt werden:
CURRENT_USER, CURRENT_ROLE
CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP
왘 literal ist eine explizite Angabe des DEFAULT-Werts. Sie muss der Datentyp-
definition bzw. der Domäne der Spalte entsprechen.
Als Beispiel für eine Anwendung der DEFAULT-Klausel vergeben wir als Vorgabewert für die Zahlungsart eines Kunden den Wert 'N' (für »Nachnahme«). Die Spaltendefinition sieht dann so aus:
zahlung CHAR(1) NOT NULL CONSTRAINT def_zahlung DEFAULT 'N'
166
DDL: Datendefinition mit SQL
IDENTITY-Spezifikation
Neu in SQL:200n ist die Möglichkeit, einer Spalte einen Generator für Sequenznummern zuzuordnen. Dieser bewirkt bei der Neueingabe eines Tupels, dass für
diese Spalte automatisch fortlaufende Werte generiert werden. Ideal ist dies für die
Erzeugung von Schlüsselnummern in Fällen, wo der Schlüssel ein einfaches numerisches Attribut ohne eigene Aussage ist. Einem Attribut kann nur dann eine
IDENTITY-Spezifikation angehängt werden, wenn es auf einem einfachen numerischen Datentyp ohne Nachkommastellen beruht.
Die Klausel lautet in vereinfachter Form:
GENERATED [ ALWAYS | BY DEFAULT ] AS IDENTITY
(START WITH startwert INCREMENT BY inkrement)
Mit den Parametern wird festgelegt, dass die Folge mit dem Wert startwert
beginnt und bei jedem Einfügen eines Tupels in die Tabelle um den Wert von inkrement erhöht wird, wobei inkrement auch negativ sein darf. Weitere Optionen erlauben die Festlegung eines minimalen und maximalen Werts, und ob die Zählung
wieder von vorn beginnen soll, wenn der Maximalwert erreicht worden ist. Letztere Einstellungen sind natürlich für Schlüsselspalten nicht interessant, denn dann
dürfen ja keine Wiederholungen vorkommen.
Bei Sybase Adaptive Server Anywhere wird die Klausel in ähnlicher Form angeboten. Wir könnten beispielsweise für eine Kundennummer automatische Werte
generieren lassen mit:
CREATE TABLE kunde3(
kunden_nr
INTEGER
IDENTITY NOT NULL ,
kname
PersonenName NOT NULL,
CONSTRAINT pk_kunde PRIMARY KEY (kunden_nr)
);
Hier ist aber keine Festlegung von Startwert und Inkrement möglich, das DBMS
fängt bei 1 an und erhöht jedesmal um 1. Beim Einfügen in die Tabelle kann man
dann den Wert für die Kundennummer weglassen, beispielsweise so:
INSERT INTO Kunde3 (kname) VALUES ('Otto');
Eine Alternative zum Erzeugen von Sequenznummern besteht darin, mit dem
Befehl CREATE SEQUENCE einen Generator als eigenes Datenbankobjekt zu erzeugen.
Beispiel:
CREATE SEQUENCE sq_kunde (START WITH 100 INCREMENT BY 1)
Beim Einfügen eines neuen Tupels in die Tabelle wird dann mit
NEXT VALUE FOR sq_kunde
in der Werteliste der nächste Wert aus der Folge abgerufen.15
15 Testen konnten wir dies leider nicht. Eine ähnliche Lösung gibt es bei ORACLE. Dort lautet der
Einfügebefehl unter Zugriff auf den Nummerngenerator:
INSERT INTO kunde3(kunden_nr, name) VALUES (sq_kunde.nextval, 'Otto').
167
4 Datendefinition in SQL
GENERATION-Klausel
SQL sieht vor, dass eine Tabellen berechnete Spalten enthalten darf. Zur Definition dient die GENERATION-Klausel. Wir gehen hier nicht darauf ein und verweisen zum Thema berechnete Spalten auf das Kapitel 6 über Datensichten. Im
Unterschied zu den Datensichten werden aber die Werte berechneter Spalten in
Basistabellen abgespeichert und bei jeder Änderung des Tupels automatisch
aktualisiert.
CHECK-Klausel
Die Anwendung von Domänen anstelle von Typangaben in der Spaltendefinition
wurde im Abschnitt über Domänen bereits erläutert. Die CHECK-Klausel ermöglicht Festlegungen bezüglich erlaubter Spaltenwerte unabhängig von einer Domänendefinition, geht aber darüber insofern hinaus, als auch Bedingungen formulierbar sind, die mehrere Spalten betreffen.
Für die CHECK-Klausel gilt die Syntax:
CHECK (bedingung)
Die Bedingung wird wie in der WHERE-Klausel der SELECT-Anweisung angegeben.
Sie muss sich auf die Spalten der zu erzeugenden Tabelle beziehen. Dabei sind
aber auch Unterabfragen zulässig, die sich auf andere Tabellen beziehen. Im folgenden Beispiel wird die CHECK-Klausel bei der Definition einer Kundentabelle
mehrfach angewandt, um die erlaubten Werte für die Spalten status und zahlung
zu spezifizieren.
CREATE TABLE kunde (
kunden_nr
INTEGER
NOT NULL,
status
CHAR(1)
NOT NULL,
name
CHAR(30) NOT NULL,
strasse
CHAR(30) NOT NULL,
plz
CHAR(5)
NOT NULL,
ort
CHAR(30) NOT NULL,
letzte_bestellung DATE,
letzte_werbeaktion DATE,
zahlungsart
CHAR(1)
NOT NULL,
CONSTRAINT chk_status CHECK (status IN ('S','W','G')),
CONSTRAINT chk_zahlungsart
CHECK (zahlungsart IN ('R','B','N','V','K'))
);
Bei jeder Datenänderung wird sichergestellt, dass die Bedingung nicht den logischen Wert FALSE annehmen kann – der Wert UNKNOWN der dreiwertigen Logik im
Zusammenhang mit Nullmarken ist dagegen zulässig. In dem folgenden Beispiel
ist es also zulässig, dass liefermenge vorübergehend eine Nullmarke enthält – es ist
168
DDL: Datendefinition mit SQL
nicht zulässig, dass die Attribute liefermenge und bestellmenge definierte Werte
haben und der Wert für liefermenge größer ist als der für bestellmenge.
CHECK (liefermenge <= bestellmenge)
Wie wir an folgendem Beispiel sehen, kann die CHECK-Klausel auch direkt an die
Spaltendefinition angefügt werden.
CREATE TABLE kunde (
kunden_nr INTEGER
NOT NULL,
status
CHAR(1)
NOT NULL
CHECK status IN ('S', 'W', 'G'),
...
zahlungsart
CHAR(1)
NOT NULL
CHECK (zahlungsart IN ('R', 'B', 'N', 'V', 'K'))
);
Die NOT NULL-Bedingung könnte auch als CHECK-Klausel formuliert werden,
wir haben im Beispiel aber die übliche Kurzform beibehalten.
Wir sehen an diesem Beispiel, dass der Unterschied zwischen einer Tabellenintegritätsregel und einer Spaltenintegritätsregel hier verschwimmt. Es gibt in diesem
Fall keine Unterschiede – weder in der Formulierung noch in der Bedeutung. Dies
gilt, solange eine CHECK-Klausel sich auf eine einzige Spalte bezieht. Wenn
mehrere Spalten betroffen sind, hat die CHECK-Klausel den Charakter einer Tabellenintegritätsregel und steht separat nach den Spaltendefinitionen.
Primärschlüssel
Der SQL-Standard unterstützt die Primärschlüsseldefinition auf der Ebene der
Tabellenerzeugung.
Jede Relation muss einen Primärschlüssel enthalten. Die PRIMARY KEY-Klausel
ist aber nicht verbindlicher Bestandteil der CREATE TABLE-Anweisung. Die Syntax folgt dem Schema:
PRIMARY KEY (spaltenliste)
Es sind die Attribute anzugeben, die zusammen den Schlüssel bilden. In vielen
Fällen besteht der Schlüssel nur aus einem Attribut.
Zusammen mit den bereits definierten Klauseln könnte die Definition der Kundentabelle so aussehen:16
CREATE TABLE kunde (
kunden_nr
INTEGER
status
CHAR(1)
NOT NULL,
NOT NULL,
16 Wir haben hier der Deutlichkeit halber den Befehl ohne Rückgriff auf Domänen formuliert.
Sonst würden in diesem Befehl keine CHECK-Klauseln auftreten, da diese in den Domänendefinitionen enthalten sind.
169
4 Datendefinition in SQL
name
CHAR(30)
NOT NULL,
strasse
CHAR(30)
NOT NULL,
plz
CHAR(5)
NOT NULL,
ort
CHAR(30)
NOT NULL,
letzte_bestellung DATE,
letzte_Werbeaktion DATE,
zahlungsart
CHAR(1)
NOT NULL DEFAULT 'N',
CONSTRAINT chk_status CHECK (status in ('S','W','G')),
CONSTRAINT chk_zahlungsart
CHECK (zahlungsart in ('R','B','N','V','K'))
CONSTRAINT pk_kunde PRIMARY KEY (kunden_nr)
);
Wenn es sich um einen einspaltigen Primärschlüssel handelt, ist es auch möglich,
das Schlüsselwort PRIMARY KEY als Bestandteil der Spaltendefinition einzutragen.
In unserem Beispiel könnten wir die Formulierung wie folgt abändern:
CREATE TABLE kunde (
kunden_nr INTEGER
...
);
NOT NULL PRIMARY KEY,
Hinter den Schlüsselwörtern PRIMARY KEY entfällt dann die Angabe der Spalte.
Damit wäre die Klausel als Spaltenintegritätsregel formuliert. Dies ändert aber
nichts an der Tatsache, dass ein Primärschlüssel sich immer auf eine ganze Tabelle
bezieht und nicht nur auf die (ggf. einzige) Spalte, aus der er besteht. Wir empfehlen daher, die PRIMARY KEY-Klausel immer nach den Spaltendefinitionen hinzuschreiben.
Die Einbeziehung eines Attributs in den Primärschlüssel setzt voraus, dass für dieses Attribut Nullmarken ausgeschlossen sind. Nach dem Standard kann auf diese
Angabe verzichtet werden – das System setzt diese Bedingung automatisch ein.
UNIQUE-Klausel
Diese Erweiterung der Tabellendefinition dient dazu, Kandidatenschlüssel zu verwalten, also Attribute oder Attributkombinationen, die geeignet sind, ein Tupel
eindeutig zu identifizieren. Der Primärschlüssel stellt davon nur einen Spezialfall
dar. Die Syntax lautet:
unique_klausel ::=
UNIQUE (spaltenliste | VALUE )
Bei einer UNIQUE-Angabe wird nicht verlangt, dass die Definitionen der
zugrunde liegenden Spalten die Klausel NOT NULL enthalten. Es wird dann nicht als
Verstoß gegen die Eindeutigkeit gewertet, wenn der Schlüsselkandidat für eine
Zeile eine Nullmarke hat. Bei mehrspaltigen Schlüsseln wäre sogar jede »eindeutige« Kombination von Nullmarken einmal erlaubt. Wir halten dies für keine gute
170
DDL: Datendefinition mit SQL
Lösung, da hier implizit so getan wird, als sei NULL von jedem anderen Wert zu
unterscheiden. Wenn die Nullmarke bedeutet, »Wert vorhanden, aber nicht bekannt«
(A-Marke), ist diese Unterstellung nicht zu halten.
Auf der anderen Seite ist damit auch festgelegt, dass für eine UNIQUE-Bedingung,
die I-Marken enthalten kann, jeweils nur ein Tupel NULL enthalten kann. So kann
z.B. ein Fahrzeugmotor immer nur in einem Fahrzeug eingebaut sein – oder in keinem, wenn er zur Überholung in der Werkstatt ist –, ein Fall, der durch eine
I-Marke zu kennzeichnen wäre. Eine UNIQUE-Bedingung auf der Fahrzeugnummer führt aber dazu, dass maximal ein Motor jeweils zur Überholung in der Werkstatt sein darf. Dieser Sachverhalt ist also dann anders abzubilden (z.B. indem für
die Beziehungsmenge ist_eingebaut_in eine eigene Relation mit den Attributen
Fahrzeugnummer und Motornummer und ggf. weiteren Attributen gebildet
wird).
Zum Beispiel könnte für die Buchhaltung jedem Kunden eindeutig ein Debitorenkonto zugeordnet sein:
CREATE TABLE KUNDE (
kunden_nr
INTEGER NOT NULL,
,,,
konto_nr
INTEGER NOT NULL,
CONSTRAINT pk_kunde PRIMARY KEY (kunden_nr),
CONSTRAINT uq_kunde UNIQUE (konto_nr)
);
UNIQUE kann bei einspaltigen Kandidatenschlüsseln auch in Kurzform als Bestand-
teil der Spaltendefinition verwendet werden. Mit UNIQUE (VALUE) wird festgelegt, dass die Tupel einer Tabelle eindeutig sein müssen, also je zwei Tupel sich in
mindestens einem Attributwert unterscheiden.17
FOREIGN KEY-Klausel
Die Anforderungen der referenziellen Integrität werden sehr gut unterstützt. Es
soll sichergestellt werden, dass für einen Fremdschlüssel nur solche Werte verwendet werden dürfen, die auch in der Menge der korrespondierenden Primärschlüsselwerte vorkommen.
Beispielsweise soll in einer Tabelle bestellung die Kundennummer kunden_nr als
Fremdschlüssel für die Tabelle kunde vorkommen. Damit kann zu jedem Auftrag
der zugehörige Auftraggeber ermittelt werden. Es muss sichergestellt werden,
dass nur gültige, d.h. in kunde vorhandene Kundennummern in bestellung erfasst
werden können. Dies ermöglicht die FOREIGN KEY-Klausel. Die Syntax der Klausel lautet:
17 Bei Relationen wäre dies ohnehin gegeben, bei Tabellen sind aber mehrere gleiche Tupel möglich (vgl. Kapitel 2.6).
171
4 Datendefinition in SQL
foreign_key-klausel ::=
FOREIGN KEY (spaltenliste)
REFERENCES tabellenname [(spaltenliste)]
[MATCH übereinstimmungstyp]
[ON DELETE änderungsaktion]
[ON UPDATE änderungsaktion]
Die nicht obligatorischen Teilklauseln MATCH sowie ON DELETE und ON UPDATE werden weiter unten gesondert behandelt.
Die Spaltenangabe nach der Angabe der referenzierten Tabelle ist dann erforderlich, wenn sich der Fremdschlüssel nicht auf den Primärschlüssel, sondern auf
einen Kandidatenschlüssel bezieht. Ansonsten kann sie entfallen.
In der Tabelle bestellung enthält das Attribut kunden_nr die Kundennummer, die
auf das gleichnamige Attribut in der Kundentabelle verweist.
CREATE TABLE bestellung (
bestell_nr
INTEGER
NOT NULL,
kunden_nr
INTEGER
NOT NULL,
bestelldatum
DATE
NOT NULL DEFAULT CURRENT DATE,
lieferdatum
DATE,
rechnungsbetrag MONEY,
CONSTRAINT pk_bestellung PRIMARY KEY (bestell_nr),
CONSTRAINT fk_bestellung_kunde
FOREIGN KEY (kunden_Nr) REFERENCES kunde
);
Auch hier ist eine Kurzform möglich, wenn der Fremdschlüssel nur aus einer
Spalte besteht. Die Kurzform lautet hier:
CREATE TABLE bestellung (
bestell_nr
INTEGER
NOT NULL PRIMARY KEY,
kunden_nr
INTEGER
NOT NULL
FOREIGN KEY REFERENCES kunde,
,,,
);
MATCH-Klausel
Mit der Match-Klausel kann festgelegt werden, welcher Grad der Übereinstimmung von Fremdschlüsselwerten mit dem Primär- oder Kandidatenschlüssel der
referenzierten Tabelle verlangt wird. Diese Festlegung ist nur dann von Relevanz,
wenn der Fremdschlüssel aus mehreren Attributen besteht, von denen wenigstens
ein Teil Nullmarken enthalten kann. Grundsätzlich gilt die referenzielle Integrität
immer dann als erfüllt, wenn der gesamte Fremdschlüssel NULL ist. Wenn aber
ein Teil der Attribute eine Nullmarke enthält, sind mehrere Varianten möglich:
übereinstimmungstyp ::= SIMPLE | PARTIAL | FULL
172
DDL: Datendefinition mit SQL
SIMPLE
Mit der Klausel MATCH SIMPLE wird festgelegt, dass die referenzielle Integrität
bereits als erfüllt gilt, wenn eines der Fremdschlüsselattribute NULL ist. Das
bedeutet, die Übereinstimmung des Fremdschlüssels mit einem Schlüssel in der
Vatertabelle wird überhaupt nur dann überprüft, wenn alle Fremdschlüsselattribute einen Wert haben. Diese Regel gilt auch implizit, wenn keine MATCH-Klausel
angegeben wird, und war in bisherigen Versionen des Standards die einzige Möglichkeit.
PARTIAL
Hier wird zugelassen, dass Teile eines Fremdschlüssel NULL-Werte haben dürfen.
Die Übereinstimmung von Fremdschlüssel- und Primär- oder Kandiatenschlüssel
wird im Unterschied zur SIMPLE-Variante überprüft, aber nur für die Attribute,
die einen Wert haben.
FULL
Hier wird generell verlangt, dass entweder alle Fremdschlüsselattribute NULL
sind oder alle einen Wert haben müssen, der dann auf Übereinstimmung mit
Schlüsselwerten in der referenzierten Tabelle überprüft wird. Enthält ein Teil der
Fremdschlüsselattribute eine Nullmarke, gilt die referenzielle Integrität als verletzt.
Wenn bei der Tabellendefinition für alle Fremdschlüsselattribute NOT NULL verlangt
wird – kein Fremdschlüsselattribut darf dann fehlen – ist die Unterscheidung der
Übereinstimmungstypen ohne Relevanz, die MATCH-Klausel ist dann überflüssig.
Aktionsregeln für die referenzielle Integrität
Mit der FOREIGN KEY-Klausel wird erreicht, dass nur gültige Fremdschlüsselwerte erfasst werden können. Damit ist bei »Vater-Sohn-Beziehungen« zwischen
Tabellen die Integrität der abhängigen Tabelle gesichert. Die referenzielle Integrität
kann aber auch durch Änderungen in der Vatertabelle bedroht sein. Dies ist dann
der Fall, wenn dort eine Zeile gelöscht wird, deren Primärschlüsselwert von der
abhängigen Tabelle referenziert wird, oder wenn ein als Fremdschlüsselwert vorkommender Primärschlüsselwert verändert wird.
Der SQL-Standard definiert eine Syntax für Regeln, die bei schreibenden Zugriffen
auf referenzierte Primärschlüsselwerte die Integrität gewährleisten. Darin können
Aktionen festgelegt werden: Die Veränderung kann ganz verboten werden, sie
kann an den Fremdschlüsselwert weitergegeben werden oder dieser kann auf NULL
bzw. den Default-Wert gesetzt werden.
Syntaktisch ist Folgendes definiert:
änderungsaktion ::=
NO ACTION | CASCADE | SET NULL | SET DEFAULT
173
4 Datendefinition in SQL
NO ACTION
Jegliche Änderung an den referenzierten Schlüsselwerten ist untersagt, wenn es
korrespondierende Fremdschlüsselwerte gibt. Diese Regel gilt auch implizit
immer dann, wenn keine Änderungsaktion angegeben wird.18
Wir können also beispielsweise keinen Kunden löschen, zu dem noch Bestellungen
existieren. Aktualisierungen oder Löschungen solcher Primärschlüsselwerte sind
untersagt.
CASCADE
Die per Fremdschlüsselwert korrespondierenden Zeilen in der abhängigen Tabelle
werden entsprechend manipuliert, wenn der referenzierte Schlüsselwert gelöscht
oder geändert wird. Wird also ein Kunde gelöscht, dann verliert man auch seine
Bestellungen.
SET NULL
Korrespondierende Fremdschlüsselwerte werden auf NULL gesetzt, wenn der referenzierte Schlüssel manipuliert wird. Das setzt voraus, dass für die Fremdschlüsselwerte Nullmarken zugelassen sind, was im Beispiel der Kunden und Bestellungen natürlich auszuschließen ist. Es gibt aber solche Fälle: Wird beispielsweise in
einer Tabelle abteilung der Abteilungsleiter durch einen Fremdschlüssel auf die
Tabelle personal dargestellt, so kann dieser vorübergehend eine Nullmarke enthalten. Das bedeutet dann, dass die Abteilung zurzeit keinen Leiter hat.
SET DEFAULT
Alle entsprechenden Fremdschlüsselwerte werden auf den bei der Tabellendefinition mit der DEFAULT-Klausel definierten Standardvorgabewert gesetzt, wenn
der Primärschlüsselwert verändert wird.
Auswahl der passenden Variante
Welche dieser Möglichkeiten infrage kommt, muss nach der jeweiligen Sachlage
entschieden werden. Würde die Option ON DELETE CASCADE bei der Definition der
Bestellungstabelle angewandt, hätte dies zur Folge, dass mit jedem Kunden, der
gelöscht wird, zugleich seine Aufträge verschwinden. Bei ON DELETE SET NULL würden die Aufträge erhalten bleiben, wären aber keinem Kunden mehr zugeordnet.
In diesem Beispiel der Beziehung kunde-bestellung wird man in den meisten Firmen wohl die Variante NO ACTION bevorzugen. Falls in einen Auftrag schon sehr
viel investiert wurde – beispielsweise im Schiffbau –, könnte aber auch bei Ausfall
des Auftraggebers ein Nullsetzen der Kundennummer angezeigt sein, was bedeutet, dass für einen bestehenden Auftrag ein neuer Kunde zu suchen ist. Bei bestellung und position wird in vielen Fällen ein kaskadierendes Verfahren sinnvoll
18 Bei manchen DBMS (z.B. ORACLE, Sybase) wird dies durch RESTRICTED ausgedrückt.
174
DDL: Datendefinition mit SQL
sein: Wird ein Auftrag storniert, entfallen auch alle Positionen. Bei der Definition
der Tabelle position wäre dann Folgendes anzugeben (wir unterstellen diesmal,
dass für die einzelnen Wertebereiche der Attribute Domänen definiert wurden):
CREATE TABLE position (
bestell_nr
Bestell_key
NOT NULL,
artikel_nr
Artikel_key
NOT NULL,
mwst
Prozentsatz,
-- aus Artikeldatei zu übernehmen
bestellmenge
CARDINAL
NOT NULL,
liefermenge
CARDINAL,
gesamtpreis
MONEY,
CHECK (bestellmenge >= 1),
CHECK (liefermenge <= bestellmenge)),
PRIMARY KEY (bestell_nr, artikel_nr),
CONSTRAINT fk_position_bestellung
FOREIGN KEY (bestell_nr)
REFERENCES bestellung
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT fk_position_artikel
FOREIGN KEY (artikel_nr)
REFERENCES artikel
);
Die Kurzform, bei der die Fremdschlüsseleigenschaft einer Spalte direkt bei der
Spaltendefinition angegeben wird, lautet hier:
CREATE TABLE position (
bestell_nr
Bestell_key
NOT NULL
FOREIGN KEY REFERENCES bestellung
ON UPDATE CASCADE
ON DELETE CASCADE,
,,,
);
4.3.6 Assertions
Integritätsbedingungen, die mehrere Tabellen betreffen, können nicht immer als
Bestandteil der CREATE TABLE-Anweisung formuliert werden. Solche Einschränkungen werden im Standard als Assertion (»Zusicherung«) bezeichnet. Es gibt
dort auch eine entsprechende Anweisung CREATE ASSERTION mit folgender Syntax:
CREATE ASSERTION assertion_name
CHECK (bedingung) [attributliste]
175
4 Datendefinition in SQL
Es ist jeweils eine logische Bedingung anzugeben, die nicht verletzt werden darf.
Die optionalen Attribute können beschreiben, dass die Assertion verzögert werden
kann (um z.B. eine Bedingung zu formulieren, die Aktionen in mehr als einer
Tabelle erfordert).
Das folgende Beispiel stellt sicher, dass einem Kunden die Zahlungsart 'B' für
»Bankeinzug« nur dann zugewiesen werden kann, wenn in der Datenbank sein
Bankkonto erfasst ist.19
CREATE ASSERTION kunde_zahlart
CHECK (NOT EXISTS (
SELECT * FROM kunde k
WHERE zahlung = 'B'
AND NOT EXISTS (
SELECT * FROM girokonto g
WHERE g.kunden_nr = k.kunden_nr
)))
DEFERRABLE;
Wenn die Anweisung CREATE ASSERTION nicht zur Verfügung steht, wie es bei vielen DBMS noch der Fall ist, muss man unter Umständen zum Hilfsmittel der
Datenbank-Trigger greifen, um eine tabellenübergreifende Integritätsbedingung
zu formulieren.20 Das ist dann zwar eine prozedurale statt einer deklarativen
Lösung, aber immer noch besser, als das Problem in die Anwendungsprogrammierung zu verlegen.
4.3.7 Entfernung von Datenbankobjekten
Sämtliche Datenbankobjekte, die mit irgendeiner Form einer CREATE-Anweisung
angelegt wurden (CREATE TABLE, CREATE DOMAIN etc.), können aus der Datenbank
auch wieder entfernt werden. Dazu stehen Varianten der DROP-Anweisung zur
Verfügung. Dabei ist grundsätzlich zu beachten, dass Datenbankobjekte selbst
auch einer Art referenzieller Integrität unterworfen sind. Tabellen oder Domänen
können ja von anderen Objekten aus referenziert werden.
Die Entfernung einer Datenbanktabelle geschieht beispielsweise mit der DDLAnweisung DROP TABLE. Sofern eine Tabelle an keiner definierten Beziehung teilnimmt und keine Datensichtdefinitionen auf sie Bezug nehmen, wird die Anweisung ohne weiteres ausgeführt. Anders sieht es aus, wenn beispielsweise eine
Fremdschlüsselbeziehung zu der fraglichen Tabelle existiert.
19 Der hier vorgestellte Befehl CREATE ASSERTION konnte bisher nicht getestet werden, da kein
den Autoren zugängliches DBMS ihn unterstützt.
20 Vgl. Kapitel 7.
176
DDL: Datendefinition mit SQL
Wenn beispielsweise eine Tabelle bestellung mit der Integritätsregel
... FOREIGN KEY (kunden_nr) REFERENCES kunde
definiert ist, wird die Anweisung DROP TABLE kunde dann nicht ausgeführt, da
implizit eine Restriktion gilt. Sollen hingegen mit einer Tabelle alle Bezüge auf sie
ebenfalls gelöscht werden, muss dies mit dem Zusatz CASCADE angefordert werden.
Die Anweisung
DROP TABLE kunde CASCADE;
löscht automatisch die obige Integritätsdefinition aus der Tabelle bestellung.
4.3.8 CREATE INDEX
Die Definition von Indexen gehört nicht in die Datendefinitionssprache im engeren Sinne. Indexe dienen dazu, Abfragen an eine Datenbank zu beschleunigen.21
Sie sind aber bei einem echten relationalen Datenbanksystem nicht erforderlich,
um Daten überhaupt zu finden. Die Formulierung von Abfragen ist unabhängig
von der Existenz oder Nichtexistenz von Indexen.22 So wird der Kunde mit
kunden_nr = 103 mit der Anweisung
SELECT * FROM kunde WHERE kunden_nr = 103
gesucht – unabhängig davon, ob ein Index auf kunden_nr existiert oder nicht.23
Bei klassischen Dateiverwaltungssystemen wäre hingegen die Nutzung eines
Index explizit anzufordern. Ein Beispiel im Pseudocode für den Fall, dass ein
Index existiert:
Öffne Kundendatei für lesenden Zugriff über Index kunden_nr
Lies Kundensatz mit Schlüssel kunden_nr = 103
Falls nicht gefunden: Fehlerbehandlung
Falls kein Index existiert, wäre dagegen in etwa wie folgt zu verfahren:
Öffne Kundendatei für sequenziellen lesenden Zugriff
Lies ersten Satz
Solange letzter Lesezugriff erfolgreich und kunden_nr ≠ 103 tue
Lies nächsten Satz
Ende tue
Falls nicht kunden_nr ≠ 103: Fehlerbehandlung
21 Vgl. Kapitel 2.7.
22 Das ändert nichts daran, dass erfahrene Anwendungsentwickler unter Umständen eine von
mehreren äquivalenten Formulierungen einer Abfrage bevorzugen, um den Optimierer »auszutricksen«. Sie riskieren aber, dass beim nächsten Versionswechsel des DBMS der erwünschte
Effekt nicht mehr eintritt.
23 Allerdings können die Antwortzeiten in den beiden Fällen erheblich voneinander abweichen.
177
4 Datendefinition in SQL
Wir führen Indexe hier auf, da sie ein wichtiges Instrument bei der Gestaltung von
Datenbankanwendungen sind und ihre Definition in vielen Fällen zugleich mit der
Definition der Tabellen erfolgt.
Die Syntax der CREATE INDEX-Anweisung ist nicht Gegenstand des SQL-Standards.
Dennoch ähneln sich die verschiedenen SQL-Implementationen in diesem Punkt.
Die Syntax bei ORACLE, Sybase oder DB2 lautet beispielsweise:
CREATE [UNIQUE] INDEX index_name
ON tabellenname (spaltenliste) [ASC | DESC]
Der Zusatz UNIQUE bewirkt, dass für sämtliche Zeilen der Tabelle die Kombination
der Werte der Indexspalten verschieden sein muss. Damit konnten in der Vergangenheit auch solche DBMS indirekt das Primärschlüsselkonzept unterstützen, bei
denen die PRIMARY KEY- und die UNIQUE-Klausel in der CREATE TABLEAnweisung (seit 1989 Bestandteil des SQL-Standards) noch nicht verfügbar waren.
Die Angabe von ASC bzw. DESC bewirkt die aufsteigende bzw. absteigende Sortierung des Index und ist nur wichtig für sortierte Ausgaben von Daten, in denen das
Sortierkriterium mit den Spalten eines Index beginnt.
Zum Beispiel wird mit folgender Anweisung ein aufsteigend sortierter, eindeutiger Index über die Spalte kunden_nr in der Kundentabelle definiert:
CREATE UNIQUE INDEX ix_kunde ON kunde (kunden_nr);
Zu beachten ist, dass Indexe nicht nur die Datenbank schneller machen. Wenn das
DBMS einerseits Abfragen beschleunigt bearbeitet, wird es bei Einfüge- und Änderungsoperationen meist langsamer. Aber auch Abfragen können unter Umständen
durch Indexe verlangsamt werden – ein weites Feld für Experimente!24
4.3.9 Übungsaufgaben
Aufgabe 4.1
einer Datenbank sind die Tabellen angestellte(abtnr, ...) und
abteilung(abtnr, ...) enthalten. In der Anweisung CREATE TABLE angestellte finIn
det sich die Klausel:
FOREIGN KEY abteilung
REFERENCES abteilung(abtnr)
ON UPDATE CASCADE
In abteilungen wird nun ein Primärschlüsselwert geändert, der als Fremdschlüsselwert referenziert wird. Die Nummer einer Abteilung, zu der Angestellte gehören, wird also geändert und die vorherige Abteilungsnummer existiert danach
nicht mehr.
24 Siehe hierzu den Aufsatz: »Kriterien für die Anlage eines Index« [Unte93a].
178
DDL: Datendefinition mit SQL
a) Was passiert aufgrund des oben angegebenen Anweisungsteils in angestellte?
b) Was würde passieren, wenn die obige Anweisung mit der Aktionsregel ON UPDATE SET NULL formuliert worden wäre?
c) Was würde passieren, wenn die obige Anweisung mit der Aktionsregel ON UPDATE NO ACTION formuliert worden wäre?
Aufgabe 4.2
Dozent
Teilnehmer
DozNr
DozName
TnNr
TnName
10
Peters
100
Lück
11
Meier
101
Hinz
12
Otten
102
Kunz
Seminar
SemNr
Thema
Datum
Preis
DozNr
DozName
1001
DatStru
13.12.2003
250
10
Peters
1002
SysAnal
15.10.2003
350
12
Otten
Seminarteilnehmer
SemNr
TnNr
TnName
Bezahlt
1001
100
Lück
J
1001
102
Kunz
N
1002
101
Hinz
J
Die oben dargestellte Datenstruktur ist in einer SQL-Datenbank zu realisieren. Die
Primärschlüssel sind jeweils unterstrichen. Zuvor sind aber noch ein paar kleine
Probleme zu klären.
a) Welche Normalformen sind verletzt (vgl. Kapitel 3.4)?
b) Wie sind die entsprechenden Tabellen zu modifizieren, damit die erste bis dritte
Normalform erfüllt wird?
c) Definieren Sie Domänen mit der CREATE DOMAIN-Anweisung.
d) Definieren Sie die Tabellen mit der CREATE TABLE-Anweisung.
179