| PostgreSQL: Das Offizielle Handbuch | ||||
|---|---|---|---|---|
| Zurück | Schnell zurück | Schnell nach vorne | Nach vorne | |
CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE tabellenname (
{ spaltenname datentyp [ DEFAULT vorgabeausdruck ] [ spalten_constraint [, ... ] ]
| tabellen_constraint } [, ... ]
)
[ INHERITS ( elterntabelle [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
wobei spalten_constraint Folgendes sein kann:
[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | UNIQUE | PRIMARY KEY |
CHECK (ausdruck) |
REFERENCES reftabelle [ ( refspalte ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE aktion ] [ ON UPDATE aktion ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
und tabellen_constraint Folgendes sein kann:
[ CONSTRAINT constraint_name ]
{ UNIQUE ( spalten_name [, ... ] ) |
PRIMARY KEY ( spalten_name [, ... ] ) |
CHECK ( ausdruck ) |
FOREIGN KEY ( spalten_name [, ... ] ) REFERENCES reftabelle [ ( refspalte [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE aktion ] [ ON UPDATE aktion ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]CREATE TABLE erzeugt eine neue, anfänglich leere Tabelle in der aktuellen Datenbank. Der Eigentümer der Tabelle wird der Benutzer sein, der den Befehl ausführt.
Wenn ein Schemaname angegeben wurde (zum Beispiel CREATE TABLE mein_schema.meine_tabelle ...), dann wird die Tabelle im angegeben Schema erzeugt, ansonsten wird sie im aktuellen Schema erzeugt. Temporäre Tabellen existieren in einem besonderen Schema, also darf bei der Erzeugung einer temporären Tabelle kein Schemaname angegeben werden. Der Tabellenname muss sich von den Namen aller Tabellen, Sequenzen, Indexe und Sichten im selben Schema unterscheiden.
CREATE TABLE erzeugt außerdem automatisch einen Datentyp, der den zusammengesetzten Datentyp einer Tabellenzeile darstellt. Daher kann eine Tabelle auch nicht den selben Namen wie ein Datentyp im selben Schema haben.
Eine Tabelle kann höchstens 1600 Spalten haben. (In der Praxis ist die Grenze niedriger, weil die Gesamtgröße einer Zeile begrenzt ist.)
Die optionalen Constraint-Klauseln geben Constraints oder Prüfungen an, die von neuen oder aktualisierten Zeilen erfüllt werden müssen, damit die Einfüge- oder Aktualisierungsaktion durchgeführt werden kann. Ein Constraint ist ein SQL-Objekt, das auf verschiedene Weisen gültige Werte für die Tabelle bestimmen kann.
Constraints können auf zwei Arten definiert werden: als Tabellen-Constraints und als Spalten-Constraints. Ein Spalten-Constraint wird als Teil einer Spaltendefinition angegeben. Ein Tabellen-Constraint gehört nicht zu einer bestimmten Spalte und kann mehr als eine Spalte verwenden. Jeder Spalten-Constraint kann auch als Tabellen-Constraint geschrieben werden; ein Spalten-Constraint ist nur eine alternative Schreibweise, wenn der Constraint sich auf nur eine Spalte auswirkt.
Wenn angegeben, dann wird die Tabelle als temporäre Tabelle erzeugt. Temporäre Tabellen werden automatisch entfernt, wenn die Sitzung beendet wird. Bestehende permanente Tabellen mit dem selben Namen sind in der aktuellen Sitzung nicht sichtbar, solange die temporäre Tabelle existiert, außer wenn man auf sie mit schemaqualifizierten Namen zugreift. Indexe für temporäre Tabellen sind automatisch ebenfalls temporär.
Das Wort LOCAL ist optional. Siehe aber unter Kompatibilität.
Der Name der zu erzeugenden Tabelle (möglicherweise mit Schemaqualifikation).
Der Name einer in der Tabelle zu erzeugenden Spalte.
Der Datentyp der Spalte. Das kann auch eine Arrayangabe sein.
Die DEFAULT-Klausel weist der Spalte, in deren Definition sie auftritt, einen Vorgabewert zu. Der Wert kann ein beliebiger Ausdruck ohne Variablen sein (keine Unteranfragen oder Verweise auf andere Spalten in der selben Tabelle). Der Datentyp des Ausdrucks muss mit dem Datentyp der Spalte übereinstimmen.
Der Vorgabewert wird bei jeder Einfügeoperation verwendet, die keinen Wert für die Spalte angibt. Wenn für eine Spalte kein Vorgabewert definiert ist, dann ist der NULL-Wert der Vorgabewert.
Die optionale INHERITS-Klausel gibt eine Liste von Tabellen an, von denen die neue Tabelle alle Spalten erbt. Wenn der gleiche Spaltenname in mehr als einer Elterntabelle existiert und die Datentypen nicht in allen übereinstimmen, dann ist das ein Fehler. Wenn es keinen Konflikt gibt, dann werden die doppelten Spalten in der neuen Tabelle zu einer einzelnen verschmolzen. Wenn die Spaltenliste der neuen Tabelle eine Spalte enthält, die auch geerbt ist, dann muss der Datentyp dabei ebenfalls übereinstimmen und die Spaltendefinitionen werden in eine verschmolzen. Die geerbten und neuen Spaltendefinitionen mit dem selben Namen müssen jedoch nicht die gleichen Constraints haben; alle Constraints aus allen Definitionen werden zusammengefasst und alle auf die neue Spalte angewendet. Wenn die neue Tabelle ausdrücklich einen Vorgabewert für die Spalte angibt, dann hat dieser Vorgabewert vor allen geerbten Vorgabewerten Vorrang. Ansonsten müssen alle Elterntabellen, die einen Vorgabewert angeben, alle den selben haben oder ein Fehler wird ausgelöst.
Diese optionale Klausel gibt an, ob die Zeilen in der neuen Tabelle OIDs haben sollten. Wenn nichts angegeben ist, dann werden OIDs erzeugt. (Wenn die Tabelle von einer Tabelle erbt, die OIDs hat, dann wird immer WITH OIDS angenommen, selbst wenn WITHOUT OIDS angegeben wurde.)
Wenn man WITHOUT OIDS angibt, dann spart man die Erzeugung von OIDs für die Zeilen der Tabelle. Das kann sich bei großen Tabellen lohnen, weil es den OID-Verbrauch verringert und dadurch nicht zum Überlauf des 32-Bit-OID-Zählers beiträgt. Wenn der Zähler einmal übergelaufen ist, dann sind OIDs nicht mehr einmalig, wodurch ihr Nutzen erheblich verringert wird.
Ein optionaler Name für einen Spalten- oder Tabellen-Constraint. Wenn kein Name angegeben ist, dann erzeugt das System einen.
Diese Spalte darf keine NULL-Werte enthalten.
Diese Spalte darf NULL-Werte enthalten. Das ist die Voreinstellung.
Diese Klausel ist nur für die Kompatibilität mit Datenbanken, die nicht dem SQL-Standard folgen. Sie sollte nicht in neuen Anwendungen verwendet werden.
Der UNIQUE-Constraint gibt an, dass eine Gruppe von einer oder mehreren verschiedenen Spalten in einer Tabelle in allen Zeilen verschiedene Werte enthalten muss. Das Verhalten eines Tabellen-Unique-Constraint ist das selbe wie bei einem Spalten-Constraint, mit der zusätzlichen Möglichkeit, dass er mehrere Spalten enthalten kann.
NULL-Werte zählen bei einem Unique Constraint nicht als gleich.
Jeder Tabellen-Unique-Constraint muss eine Spaltengruppe nennen, die sich von den Spaltengruppen aller anderen Unique-Constraints und Primärschlüssel-Constraints unterscheidet. (Ansonsten wäre es einfach nochmal der selbe Constraint.)
Ein Primärschlüssel-Constraint gibt an, dass eine Spalte oder Spalten in allen Zeilen einer Tabelle verschiedene Nicht-NULL-Werte haben müssen. Im Prinzip ist PRIMARY KEY einfach eine Kombination aus UNIQUE und NOT NULL, aber wenn man eine Gruppe von Spalten als Primärschlüssel identifiziert, dann enthält das auch Informationen über das Design des Schemas, weil ein Primärschlüssel anzeigt, dass diese Spalten als eindeutige Identifikation von Zeilen verwendet werden können.
Eine Tabelle kann nur einen Primärschlüssel haben, egal ob als Spalten-Constraint oder als Tabellen-Constraint.
Der Primärschlüssel-Constraint sollte eine Spaltengruppe nennen, die sich von den Spaltengruppen aller Unique-Constraints in der selben Tabelle unterscheidet.
Die CHECK-Klausel gibt einen Ausdruck an, der ein Ergebnis vom Typ boolean zurückgibt, den eine neue oder aktualisierte Zeile erfüllen muss, damit die Einfüge- oder Aktualisierungsoperation zugelassen wird. Ein Check-Constraint, der als Spalten-Constraint angegeben ist, sollte nur den Wert dieser Spalte verwenden; ein Ausdruck, der in einem Tabellen-Constraint vorkommt, kann dagegen mehrere Spalten verwenden.
Gegenwärtig kann ein CHECK-Ausdruck keine Unteranfragen enthalten oder auf Variablen außer den Spalten der aktuellen Zeile zugreifen.
Dieses Klauseln erzeugen einen Fremdschlüssel-Constraint, welcher bestimmt, dass eine Gruppe von Spalten (oder eine Spalte) der neuen Tabelle nur Werte enthalten darf, die mit Werte in den Spalten refspalte einer anderen Tabelle reftabelle übereinstimmen. Wenn refspalte weggelassen wird, dann wird der Primärschlüssel von reftabelle verwendet. Die Spalten, auf die der Constraint in der anderen Tabelle verweist, müssen die Spalten eines Unique Constraint oder des Primärschlüssels der Tabelle sein.
Ein in diese Spalten eingefügter Wert wird mit den Werten der anderen Tabelle anhand des angegebenen Match-Typs verglichen. Es gibt drei Match-Typen: MATCH FULL, MATCH PARTIAL und MATCH SIMPLE; letzterer ist die Voreinstellung, wenn nichts angegeben wurde. Bei MATCH FULL dürfen keine Spalten eines mehrspaltigen Fremdschlüssels den NULL-Wert haben, außer wenn alle Spalten im Fremdschlüssel den NULL-Wert haben. Bei MATCH SIMPLE können einige Spalten im Fremdschlüssel den NULL-Wert haben und andere nicht. MATCH PARTIAL ist noch nicht implementiert.
Wenn die Daten in der Tabelle, auf die der Fremdschlüssel verweist, geändert werden, dann werden in der neuen Tabelle bestimmte Aktionen ausgeführt. Die Klausel ON DELETE bestimmt, was getan werden soll, wenn eine Zeile in der Tabelle, auf die ein Wert verweist, gelöscht werden soll. Die Klausel ON UPDATE bestimmt gleichermaßen, was getan werden soll, wenn eine Zeile, auf die ein Wert verweist, aktualisiert wird und so einen neuen Wert erhält. Die möglichen Aktionen für beide Klauseln sind folgende:
Erzeuge einen Fehler, der anzeigt, dass der Lösch- oder Aktualisierungsvorgang einen Fremdschlüssel verletzen würde. Das ist die Standardaktion.
Das Gleiche wie NO ACTION.
Lösche alle Zeilen, die auf die zu löschende Zeile verweisen, beziehungsweise ändere alle Zeilen, die auf die zu aktualisierende Zeile verweisen, in den neuen Wert.
Setze die Spalten, die auf die zu löschende oder aktualisierende Zeile verweisen, auf den NULL-Wert.
Setze die Spalten, die auf die zu löschende oder aktualisierende Zeile verweisen, auf ihren Vorgabewert.
Wenn eine Primärschlüsselspalte oft aktualisiert wird, dann sollte man eventuell einen Index für die Spalten des Fremdschlüssel erzeugen, damit die Aktionen NO ACTION und CASCADE für die Fremdschlüsselspalte effizienter ausgeführt werden können.
Wenn DEFERRABLE angegeben ist, dann kann die Prüfung eines Constraints bis an das Ende der Transaktion verschoben werden. Ein Constraint, der nicht verschoben werden kann, wird sofort nach jedem Befehl geprüft. Wenn die Prüfung verschoben werden kann, dann kann das mit dem Befehl SET CONSTRAINTS kontrolliert werden. NOT DEFERRABLE (nicht verschiebbar) ist die Voreinstellung. Gegenwärtig wird diese Klausel nur bei Fremdschlüssel-Constraints akzeptiert. Alle anderen Constraint-Typen können nicht verschoben werden.
Wenn ein Constraint verschoben werden kann, dann bestimmt diese Klausel, wann der Constraint in der Standardeinstellung geprüft wird. Im Fall von INITIALLY IMMEDIATE (die Voreinstellung) wird er nach jedem Befehl geprüft, im Fall von INITIALLY DEFERRED erst am Ende der Transaktion. Mit dem Befehl SET CONSTRAINTS kann die Prüfzeit geändert werden.
Wenn eine Anwendung die OIDs verwendet um Tabellenzeilen eindeutig zu identifizieren, dann sollte ein Unique Constraint für die Spalte oid dieser Tabelle erzeugt werden, damit die OIDs die Zeilen immer noch eindeutig bestimmen, wenn der Zähler überlaufen sollte. Sie sollten nicht davon ausgehen, dass OIDs in allen Tabellen einmalig sind; wenn Sie eine datenbankweite eindeutige Identifikation von Zeilen benötigen, dann verwenden Sie dazu die Kombination aus tableoid und der Zeilen-OID. (In der Zukunft wird es wahrscheinlich einen getrennten OID-Zähler für jede Tabelle geben, und dann ist es notwendig, nicht nur empfehlenswert, tableoid für eine datenbankweit eindeutige Identifikation von Zeilen zu verwenden.)
Tipp: WITHOUT OIDS sollten Sie nicht verwenden, wenn die Tabelle keine Primärschlüssel hat, weil es ohne OID und eindeutigen Schlüssel schwierig wird, bestimmte Zeilen eindeutig zu identifizieren.
PostgreSQL erzeugt für jeden Unique Constraint und Primärschlüssel automatisch einen Index um die Funktion des Constraints zu unterstützen. Es ist also nicht nötig, für Primärschlüsselspalten noch einen ausdrücklichen Index zu erzeugen.
Unique Constraints und Primärschlüssel werden in der gegenwärtigen Implementierung nicht vererbt. Dadurch wird die Kombination aus Vererbung und Unique Constraint ziemlich unbrauchbar.
Erzeuge eine Tabelle filme und eine Tabelle verleihe:
CREATE TABLE filme (
code char(5) CONSTRAINT schlüssel PRIMARY KEY,
titel varchar(40) NOT NULL,
vid integer NOT NULL,
prod_datum date,
genre varchar(10),
länge interval hour to minute
);
CREATE TABLE verleihe (
vid integer PRIMARY KEY DEFAULT nextval('sequenz'),
name varchar(40) NOT NULL CHECK (name <> '')
);
Erzeuge eine Tabelle mit einem 2-dimensionalen Array:
CREATE TABLE array (
vektor int[][]
);
Definiere die Tabelle filme mit einem Tabellen-Unique-Constraint. Tabellen-Unique-Constraints können eine oder mehrere Spalten einer Tabelle umfassen.
CREATE TABLE filme (
code char(5),
titel varchar(40),
vid integer,
prod_datum date,
genre varchar(10),
länge interval hour to minute
CONSTRAINT produktion UNIQUE (prod_datum)
);
Definiere eine Tabelle mit Spalten-Check-Constraint:
CREATE TABLE verleihe (
vid integer CHECK (vid > 100),
name varchar(40)
);
Definiere eine Tabelle mit Tabellen-Check-Constraint:
CREATE TABLE verleihe (
vid integer,
name varchar(40)
CONSTRAINT con1 CHECK (vid > 100 AND name <> '')
);
Definiere die Tabelle filme mit einem Primärschlüssel-Tabellen-Constraint. Primärschlüssel-Tabellen-Constraints können eine oder mehrere Spalten einer Tabelle umfassen.
CREATE TABLE filme (
code char(5),
titel varchar(40),
vid integer,
prod_datum date,
genre varchar(10),
länge interval hour to minute,
CONSTRAINT code_titel PRIMARY KEY(code, titel)
);
Definiere die Tabelle verleihe mit einem Primärschlüssel. Die folgenden beiden Beispiele sind gleichbedeutend, das erste verwendet die Tabellen-Constraint-Schreibweise, das zweite die Spalten-Constraint-Schreibweise.
CREATE TABLE verleihe (
vid integer,
name varchar(40),
PRIMARY KEY(vid)
);
CREATE TABLE verleihe (
vid integer PRIMARY KEY,
name varchar(40)
);
Folgender Befehl weist der Spalte name eine Konstante als Vorgabewert zu, sorgt dafür, dass der Vorgabewert der Spalte vid der nächste Wert aus einem Sequenzobjekt ist, und richtet es ein, dass der Vorgabewert der Spalte geändert die Zeit ist, zu der die Zeile eingefügt wird.
CREATE TABLE verleihe (
name varchar(40) DEFAULT 'Luso-Film',
vid integer DEFAULT nextval('verleih_sequenz'),
geändert timestamp DEFAULT current_timestamp
);
Definiere die Tabelle verleihe mit zwei NOT NULL-Spalten-Constraints, von denen einer einen ausdrücklichen Namen erhält:
CREATE TABLE verleihe (
vid integer CONSTRAINT nicht_null NOT NULL,
name varchar(40) NOT NULL
);
Definiere einen Unique Constraint für die Spalte name:
CREATE TABLE verleihe (
vid integer,
name varchar(40) UNIQUE
);Das Gleiche kann folgendermaßen als Tabellen-Constraint geschrieben werden:
CREATE TABLE verleihe (
vid integer,
name varchar(40),
UNIQUE (name)
);
Der Befehl CREATE TABLE ist konform mit dem SQL-Standard, mit den im Folgenden beschriebenen Ausnahmen.
Obwohl die Syntax von CREATE TEMPORARY TABLE dem SQL-Standard ähnelt, ist die Auswirkung nicht die selbe. Im Standard werden temporäre Tabellen einmal definiert und existieren dann automatisch (mit leerem Inhalt) in jeder Sitzung, die sie benötigt. In PostgreSQL muss dagegen jede Sitzung CREATE TEMPORARY TABLE ausführen, wenn sie eine temporäre Tabelle benötigt. Dadurch kann jede Sitzung den selben Namen für temporäre Tabellen mit unterschiedlichen Aufgaben verwenden, wohingegen nach dem SQL-Standard alle Instanzen einer bestimmten temporären Tabelle die gleiche Struktur haben.
Das vom Standard vorgeschriebene Verhalten für temporäre Tabellen wird weithin ignoriert. Das Verhalten von PostgreSQL in diesem Punkt ist vergleichbar mit mehreren anderen SQL-Datenbanken.
Der im Standard angegebene Unterschied zwischen globalen und lokalen temporären Dateien ist in PostgreSQL nicht vorhanden, da diese Unterscheidung vom Konzept des Moduls abhängt, aber PostgreSQL hat keine Module.
Der SQL-Standard bestimmt, dass CHECK-Spalten-Constraints nur die Spalte, für die sie gelten, verwenden dürfen; nur CHECK-Tabellen-Constraints dürfen auf mehrere Spalten zugreifen. PostgreSQL setzt diese Einschränkung nicht durch; es behandelt Spalten- und Tabellen-Constraints gleich.
Der NULL-„Constraint“ (eigentlich ein Nicht-Constraint) ist eine PostgreSQL-Erweiterung des SQL-Standards, die für die Kompatibilität mit anderen Datenbanken gedacht ist (und als Gegenstück für den NOT NULL-Constraint). Da er das Standardverhalten jeder Spalte ausdrückt, ist er nur Dekoration.
| Zurück | Zum Anfang | Nach vorne |
| CREATE SEQUENCE | Nach oben | CREATE TABLE AS |