Home - SQL Befehle - Datenmanipulation - Objekt erzeugen www.sqldocu.com
zurück vor



Vorschriften erstellen (CONSTRAINTS)
Beschreibung
 top
Vorschriften werden mit dem Schlüsselwort CONSTRAINT (übersetzt: Zwang) erzeugt. Meistens werden sie mit der/den Tabelle/Tabellen, auf die sie sich beziehen, definiert (siehe Tabellen erstellen). Jede Vorschrift (CONSTRAINT) muss einen eindeutigen Namen innerhalb der DB bzw. des Schemas erhalten. Vorschriften werden hauptsächlich dazu verwendet, Daten einer DB konsistent zu halten. Oft sind Tabellen von anderen abhängig. In unseren Beispiel Tabellen enthält die Tabelle crew alle CrewMitglieder der Föderation. Zusätzlich wird für jedes CrewMitgliede die Nummer des Raumschiffes abgespeichert, auf dem es arbeitet. Jede Nummer ist in der Tabelle raumschiff der Name eines Raumschiffes zugeordnet. Wird aus raumschiff ein Datensatz gelöscht, müssen die Datensätze aller CrewMitglieder die sich auf den gelöschten Datensatz beziehen ebenfalls geändert werden. Wie sie geändert werden hängt von der Implementierung ab (z.B: betroffenen Datensätze werden ebenfalls gelöscht, oder sie erhalten Nummer eines neuen Raumschiffes...). Würde dies nicht geschehen so enthielten einige Datensätze in der Tabelle crew Nummern von Raumschiffen, die in der Tabelle raumschiff nicht existieren. Somit sind die Daten dieser beiden Tabellen nicht mehr konstisten. Wenn alle Daten konsistent sind spricht man von referenzieller Integrität.
CONSTRAINTS werden hauptsächlich verwendet um die referenzielle Integrität der Datensätze einer RDBMS zu gewährleisten.
Leider wird der unten beschriebene Syntax (entspricht dem ANSI Standard) nicht von allen RDBMS unterstützt. In MySQL gibt es gravierende Abweichungen vom Standard. In den gezeigten Beispielen wird auf die Abweichungen von MySQL eingegangen.


Syntax
 top
Syntax ConstraintsBedingung
CONSTRAINT
ConstraintName
CHECK (Suchbedingung)|UNIQUE (SpaltenName [,..])|PRIMARY KEY (SpaltenName [,..])|
FOREIGN KEY (SpaltenName [,..]) Referenzbedingung
Syntax ReferenzBedingung
REFERENCES
TabellenName
( SpaltenName [,..] )   optional  
ON DELETE Aktion|UPDATE Aktion   optional  
| ... trennt Syntaxmöglichkeiten (entweder oder)
[] ... Syntax innerhalb dieser Klammern ist optional
optional ... Syntax in dieser Zeile ist optional
Hinweis:
Alle oben angeführten Optionen entsprechen dem ANSI Standard. Im Moment wird der beschriebene Syntax nicht vollständig von RDBMS unterstützt. In MySQL gibt es gravierende Abweichungen vom Standard. Wenn du CONSTRAINT mit MySQL verwenden möchtest solltest du immer die aktuelle Version von MySQL verwenden.

Beispiele:
CREATE TABLE meineTabelle1 (Spalte1 VARCHAR(255), Spalte2 INTEGER)
CREATE TABLE root.meineTabelle1 (Spalte1 INTEGER)
CREATE TABLE meineTabelle1 (
  Spalte1 INTEGER NOT NULL,
  Spalte2 DECIMAL(7,2),
  CONSTRAINT meinConstraint1 PRIMARY KEY (Spalte2,Spalte4),
  CONSTRAINT meinConstraint2 FOREIGN KEY REFERENCES meineTabelle2(Spalte3)
  )
Beispiel Tabelle:
 top
Diese Tabellen werden verwendet um SQL besser darstellen zu können.


TabelleName: crew

Spaltenname name nr_schiff
Reihe 1 James Kirk 1
Reihe 2 Spock 1
Reihe 3 Mc Coy 1
Reihe 4 Scotty 1
Reihe 5 Uhura 1
Reihe 6 Jean-Luc Picard 2
Reihe 7 1. Offizier 2
Reihe 8 2. Offizier 2
Reihe 9 3. Offizier 2


