DDL – Einzelheiten
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.
Definition einer Tabelle
[Bearbeiten]Um eine Tabelle zu erzeugen, sind sehr umfangreiche Angaben nötig.
CREATE TABLE <Tabellenname> ( <Spaltenliste> [ , <Einschränkungen> ] );
Zum Erstellen einer Tabelle gehören folgende Angaben:
- 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 den jeweiligen Datentypen.
- 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; sie werden deshalb getrennt besprochen.
Notwendig sind: der Name des Befehls, der Name der Tabelle, 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 ihrer 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 den Skripten zur Beispieldatenbank. Die wichtigsten Bestandteile ersehen Sie aus dem folgenden Beispiel; weitere Bestandteile werden in den späteren Abschnitten behandelt.
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, ihre Werte durch die automatische Zählung erhält und 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.
Definition einer einzelnen Spalte
[Bearbeiten]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.
COLLATE – Sortierungsregel
[Bearbeiten]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:
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 aber aus Firebird.
NULL-Werte zulässig oder nicht
[Bearbeiten]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.
DEFAULT – Vorgabewert
[Bearbeiten]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:
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
AUTO_INCREMENT – automatischer Zähler
[Bearbeiten]AUTO_INCREMENT legt fest, dass die Werte in dieser Spalte automatisch vom DBMS hochgezählt werden – siehe das obige Beispiel unter „Definition einer Tabelle“.
Die folgenden SQL-Systeme bieten einen automatischen Zähler als Teil der Spaltendefinition an. Teilweise hängt es von der Version ab, was möglich ist und was nicht.
| DBMS | Schlüsselwort | Beispiel | Bemerkungen |
|---|---|---|---|
| MS-SQL | IDENTITY | ALTER TABLE dbo.doc_exe
ADD column_b INT IDENTITY CONSTRAINT column_b_pk PRIMARY KEY,
|
|
| MySQL | AUTO_INCREMENT | CREATE TABLE Abteilung
( ID INTEGER NOT NULL AUTO_INCREMENT PRIMARYKEY, /* usw. */
);
|
|
| Oracle | AUTO INCREMENT | CREATE TABLE Fahrzeug
(ID INTEGER NOT NULL AUTO INCREMENT, /* usw. */
CONSTRAINT Fahrzeug_PK PRIMARY KEY (ID)
);
|
nur möglich bei der Lite-Version für mobile Computer, "AUTO INCREMENT" wird hier mit Leerzeichen geschrieben |
Bei den folgenden SQL-Systemen werden Zähler mit anderen Verfahren angeboten.
| DBMS | Schlüsselwörter | Beispiel | Bemerkungen |
|---|---|---|---|
| Firebird | SEQUENCE TRIGGER |
/* Definition */
CREATE SEQUENCE Fahrzeug_ID;
/* Benutzung im Trigger */
CREATE OR ALTER TRIGGER Fahrzeug_BI0 FOR Fahrzeug
ACTIVE BEFORE INSERT POSITION 0
AS
begin
IF ((new.ID IS NULL) OR (new.ID = 0))
then new.ID = next value FOR Fahrzeug_ID;
end
|
Es wird ein Zähler definiert (wahlweise je Tabelle oder pauschal). In einem Before-Insert-Trigger wird der nächste Wert abgerufen und eingefügt. |
| Firebird veraltet |
GENERATOR TRIGGER |
/* Definition */
CREATE GENERATOR Fahrzeug_ID;
SET GENERATOR Fahrzeug_ID TO 0;
/* Benutzung im Trigger */
CREATE OR ALTER TRIGGER Fahrzeug_BI0 FOR Fahrzeug
ACTIVE BEFORE INSERT POSITION 0
AS
begin
IF ((new.ID IS NULL) OR (new.ID = 0))
then new.ID = GEN_ID(Fahrzeug_ID, 1);
end
|
Es wird ein Zähler definiert (wahlweise je Tabelle oder pauschal). In einem Before-Insert-Trigger wird der nächste Wert abgerufen und eingefügt.
Das Verfahren mit GENERATOR sollte für neue Datenbanken nicht mehr benutzt werden. |
| Oracle | SEQUENCE TRIGGER |
/* Definition */
CREATE SEQUENCE Fahrzeug_ID;
/* Benutzung im Trigger */
CREATE OR REPLACE TRIGGER Fahrzeug_BI
BEFORE INSERT ON Fahrzeug
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (:NEW.ID IS NULL)
begin
/* in früheren Oracle-Versionen: */
SELECT Fahrzeug_ID.NEXTVAL INTO :NEW.ID FROM DUAL;
/* ab Version 11g direkte Zuweisung: */
:NEW.ID := Fahrzeug_ID.NEXTVAL;
end
|
Es wird ein Zähler definiert (wahlweise je Tabelle oder pauschal). In einem Before-Insert-Trigger wird der nächste Wert abgerufen und eingefügt. |
| Oracle | SEQUENCE INSERT |
/* Definition */
CREATE SEQUENCE Fahrzeug_ID;
/* Benutzung im INSERT-Befehl */
INSERT INTO Fahrzeug
( ID, Kennzeichen /* usw. */ )
VALUES
( Fahrzeug_ID.NEXTVAL, 'BO-CR 123' /* usw. */ )
;
|
Es wird ein Zähler definiert (wahlweise je Tabelle oder pauschal). Im INSERT-Befehl wird der nächste Wert abgerufen und übernommen.
Die Version mit Trigger ist vorzuziehen. |
Wie schon früher erwähnt, gehört der Zähler gemäß AUTO_INCREMENT zur Tabelle; eine „Sequenz“ bezieht sich dagegen auf die gesamte Datenbank. Man kann ohne Weiteres eine einzige Sequenz AllMyIDs definieren und die neue ID einer jeden Tabelle daraus ableiten. In der Beispieldatenbank benutzen wir getrennte Sequenzen, weil sie für die verschiedenen DBMS „ähnlich“ aussehen soll.
Für die meisten Varianten gibt es Parameter zur genaueren Steuerung, nachzulesen in der DBMS-Dokumentation.
COMMENT – Beschreibung verwenden
[Bearbeiten]Dies beschreibt den Inhalt der Spalte – nützlich für alle Spalten, sofern ein Bezeichner nicht ganz klar ist oder der Inhalt besondere Bedingungen erfüllen soll. Damit erleichtern Sie sich und anderen die Arbeit.
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. */
);
Tabelle ändern
[Bearbeiten]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.
Stop – Aufgabe kann nicht ausgeführt werden
[Bearbeiten]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.
ADD COLUMN – Spalte hinzufügen
[Bearbeiten]Diese Aufgabe fügt der Tabelle eine weitere Spalte hinzu.
Die Tabelle Versicherungsvertrag wird um Spalten zur Berechnung und Anpassung der Versicherungsprämie erweitert.
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.
ALTER COLUMN – Spalte ändern
[Bearbeiten]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.
Firebird-Version: Maßnahme kann nicht unbedingt ausgeführt werden.alter table Abteilung
ALTER COLUMN Kuerzel TYPE CHAR(4);
AusgabeFirebird-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, obwohl die tatsächlichen Inhalte passen, sondern bringt eine völlig verwirrende Fehlermeldung, die gar nicht zu dieser Situation passt.
Dafür gibt es einen Umweg – siehe das Kapitel Änderung der Datenbankstruktur.
- 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.
DROP COLUMN – Spalte entfernen
[Bearbeiten]Diese Aufgabe entfernt eine Spalte aus der Tabelle, z. B. die eben erwähnte temporäre Spalte.
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.
ADD CONSTRAINT – Einschränkung hinzufügen
[Bearbeiten]Diese Aufgabe erweitert die Bedingungen für die Daten der Tabelle.
ALTER TABLE <Tabellenname> ADD [ CONSTRAINT <constraint name> ] <Inhalt> ;
Einzelheiten zum <Inhalt> folgen unter CONSTRAINTs – Einschränkungen.
DROP CONSTRAINT – Einschränkung entfernen
[Bearbeiten]Diese Aufgabe löscht eine Bedingung, die für die Daten der Tabelle gültig war.
ALTER TABLE <Tabellenname> DROP CONSTRAINT <constraint name>;
CONSTRAINTs – Einschränkungen
[Bearbeiten]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 Folgendes 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. Wir empfehlen 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.
PRIMARY KEY – Primärschlüssel der Tabelle
[Bearbeiten]Der Primärschlüssel – PRIMARY KEY mit PK als gängiger Abkürzung – ist das wichtigste Mittel, mit dem die Datenbank alle Einträge verwaltet. Ohne PK sind weder Änderungen noch Löschungen einzelner Datensätze möglich, ohne alle Spalten anzugeben. Im praktischen Einsatz haben Tabellen ohne Primärschlüssel keinen Sinn. Fremdschlüssel (FOREIGN KEYs, FK) wären ohne Primärschlüssel nicht möglich.
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, sofern die 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 aber ihre interne Struktur ändern und die Personalnummern anpassen könnte, 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.
- Die Tabelle Zuordnung_SF_FZ der Beispieldatenbank enthält die Zuordnungen Fahrzeuge/Schadensfälle; 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:
- Unmöglich sind sämtliche Spalten (wie eine PLZ), bei denen mehrere Datensätze mit dem gleichen Wert vorkommen können.
- Unmöglich ist 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) ist zwar eindeutig, aber als Kombination von vier Spalten äußerst unpraktisch.
- Eine Spalte, deren Werte sich ändern können, ist zwar möglich, aber nicht geeignet. Das gilt z. B. für das Kfz-Kennzeichen, aber auch (wie schon gesagt) für etwas wie die Personalnummer.
Der Primärschlüssel kann wie folgt festgelegt werden:
- im CREATE TABLE-Befehl bei einer einzelnen Spalte als Zuordnung für diese Spalte
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>
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
create table Abteilung
( ID INTEGER not null auto_increment,
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.
UNIQUE – Eindeutigkeit
[Bearbeiten]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 Spalte als Zuordnung für diese Spalte
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
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
alter TABLE Fahrzeug
ADD [ CONSTRAINT Fahrzeug_Kz ] UNIQUE (Kennzeichen);
INDEX – Suche beschleunigen
[Bearbeiten]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 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
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:
CREATE DESC INDEX Schadensfall_Datum
ON Schadensfall (Datum);
Die Eindeutigkeitsbedingung UNIQUE benutzt intern (vermutlich immer) ebenfalls einen INDEX; dieser kann auch ausdrücklich angegeben werden:
ALTER TABLE Fahrzeug
ADD CONSTRAINT Fahrzeug_Kennzeichen UNIQUE (Kennzeichen)
USING INDEX Fahrzeug_Kennzeichen_UK;
FOREIGN KEY – Fremdschlüssel
[Bearbeiten]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 Fahrzeug 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 im Kapitel Fremdschlüssel-Beziehungen behandelt.
CHECK – Werteprüfungen
[Bearbeiten]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 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.
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>
Bei Personen als Versicherungsnehmer benötigt man Vorname, Geburtsdatum und Führerschein sowie ein Mindestalter von 16 Jahren.
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 deutlich umständlicher; deshalb sollte es hier nicht stehen.
- 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).
alter table Versicherungsvertrag
add CONSTRAINT Vertrag_CheckArt
check (Art in ('VK', 'TK', 'HP') );
Zusammenfassung
[Bearbeiten]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.
Übungen
[Bearbeiten]
| Übung 1 | Definitionen | Zur Lösung |
Welche der folgenden Aussagen sind wahr, welche sind falsch?
- Zur Definition einer Tabelle gehört unbedingt die Definition der Spalten.
- Zur Definition einer Tabelle gehört unbedingt die Definition des Primärschlüssels.
- Zur Definition einer Tabelle gehören unbedingt die Klammern.
- Die Definition von Einschränkungen ist während des CREATE-Befehls oder durch einen ALTER-Befehl möglich.
- Als UNIQUE darf nur eine Spalte festgelegt werden.
- Jede Spalte kann als NOT NULL festgelegt werden.
- Für jede Spalte können Vorgabewerte festgelegt werden.
- Es gibt Situationen, in denen die Definition einer Spalte nicht geändert werden kann.
- Der Begriff CONSTRAINT gehört zur Definition einer Einschränkung.
- Ein Primärschlüssel kann über beliebig viele Spalten festgelegt wird.
- Es ist üblich, dass der Wert eines Primärschlüssels immer wieder einmal geändert wird.
| Übung 2 | Tabellendefinition | Zur Lösung |
Bitte geben Sie an, welche Bestandteile der folgenden Definition falsch sind bzw. welche Angaben fehlen.
create table Computer
constraint ComputerID primary key (Nummer)
unique Name,
Name not null varchar collation Win1252
Nummer integer primary Key
Hersteller varchar(30)
Herstellung date
Festplatte long default 320*1024*1024*1024
Ram_Groesse long,
;
Hinweis: Bei den folgenden Definitionen verwenden Sie bitte für alle Einschränkungen geeignete Namen.
| Übung 3 | Einschränkungen allgemein definieren | Zur Lösung |
Erstellen Sie die Definition für eine Tabelle mit internationalen Postleitzahlen: laufende Nummer, Land, Code, Ortsname. Legen Sie für jede Spalte möglichst viele Einzelheiten fest; bei der Reihenfolge der Einzelheiten müssen Sie wegen der Unterschiede der DBMS nur auf die CONSTRAINTS achten.
| Übung 4 | Spalten mit Einschränkungen hinzufügen | Zur Lösung |
Ergänzen Sie die Tabelle Versicherungsvertrag um folgende Spalten:
- Basisprämie für einen Betrag, Vorgabewert 500, keine negativen Beträge
- Prämiensatz für eine Zahl, Vorgabewert 100, Minimalwert 10 [%]
| Übung 5 | Einschränkung und Index hinzufügen | Zur Lösung |
Ändern Sie die Tabelle Versicherungsvertrag so, dass die Spalte Vertragsnummer eindeutig ist und einen (ausdrücklich angegebenen) Index benutzt.
| Übung 6 | Einschränkung hinzufügen | Zur Lösung |
Ändern Sie die Tabelle Versicherungsnehmer so, dass die Spalte Eigener_Kunde nur die Werte 'J' und 'N' annehmen darf.
| Lösung zu Übung 1 | Definitionen | Zur Übung |
Die Aussagen 1, 3, 4, 6, 7, 8, 10 sind wahr, die Aussagen 2, 5, 9, 11 sind falsch.
| Lösung zu Übung 2 | Tabellendefinition | Zur Übung |
- Es fehlen die Klammern um die gesamte Auflistung aller Einzelheiten, und es fehlen Kommata nach jedem einzelnen Bestandteil. Dagegen ist das letzte Komma falsch.
- Zeilen 2/3: Diese Zeilen gehören an das Ende: Zuerst müssen die Spalten festgelegt werden, dann kann darauf Bezug genommen werden.
- Zeile 4: Es fehlt die Größenangabe für die Zeichenkette. Ob die Reihenfolge der Teile passt, hängt vom DBMS ab.
- Zeile 2 und 5: Doppelte Festlegung des Primary Key. Es bezieht sich zwar in beiden Fällen auf dieselbe Spalte, es sind aber wegen des CONSTRAINT-Namens unterschiedliche Definitionen.
| Lösung zu Übung 3 | Einschränkungen allgemein definieren | Zur Übung |
create Table PLZ_Codes
( ID INTEGER not null auto_increment
CONSTRAINT PLZ_PK primary key,
Land CHAR ( 2) not null default 'DE',
Code VARCHAR(10) not null, -- auch CHAR(10) ist denkbar
Ort VARCHAR(30) not null,
CONSTRAINT PLZ_UK unique (Land, Code)
);
| Lösung zu Übung 4 | Spalten mit Einschränkungen hinzufügen | Zur Übung |
ALTER TABLE Versicherungsvertrag
ADD [COLUMN] Basispraemie DECIMAL
DEFAULT 500 NOT NULL
CONSTRAINT Vertrag_Basispraemie_Check CHECK(Basispraemie > 0),
ADD [COLUMN] Praemiensatz INTEGER
DEFAULT 100 NOT NULL
CONSTRAINT Vertrag_Praemiensatz_Check CHECK(Praemiensatz >= 10);
| Lösung zu Übung 5 | Einschränkung und Index hinzufügen | Zur Übung |
ALTER TABLE Versicherungsvertrag
ADD CONSTRAINT Versicherungsvertrag_Nummer UNIQUE (Vertragsnummer)
USING INDEX Versicherungsvertrag_Nummer_UK;
| Lösung zu Übung 6 | Einschränkung hinzufügen | Zur Übung |
ALTER TABLE Versicherungsnehmer
ADD CONSTRAINT Versicherungsnehmer_Eigener_Kunde
CHECK( Eigener_Kunde = 'J' OR Eigener_Kunde = 'N' );
Siehe auch
[Bearbeiten]In den folgenden Kapiteln sind Einzelheiten zu finden:
Bei Wikipedia gibt es grundlegende Erläuterungen:
- Globally Unique Identifier (GUID) als eindeutige Kennung
- Versicherungsnummer der deutschen Rentenversicherung