Einführung in SQL: DDL - Einzelheiten
Aus Wikibooks
In diesem Kapitel werden einige Befehle der Data Definition Language (DDL) vertieft behandelt.
Wegen des Umfangs mancher Befehle und Optionen werden die Abschnitte sachlich gegliedert, nicht nach einem einzelnen Befehl.
Inhaltsverzeichnis |
[Bearbeiten] Definition einer Tabelle
Um eine Tabelle zu erzeugen, sind wesentlich konkretere umfangreiche Angaben nötig.
CREATE TABLE <Tabellenname>
( <Spaltenliste>
[ , <Einschränkungen> ]
);
Zum Erzeugen einer Tabelle werden folgende Angaben benutzt:
- der Name der Tabelle, mit dem die Daten über die DML-Befehle gespeichert und abgerufen werden
Dazu kommen in Klammern gesetzt die weiteren Angaben:
- die Liste der Spalten (Felder), und zwar vor allem mit dem jeweiligen Datentyp
- Angaben wie der Primärschlüssel (PRIMARY KEY, PK) oder weitere Indizes
Jede Spalte und Einschränkung wird mit einem Komma abgeschlossen; dieses entfällt vor der schließenden Klammer. Die Einschränkungen – CONSTRAINTs – werden häufig nicht sofort festgelegt, sondern durch anschließende ALTER TABLE-Befehle; ich bespreche sie deshalb getrennt.
Notwendig sind: der Name des Befehls, der Name der Tabellen, die runden Klammern, mindestens eine Spalte mit Name und Typ. Eine solche "Minimalversion" gibt es aber nur für Code-Beispiele; in der Praxis gehören immer mehrere Spalten und der PK dazu.
Beim Entwurf einer Datenbank und ihren Tabellen sollten Sie immer beachten:
- Datentypen, Art und Umfang der Zusatzangaben hängen vom DBMS ab.
- Primärschlüssel: Manche DBMS verlangen ausdrücklich einen PK – meistens eine ID o.ä., nur selten aus mehreren Spalten zusammengesetzt. Auch wenn es nicht verlangt wird, ist ein PK dringend zu empfehlen; eine Tabelle ohne PK ist selten sinnvoll. Dessen Inhalte müssen eindeutig sein und dürfen sich nicht wiederholen.
- Dafür wird meistens eine automatische Fortzählung (AUTO_INCREMENT) angeboten, was ohne Entwicklungsaufwand die Bedingungen eines Primärschlüssels erfüllt.
|
|
Sie müssen bei Art und Umfang aller Angaben immer die Besonderheiten Ihres DBMS beachten! |
|---|
Die Code-Auszüge stammen überwiegend aus der Beispieldatenbank. Die wichtigsten Bestandteile ersehen Sie aus dem folgenden Beispiel; weitere Bestandteile werden in den späteren Abschnitten behandelt.
SQL-Quelltext:
CREATE TABLE Abteilung ( ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, Kuerzel VARCHAR(10) NOT NULL, Bezeichnung VARCHAR(30) NOT NULL, Ort VARCHAR(30) );
Die Tabelle Abteilung wird mit vier Spalten erzeugt:
- ID ist eine ganze Zahl, die nicht NULL sein darf, die Werte durch die automatische Zählung erhält und die als PRIMARY KEY benutzt wird.
- Kuerzel ist eine Zeichenkette mit variabler Länge (höchstens 10 Zeichen), die nicht NULL sein darf.
- Bezeichnung und Ort sind Zeichenketten mit höchstens 30 Zeichen. Der Ort darf NULL sein, die Bezeichnung nicht.
[Bearbeiten] Definition einer einzelnen Spalte
Jede einzelne Spalte wird wie folgt definiert.
<Spaltenname> <Datentyp> [ <Optionen> ]
Jede der Optionen wird mit einem Schlüsselwort und der erforderlichen Angabe hinzugefügt.
Die Optionen können in der Regel kombiniert werden; die Reihenfolge muss beachtet werden. Verschiedene der Einschränkungen (siehe unten) können auch bei einer einzelnen Spalte angegeben werden.
[Bearbeiten] COLLATE – Sortierungsregel
Jede Spalte kann eine eigene Regel für die alphabetische Sortierung erhalten – abweichend von der Regel der Tabelle oder Datenbank bzw. von der Standardsortierung gemäß Zeichensatz.
In der obigen Definition der Tabelle Abteilung soll die Bezeichnung nicht nach den allgemein für die Datenbank gültigen Regeln sortiert werden, sondern nach denen für kanadisches Französisch:
Firebird-Version
SQL-Quelltext:
CREATE /* usw. bis */ Bezeichnung VARCHAR(30) NOT NULL COLLATION FR_CA, /* usw. */
Achtung: So funktioniert der Befehl nicht. Der obige CREATE-Befehl stammt aus der MySql-Version, diese COLLATION-Ergänzung stammt aus Firebird.
[Bearbeiten] NULL-Werte zulässig oder nicht
NULL bzw. NOT NULL legt ausdrücklich fest, ob NULL-Werte in der Spalte zulässig sind oder nicht. Der Standardwert ist "zulässig", das NULL kann deshalb entfallen.
Im obigen CREATE-Befehl gilt:
- Die Spalten ID, Kuerzel, Bezeichnung dürfen keine NULL-Werte enthalten. Die Informationen in diesen Spalten sind wesentlich für einen Datensatz; eine Speicherung ohne einen dieser Werte wäre sinnlos.
- Die Spalte Ort darf dagegen NULL-Werte enthalten. Diese Angabe ist nur eine zusätzliche Information; die Abteilung steht auch dann eindeutig fest, wenn der Sitz nicht bekannt oder noch nicht festgelegt ist.
[Bearbeiten] DEFAULT – Vorgabewert
Mit DEFAULT <Wert> wird ein Standardwert festgelegt (als konstanter Wert oder als Ergebnis einer Funktion); dieser wird immer dann verwendet, wenn bei einer Neuaufnahme für diese Spalte kein Wert angegeben ist.
In der Tabelle Mitarbeiter erhält die Spalte Ist_Leiter den Vorgabewert 'N'; denn ein Mitarbeiter ist normalerweise kein Abteilungsleiter:
SQL-Quelltext:
CREATE TABLE Mitarbeiter ( ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, /* usw. bis */ Ist_Leiter CHAR(1) DEFAULT 'N', Abteilung_ID INTEGER NOT NULL );
Weit verbreitet sind dabei Standardwerte, mit denen Datum/Zeit einer Änderung und die Bearbeiterin registriert werden:
- CURRENT_TIMESTAMP als aktuelles Datum und Uhrzeit
- CURRENT_USER
[Bearbeiten] AUTO_INCREMENT – automatischer Zähler
AUTO_INCREMENT legt fest, dass die Werte in dieser Spalte automatisch vom DBMS hochgezählt werden.
Siehe das Beispiel unter Definition einer Tabelle.
[Bearbeiten] COMMENT – Beschreibung verwenden
Damit wird der Inhalt der Spalte beschrieben. Dies ist nützlich für alle Spalten, deren Bezeichner nicht ganz eindeutig sind oder deren Inhalt besondere Bedingungen erfüllen sollen. Damit erleichtern Sie sich selbst und anderen "Betreuern" der Datenbank die Arbeit.
SQL-Quelltext:
CREATE TABLE Schadensfall ( ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, Datum DATE NOT NULL, Ort VARCHAR(200) NOT NULL COMMENT 'nicht nur mit dem Ortsnamen, sondern auch mit Straße und äußeren Umständen' , /* usw. */ );
[Bearbeiten] Tabelle ändern
Mit ALTER TABLE wird die Struktur einer Tabelle geändert:
ALTER TABLE <Tabellenname> <Aufgabenliste> ;
Es können also mehrere Aufgaben mit einem ALTER-Befehl ausgeführt werden. Die möglichen Aufgaben sind in den einzelnen Abschnitten beschrieben.
Der Begriff COLUMN ist nicht immer Teil des Befehls: Bei manchen DBMS kann er weggelassen werden, bei manchen darf er nicht benutzt werden.
[Bearbeiten] Stop – Aufgabe kann nicht ausgeführt werden
Eine Spalte kann oft nicht geändert oder gelöscht werden, wenn sie an anderer Stelle benutzt wird. Das gilt vor allem dann, wenn diese Spalte beim PRIMARY KEY, einem INDEX, einem FOREIGN KEY oder in einer CHECK-Einschränkung für die Tabelle benutzt wird. In diesen Fällen muss zunächst die "abhängige" Konstruktion gelöscht, deaktiviert oder geändert werden. Erst danach kann die Änderung in der Tabelle ausgeführt werden.
Der Datentyp kann durch ALTER COLUMN nur dann geändert werden, wenn die "alten" Werte automatisch (implizit) in den neuen Datentyp konvertiert werden können.
[Bearbeiten] ADD COLUMN – Spalte hinzufügen
Diese Aufgabe fügt der Tabelle eine weitere Spalte hinzu.
Die Tabelle Versicherungsvertrag wird um Spalten zur Berechnung und Anpassung der Versicherungsprämie erweitert.
Firebird-Version hinsichtlich Schreibweise (ohne "COLUMN") und Reihenfolge
SQL-Quelltext:
ALTER TABLE Versicherungsvertrag ADD Basispraemie DECIMAL DEFAULT 500 NOT NULL, ADD Praemiensatz INTEGER DEFAULT 100 NOT NULL, ADD Praemienaenderung DATE;
Die bisherigen Inhalte der Tabelle bleiben unverändert. In den neuen Spalten wird der DEFAULT-Wert eingetragen, andernfalls NULL.
[Bearbeiten] ALTER COLUMN – Spalte ändern
Diese Aufgabe ändert eine Spalte dieser Tabelle. Dies kann eine Änderung des Datentyps, ein anderer DEFAULT-Wert oder eine andere Einschränkung sein.
In der Tabelle Abteilung ist die Spalte Kuerzel mit VARCHAR(10) definiert, die einzelnen Werte sind aber immer genau 4 Zeichen lang. Die Spaltendefinition soll an die Realität angepasst werden.
Maßnahme kann nicht unbedingt ausgeführt werden
SQL-Quelltext:
ALTER TABLE Abteilung ALTER COLUMN Kuerzel TYPE CHAR(4);
SQL-Ausgabe:
Firebird-Fehlermeldung: This operation is not defined for system tables. unsuccessful metadata update. New size specified for column KUERZEL must be at least 40 characters.
Mit der Option TYPE wird der Datentyp geändert. Vorhandene Inhalte werden dabei möglichst implizit konvertiert. Beispielsweise MySql hat keine Probleme damit, den Text entsprechend abzuschneiden. Firebird weigert sich aber, auch wenn die tatsächlichen Inhalte passen, sondern bringt eine völlig verwirrende Fehlermeldung, die gar nicht zu dieser Situation passt.
In solchen Fällen gibt es einen Umweg:
- Erzeugen Sie eine neue, temporäre Spalte.
- Kopieren Sie alle Inhalte durch einen UPDATE-Befehl aus der "alten" Spalte, die geändert werden soll, in die temporäre Spalte.
- Löschen Sie die "alte" Spalte.
- Erzeugen Sie eine neue Spalte unter dem "alten" Namen mit den "neuen" Eigenschaften.
- Kopieren Sie alle Inhalte durch einen UPDATE-Befehl aus der temporären Spalte in die neue Spalte, wobei sie passend konvertiert werden müssen.
- Löschen Sie die temporäre Spalte.
Dieser Weg wird unter Änderung der Datenbankstruktur benutzt.
[Bearbeiten] DROP COLUMN – Spalte entfernen
Diese Aufgabe entfernt eine Spalte aus der Tabelle, z.B. die eben erwähnte temporäre Spalte.
SQL-Quelltext:
ALTER TABLE Abteilung DROP COLUMN Temp;
Durch Löschen einer Spalte wird nicht der Speicherplatz der Spalte freigegeben. Dafür muss (sofern erforderlich) ein vollständiges Backup + Restore der Datenbank ausgeführt werden. Aber das machen Sie ja sowieso regelmäßig.
[Bearbeiten] ADD CONSTRAINT – Einschränkung hinzufügen
Diese Aufgabe erweitert die Bedingungen, die für die Daten der Tabelle gelten sollen.
ALTER TABLE <Tabellenname> ADD [ CONSTRAINT <constraint name> ] <Inhalt> ;
Einzelheiten zum <Inhalt> stehen im Abschnitt CONSTRAINTs – Einschränkungen.
[Bearbeiten] DROP CONSTRAINT – Einschränkung entfernen
Diese Aufgabe löscht eine Bedingung, die für die Daten der Tabelle gültig war.
ALTER TABLE <Tabellenname> DROP CONSTRAINT <constraint name>;
[Bearbeiten] CONSTRAINTs – Einschränkungen
Dabei handelt es sich um Bedingungen, denen ein Datensatz entsprechen muss. Wenn eine der aktuell gültigen Bedingungen verletzt wird, wird der betreffende Datensatz nicht gespeichert. Die Bedingungen können folgende Beziehungen betreffen:
- die Schlüssel innerhalb der Tabelle: PRIMARY KEY, INDEX
- die Beziehungen zu anderen Tabellen: FOREIGN KEY
- die Werte innerhalb der Spalte: UNIQUE, CHECK
Ein CONSTRAINT kann mit oder ohne eigenen Namen festgelegt werden. Ich empfehle die Benutzung eines Namens, weil dies die Arbeit übersichtlicher macht: Bei Verletzung einer Regel wird dieser Name meistens angegeben; anhand des Namens ist das Löschen direkt möglich. Aber das ist Geschmackssache und hängt wohl auch vom DBMS ab. Das Schlüsselwort CONSTRAINT selbst ist nur erforderlich bei Verwendung des Namens; ansonsten würden die Schlüsselwörter der einzelnen Bedingungen ausreichen.
Ein CONSTRAINT wird auf eine der folgenden Arten festgelegt:
- im CREATE TABLE-Befehl bei einer einzelnen Spalte als Bedingung für diese Spalte
- im CREATE TABLE-Befehl als Bedingung für die Tabelle, also in der Liste der <Einschränkungen>
- im ALTER TABLE-Befehl durch ADD CONSTRAINT
Ein CONSTRAINT wird wie folgt gelöscht:
ALTER TABLE <Tabellenname> DROP CONSTRAINT <constraint name>;
Ein CONSTRAINT kann nicht geändert werden. Es ist nur Löschen und erneute Festlegung möglich.
Welche Wörter der Schlüsselbegriffe optional sind (z.B. KEY), hängt von der konkreten Situation ab.
[Bearbeiten] PRIMARY KEY – Primärschlüssel der Tabelle
Der Primärschlüssel – PRIMARY KEY und PK als gängige Abkürzung – ist das wichtigste Mittel, mit dem die Datenbank alle Einträge verwaltet. Ohne PK sind weder Änderungen und Löschungen einzelner Datensätze noch Fremdschlüssel (FOREIGN KEYs) möglich.
- Bei der vorstehenden Aussage bin ich mir nicht 100%ig sicher, ob es auch theoretisch korrekt ist. Für die Praxis gilt dies aber auf jeden Fall.
Als Primärschlüssel geeignet sind folgende Arten von Spalten:
- der Datentyp
GUID - eine Spalte mit einem INTEGER-Datentyp, der als AUTO_INCREMENT verwendet wird oder ersatzweise durch eine SEQUENCE bestimmt wird
- Die Beispieldatenbank benutzt ausnahmslos eine solche Spalte namens ID.
- eine Spalte mit einem INTEGER-Datentyp, deren Werte nach Lage der Dinge eindeutig sind und während der "Lebenszeit" der Datenbank nicht mehr geändert werden.
- Die Beispieldatenbank enthält in der Tabelle Mitarbeiter die Spalte Personalnummer. Diese ist eigentlich eindeutig und dürfte deshalb als PK verwendet werden. Da die Firma ihre interne Struktur aber durchaus ändern könnte und dann die Personalnummern anpassen würde, scheidet diese Spalte als PK aus.
- eine Kombination aus zwei Spalten, von denen jede dem PK jeweils einer anderen Tabelle entspricht, wenn die "neue" Tabelle nur die Zuordnungen zwischen den beiden anderen Tabellen darstellt.
- In der Tabelle Zuordnung_SF_FZ der Beispieldatenbank sind die Zuordnungen Fahrzeuge/Schadensfälle enthalten; anstelle einer eigenen ID wäre auch ein Primärschlüssel aus Fahrzeug_ID plus Schadensfall_ID möglich und sinnvoll.
Als Primärschlüssel ungeeignet oder unmöglich sind folgende Arten von Spalten:
- sämtliche Spalten (wie eine PLZ), bei denen mehrere Einträge vorkommen können
- eine Kombination von Name/Vorname bei allen Tabellen mit Namen, weil über kurz oder lang ein "Müller, Hans" doppelt vorkommen dürfte
- auch eine Kombination von Name/Vorname/Geburtstag scheidet aus dem gleichen Grund aus
- eine Kombination von Name/Geburtstag/Region/lfd.Nr. (ähnlich wie bei der Versicherungsnummer der deutschen Rentenversicherung) wäre eindeutig, aber als Kombination von vier Spalten äußerst unpraktisch
Der Primärschlüssel kann wie folgt festgelegt werden:
- im CREATE TABLE-Befehl bei einer einzelnen Spalte als Zuordnung für diese Spalte
MySql-Version
SQL-Quelltext:
CREATE TABLE Abteilung ( ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, Kuerzel VARCHAR(10) NOT NULL, Bezeichnung VARCHAR(30) NOT NULL, Ort VARCHAR(30) );
Die Spalte ID wird direkt als (einzige) Spalte des PK definiert. Dies ist implizit ein CONSTRAINT, bekommt aber keinen eigenen Namen.
- im CREATE TABLE-Befehl in der Liste der <Einschränkungen>
Firebird-Version
SQL-Quelltext:
CREATE TABLE Abteilung ( ID INTEGER, Kuerzel VARCHAR(10) NOT NULL, Bezeichnung VARCHAR(30) NOT NULL, Ort VARCHAR(30) CONSTRAINT Abteilung_PK PRIMARY KEY (ID) );
Der PK bekommt als CONSTRAINT einen eigenen Namen, der Vermerk in Klammern führt die Spalten auf, die als PK verwendet werden (hier wie meistens handelt es sich um eine einzelne Spalte).
- im ALTER TABLE-Befehl durch ADD CONSTRAINT
MySql-Version mit zwei aufeinanderfolgenden Befehlen
SQL-Quelltext:
CREATE TABLE Abteilung ( ID INTEGER, Kuerzel VARCHAR(10) NOT NULL, Bezeichnung VARCHAR(30) NOT NULL, Ort VARCHAR(30) ); ALTER TABLE Abteilung ADD CONSTRAINT Abteilung_PK PRIMARY KEY (ID);
Die Tabelle erhält zunächst noch keinen PK, auch wenn das durch AUTO_INCREMENT suggeriert und vorbereitet wird. Vielmehr wird der PK anschließend (mit eigenem Namen) definiert; der Vermerk in Klammern führt die Spalten auf, die als PK verwendet werden.
[Bearbeiten] UNIQUE – Eindeutigkeit
Ein UNIQUE KEY sorgt dafür, dass innerhalb einer Spalte bzw. einer Kombination von Spalten kein Wert doppelt auftreten kann. Beispiele sind in der Tabelle Mitarbeiter die Spalte Personalnummer und in der Tabelle Fahrzeug die Spalte Kennzeichen.
Eine solche Eindeutigkeitsbedingung kann wie folgt festgelegt werden:
- im CREATE TABLE-Befehl bei einer einzelnen Spalte als Zuordnung für diese Spalte
SQL-Quelltext:
CREATE TABLE Fahrzeug ( ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, Kennzeichen VARCHAR(10) NOT NULL UNIQUE, Farbe VARCHAR(30), Fahrzeugtyp_ID INTEGER NOT NULL );
- im CREATE TABLE-Befehl in der Liste der <Einschränkungen> mit Bezug auf eine oder mehrere Spalten
SQL-Quelltext:
CREATE TABLE Fahrzeug ( ID INTEGER NOT NULL AUTO_INCREMENT, Kennzeichen VARCHAR(10) NOT NULL, Farbe VARCHAR(30), Fahrzeugtyp_ID INTEGER NOT NULL, CONSTRAINT Fahrzeug_PK PRIMARY KEY (ID), CONSTRAINT Fahrzeug_Kz UNIQUE (Kennzeichen) );
- im ALTER TABLE-Befehl durch ADD CONSTRAINT
SQL-Quelltext:
ALTER TABLE Fahrzeug ADD [ CONSTRAINT Fahrzeug_Kz ] UNIQUE (Kennzeichen);
[Bearbeiten] INDEX – Suche beschleunigen
Ein INDEX ist ein Verfahren innerhalb einer Datenbank, mit dem schnell auf Datensätze zugegriffen werden kann. Vor allem der PK benutzt selbst einen Index. (Intern arbeiten die DBMS unterschiedlich; für den Nutzer sieht es immer so aus, als wenn der PK ein Index ist.) Sämtliche Spalten bzw. Kombinationen von Spalten, nach denen in SELECT-Befehlen häufiger gesucht oder sortiert wird, sollten mit einem Index versehen werden. Beispiele:
- Name/Vorname sowie PLZ und separat PLZ/Name (vielleicht auch PLZ/Straße) in Tabellen mit Adressen
- solche Spalten, die für den Nutzer wie ein PK aussehen, es aber nicht sind, z.B. in der Tabelle Mitarbeiter die Spalte Personalnummer und in der Tabelle Fahrzeug die Spalte Kennzeichen
- Die Angabe UNIQUE für eine Spalte sorgt bereits für einen Index; eine doppelte Festlegung ist nicht nötig.
- das Datum in der Tabelle Schadensfall
Wenn vorzugsweise die größeren Werte zuerst benötigt werden, ist mit DESC ein absteigender Index (wie im folgenden Beispiel) sinnvoll. In manchen Fällen sind durchaus zwei getrennte Indizes auf dieselbe Spalte angebracht – der eine ASC, der andere DESC.
Ein Index ist nicht sinnvoll, wenn eine Spalte nur wenige verschiedene Werte enthalten kann wie in der Tabelle Versicherungsvertrag die Spalte Art.
Ein Index kann wie folgt festgelegt werden:
- im CREATE TABLE-Befehl in der Liste der <Einschränkungen> mit Bezug auf eine oder mehrere Spalten
MySql-Version
SQL-Quelltext:
CREATE TABLE Schadensfall ( ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, Datum DATE NOT NULL, Ort VARCHAR(200) NOT NULL, /* usw. */ INDEX Schadensfall_Datum (Datum DESC) );
In der Tabelle wird sofort ein Index (mit eigenem Namen) für die Spalte Datum angelegt, und zwar sollen die Werte absteigend verwaltet werden. (Das bedeutet, dass die Daten mit dem "größten" Wert, also die aktuellsten Werte zuerst gefunden werden.) Der Standardwert, der nicht angegeben werden muss, ist ASC (= aufsteigend).
- mit einem zusätzlichen CREATE INDEX-Befehl in folgender Syntax:
CREATE [ UNIQUE ] INDEX <Indexname>
ON <Tabellenname> ( <Spaltenliste> );
"Irgendwo" (unterschiedlich nach DBMS) kann außerdem ASC bzw. DESC festgelegt werden.
Das vorige Beispiel sieht dann unter Firebird (DESC vorgezogen) so aus:
SQL-Quelltext:
CREATE DESC INDEX Schadensfall_Datum ON Schadensfall (Datum);
[Bearbeiten] FOREIGN KEY – Fremdschlüssel
Ein FOREIGN KEY (FK) regelt die logischen Verbindungen zwischen zwei Tabellen: Ein Datensatz in einer Tabelle darf in einer bestimmten Spalte nur solche Werte benutzen, die in einer anderen Tabelle als PK registriert sind. Beispiele:
- In der Tabelle Mitarbeiter darf als Abteilung_ID nur eine gültige ID der Tabelle Abteilung stehen.
- In der Tabelle Fahrzeuge darf als Fahrzeugtyp_ID nur eine gültige ID der Tabelle Fahrzeugtyp stehen.
- In der Tabelle Fahrzeugtyp darf als Hersteller_ID nur eine gültige ID der Tabelle Fahrzeughersteller stehen.
Ein Fremdschlüssel kann wie folgt festgelegt werden:
- im CREATE TABLE-Befehl bei einer einzelnen Spalte als Bedingung für diese Spalte
- im CREATE TABLE-Befehl als Bedingung für die Tabelle, also in der Liste der <Einschränkungen>
- im ALTER TABLE-Befehl durch ADD CONSTRAINT
Einzelheiten werden unter Fremdschlüssel-Beziehungen behandelt.
[Bearbeiten] CHECK – Werteprüfungen
Ein CHECK ist eine Prüfung, ob die Werte, die für einen Datensatz gespeichert werden sollen, bestimmten Regeln entsprechen. Diese Prüfung wird sowohl bei INSERT als auch bei UPDATE vorgenommen; sie kann für eine einzelne Spalte oder für die Tabelle festgelegt werden.
Als Bedingung in der CHECK-Klausel kann im wesentlichen alles stehen, was für die WHERE-Klausel vorgesehen ist.
Eine solche Prüfung kann wie folgt festgelegt werden:
- im CREATE TABLE-Befehl bei einer einzelnen Spalte als Bedingung für diese Spalte
In der Tabelle Schadensfall sind als Schadenshoehe natürlich keine negativen Zahlen zulässig. Die Spalte Verletzte ist als CHAR(1) definiert; sinnvollerweise sind nur die Werte 'J' und 'N' zulässig.
SQL-Quelltext:
CREATE TABLE Schadensfall ( ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, Datum DATE NOT NULL, Ort VARCHAR(200) NOT NULL, Beschreibung VARCHAR(1000) NOT NULL, Schadenshoehe DECIMAL(16,2) CHECK(Schadenshoehe >= 0), Verletzte CHAR(1) NOT NULL CHECK(Verletzte = 'J' OR Verletzte = 'N'), Mitarbeiter_ID INTEGER NOT NULL );
- im CREATE TABLE-Befehl als Bedingung für die Tabelle, also in der Liste der <Einschränkungen>
Wenn ein Versicherungsnehmer eine Person ist, müssen Vorname, Geburtsdatum und Führerschein registriert sein. Außerdem muss er mindestens 16 Jahre alt sein.
SQL-Quelltext:
CREATE TABLE Versicherungsnehmer ( ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, Name VARCHAR(30) NOT NULL , Vorname VARCHAR(30) NOT NULL , Geburtsdatum DATE , Fuehrerschein DATE , /* usw. für alle anderen Spalten, danach: */ CONSTRAINT Versicherungsnehmer_CheckDatum CHECK( ( (Geburtsdatum IS NULL) AND (Fuehrerschein IS NULL) AND (Vorname IS NULL OR Vorname = '') ) OR (Fuehrerschein >= Geburtsdatum + 365*16) ));
Die ersten Bedingungen prüfen, ob es sich um eine Person handelt; wenn nicht, sind Führerscheinprüfung und Geburtsdatum irrelevant, und der Datensatz kann gespeichert werden. Wenn es sich um eine Person handelt, wird auch die letzte Bedingung benötigt; diese wird "näherungsweise" geprüft und berücksichtigt, dass das DBMS ein Datum intern als ganze Zahl speichert. Alternativ könnten auch mit EXTRACT() Tag, Monat, Jahr getrennt verglichen werden. Dieses Verfahren wäre aber viel umständlicher; deshalb wollte ich es hier nicht angeben.
- im ALTER TABLE-Befehl durch ADD CONSTRAINT
In der Tabelle Versicherungsvertrag sind als Art nur bestimmte Werte zulässig: 'VK' (= Vollkasko), 'TK' (= Teilkasko incl. Haftpflicht), 'HP' (= Haftpflicht).
SQL-Quelltext:
ALTER TABLE Versicherungsvertrag ADD CONSTRAINT Vertrag_CheckArt CHECK (Art IN ('VK', 'TK', 'HP') );
[Bearbeiten] Zusammenfassung
In diesem Kapitel lernten wir mehrere Verfahren kennen, mit denen einzelne Spalten und ganze Tabellen genauer festgelegt werden:
- Zu einer Spalte gehören nicht nur der Datentyp, sondern auch die Vorgabe von Werten und Wertebereichen.
- Für eine Spalte können Einschränkungen wie "Eindeutigkeit" oder "Teil des Primärschlüssels" oder "Teil eines Index" gelten.
- Für eine Tabelle können Wertebereiche über mehrere Spalten geprüft werden.
- Eigentlich immer gehört zu einer Tabelle ein Primärschlüssel.
- Außerdem können Indizes und Fremdschlüssel festgelegt werden.