TabelleName: raumschiff
  status=0 —> Raumschiff einsatzbereit
  status=1 —> Raumschiff in Reparatur

Spaltename nr_schiff name status
Reihe 1 1 U.S.S. Enterprise 1
Reihe 2 2 U.S.S. Stargazer 0


CHECK Constraint
 top
CREATE TABLE crew (
  name VARCHAR(255),
  nr_schiff INTEGER,
  CONSTRAINT pruefe_nummer CHECK (nr_schiff >0)
)

Es wird eine Tabelle mit dem Namen crew erzeugt. Mit crew wird eine Vorschrift mit dem Namen pruefe_nummer angelegt. pruefe_nummer stellt sicher, dass in der Spalte nr_schiff keine negativen Werte gespeichert werden können. Die CHECK Bedingung kann beliebig erweitert werden:
z.B: CHECK (nr_schiff > 0 AND nr_schiff < 100)
(siehe Vergleichsopterationen).
CHECK Constraints können dafür verwendet werden, Wertebereiche von Spalten einzuschränken, nur bestimmte Werte zu zulassen usw.
Hinweis: MySQL unterstützt keine CHECK Constaints!

PHP Syntax     PERL Syntax

UNIQUE Constraint
 top
CREATE TABLE crew (
  name VARCHAR(255),
  nr_schiff INTEGER,
  CONSTRAINT name_index UNIQUE (name)
)

Es wird eine Tabelle mit dem Namen crew erzeugt. Mit crew wird eine Vorschrift mit dem Namen name_index angelegt. name_index stellt sicher, dass in der Spalte name kein Wert mehrfach vorkommt. Leere Felder sind in der Spalte name erlaubt.

PHP Syntax     PERL Syntax

PRIMARY KEY Constraint
 top
CREATE TABLE crew (
  name VARCHAR(255),
  nr_schiff INTEGER,
  CONSTRAINT name_index PRIMARY KEY (name)
)

Es wird eine Tabelle mit dem Namen crew erzeugt. Mit crew wird eine Vorschrift mit dem Namen name_index angelegt. name_index stellt sicher, dass in der Spalte name kein Wert mehrfach vorkommt. Leere Felder sind in der Spalte name nicht erlaubt.
Für PRIMARY KEY können auch mehrere Spalten herangezogen werden. Der Datensatz des PRIMARY KEYs setzt sich in diesem Fall aus den verwendeten Spalten zusammen. Jeder Datensatz muss wiederum eindeutig sein.
PHP Syntax     PERL Syntax

FOREIGN KEY Constraint
 top
CREATE TABLE raumschiff (
  nr_schiff INTEGER,
  name VARCHAR(255),
  status INTEGER UNSIGNED,
  CONSTRAINT raumschiff_pk PRIMARY KEY (nr_schiff)
) 		PARENT TABLE

CREATE TABLE crew (
  name VARCHAR(255),
  nr_schiff INTEGER,
  CONSTRAINT crew_fk FOREIGN KEY (nr_schiff)
  REFERENCES raumschiff(nr_schiff)
) 		CHILD TABLE

FOREIGN KEY CONSTRAINTS werden verwendet um die referenzielle Integrität von DB (SCHEMA) zu gewährleisten (siehe Beschreibung). In unseren Beispiel enthält die Tabelle crew alle CrewMitglieder der Föderation. Zusätzlich wird in der zweiten Spalte für jedes CrewMitgliede die Nummer des Raumschiffes abgespeichert, auf dem es arbeitet. Die Tabelle raumschiff besteht aus 3 Spalten. Die erste Spalte enthält die Nummer des Raumschiffs, die zweite Spalte enthält den Namen des Raumschiffs und die dritte Spalte ist hier nicht von Belang.
Man erkennt die erste Spalte der Tabelle raumschiff und die zweite Spalte der Tabelle crew miteinander korrelieren.
Wird aus der Tabelle raumschiff ein Datensatz (Raumschiff mit entsprechender Nummer) gelöscht, muss in der Tabelle crew jeder Datensatz (CrewMitglied das auf dem "gelöschten" Raumschiff gearbeitet hat) der auf den gelöschten Datsatz referenziert, geändert werden.
Die Tabelle crew hängt somit von der Tabelle raumschiff ab. Man bezeichnet daher die Tabelle raumschiff als PARENT TABLE (Eltern Tabelle) und die Tabelle crew als CHILD TABLE (Kind Tabelle).
Aus Sicht der Tabelle raumschiff ist die zweite Spalte der Tabelle crew ein Fremdschlüssel (übersetzt: FOREIGN KEY).
Durch das Anlegen des FOREIGN KEYs ist es nicht möglich, ein Raumschiff in der Tabelle raumschiff zu löschen, solange CrewMitglieder in der Tabelle crew existieren, die auf diesem Raumschiff arbeiten. Einiges ist beim Anlegen eines FOREIGN KEYs zu beachten:
  • FOREIGN KEY der CHILD Tabelle und die korrelierende Spalte der PARENT Tabelle müssen denselben Datentyp besitzen.
  • Für beide Tabellen muss der Benutzer die entsprechenden Rechte besitzen.
CREATE TABLE raumschiff (
  nr_schiff INTEGER,
  name VARCHAR(255),
  status INTEGER UNSIGNED,
  CONSTRAINT raumschiff_pk PRIMARY KEY (nr_schiff)
) 		PARENT TABLE

CREATE TABLE crew (
  name VARCHAR(255),
  nr_schiff INTEGER,
  CONSTRAINT crew_fk FOREIGN KEY (nr_schiff)
  REFERENCES raumschiff(nr_schiff)
  ON DELETE CASCADE
) 		CHILD TABLE

Die Option ON DELETE ermöglicht es festzulegen, welche Aktion das RDBMS durchführt wenn in der PARENT Tabelle ein Datensatz (hier: Raumschiff mit Nummer) gelöscht wird.
Die Option ON UPDATE ermöglicht es festzulegen, welche Aktion das RDBMS durchführt wenn in der PARENT Tabelle ein Datensatz (hier: Raumschiff mit Nummer) geändert wird.
Folgende Aktionen sieht der ANSI Standard vor:
  • CASCADE   Wird ein Datensatz (hier Raumschiff mit Nummer) in der PARENT Tabelle gelöscht, löscht das RDBMS automatisch alle abhänigen Zeilen in der CHILD Tabelle (hier alle CrewMitglieder des gelöschten Raumschiffs)
  • SET NULL   Alle abhängigen Zeilen (hier: CrewMitglieder des gelöschten Raumschiffs) in der CHILD Tabelle werden auf NULL gesetzt.
  • SET DEFAULT   Alle abhängigen Zeilen (hier: CrewMitglieder des gelöschten Raumschiffs) in der CHILD Tabelle werden auf den DEFAULT Wert gesetzt.
  • RESTRICT   Es können keine Zeilen in der PARENT Tabelle gelöscht werden, bevor nicht alle abhängigen Zeilen in der CHILD Tabelle gelöscht wurden.
  • NO ACTION   Standard Einstellung wird verwendet. Nach ANSI Standard ist dies RESTRICT.
Hinweis: Nicht alle RDBMS unterstützen den hier vorgeführten ANSI Standard.

MySQL unterstützt im Moment die Option FOREIGN KEY nicht. In Zukunft will MySQL diese Option implementieren. Ansätze sind bereits vorhanden. Um Source Code möglichst Plattform unabhängig programmieren zu könnnen, akzeptieren die neusten Versionen der MySQL Compiler die Option FOREIGN KEY. Jedoch bewirkt diese Option in MySQL derzeit nichts.
MySQL Syntax (wird von MySQL Compiler akzeptiert, bewirkt jedoch nichts).

CREATE TABLE raumschiff (
  nr_schiff INTEGER NOT NULL,
  name VARCHAR(255),
  status INTEGER UNSIGNED,
  CONSTRAINT raumschiff_pk PRIMARY KEY (nr_schiff)
) TYPE=INNODB

CREATE TABLE crew (
  name VARCHAR(255),
  nr_schiff INTEGER,
  INDEX nr_schiff_index (nr_schiff),   CONSTRAINT crew_fk FOREIGN KEY (nr_schiff)   REFERENCES raumschiff(nr_schiff) ) TYPE=INNODB
PHP Syntax     PERL Syntax


top