| Documentation PostgreSQL 8.0.25 | ||||
|---|---|---|---|---|
| Précédent | Arrière rapide | Avance rapide | Suivant | |
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE nom_table (
{ nom_colonne type_données [ DEFAULT
default_expr ] [ contrainte_colonne [ ... ] ]
| contrainte_table
| LIKE table_parent [ { INCLUDING | EXCLUDING }
DEFAULTS ] } [, ... ]
)
[ INHERITS ( table_parent [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]
où contrainte_colonne
est :
[ CONSTRAINT nom_contrainte ]
{ NOT NULL | NULL | UNIQUE [ USING INDEX TABLESPACE espacelogique ] |
PRIMARY KEY [ USING INDEX TABLESPACE espacelogique ] |
CHECK (expression) |
REFERENCES table_reference [ ( colonne_reference ) ] [ MATCH FULL
| MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
et contrainte_table est :
[ CONSTRAINT nom_contrainte ]
{ UNIQUE ( nom_colonne [, ... ] ) [ USING INDEX TABLESPACE espacelogique ] |
PRIMARY KEY ( nom_colonne [, ...
] ) [ USING INDEX TABLESPACE espacelogique ] |
CHECK ( expression ) |
FOREIGN KEY ( nom_colonne [, ...
] ) REFERENCES table_reference [ (
colonne_reference [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]CREATE TABLE créera une nouvelle table initialement vide dans la base de données courante. La table sera la propriété de l'utilisateur qui a lançé cette commande.
Si un nom de schéma est donné (par exemple, CREATE TABLE monschema.matable ...), alors la table est créée dans le schéma spécifié. Sinon, il est créé dans le schéma actuel. Les tables temporaires existent dans un schéma spécial, donc un nom de schéma pourrait ne pas être donné lors de la création d'une table temporaire. Le nom de la table doit être distinct des noms des autres tables, séquences, index ou vues dans le même schéma.
CREATE TABLE crée aussi automatiquement un type de données qui représente le type composé correspondant à une ligne de la table. Du coup, les tables ne peuvent pas avoir le même nom que tout type de données du même schéma.
Les clauses de contrainte optionnelle spécifient les contraintes (ou tests) que les nouvelles lignes ou les lignes mises à jour doivent satisfaire pour qu'une opération d'insertion ou de mise à jour réussisse. Une contrainte est un objet SQL qui aide à définir l'ensemble de valeurs valides de plusieurs façons.
Il existe deux façons de définir des contraintes : les contraintes de table et celles des colonnes. Une contrainte de colonne est définie pour faire partie d'une définition de la colonne. Une définition de la contrainte des tables n'est pas liée à une colonne particulière et elle comprend plus d'une colonne. Chaque contrainte de colonne peut aussi être écrite comme une contrainte de table ; une colonne de contrainte est seulement un outil de notation à utiliser lorsque la contrainte affecte seulement une colonne.
Si spécifié, la table est créée comme une table temporaire. Les tables temporaires sont automatiquement supprimées à la fin d'une session ou, optionnellement, à la fin de la transaction en cours (voir ON COMMIT ci-dessous). Les tables permanentes existantes avec le même nom ne sont pas visibles dans la session en cours alors que la table temporaire existe sauf si elles sont référencées avec les noms qualifiés du schéma. Tous les index créés sur une table temporaire sont aussi automatiquement temporaires.
Optionellement, GLOBAL ou LOCAL peuvent être écrit avant TEMPORARY ou TEMP. Ceci ne fait pas de différence dans PostgreSQL, mais voir Compatibilité.
Le nom (peut-être qualifié par le nom du schéma) de la table à créer.
Le nom d'une colonne à créer dans la nouvelle table.
Le type de données de la colonne. Ceci pourrait inclure des spécificateurs de tableaux. Pour plus d'informations sur les types de données supportés par PostgreSQL, référez-vous à Chapitre 8.
La clause DEFAULT affecte une valeur par défaut pour la colonne dont la définition apparaît à l'intérieur. La valeur est toute expression libre de variable (les sous-requêtes et références croisées aux autres colonnes dans la table en cours ne sont pas autorisées). Le type de données de l'expression par défaut doit correspondre au type de données de la colonne.
L'expression par défaut sera utilisée dans les opérations d'insertion qui ne spécifient pas une valeur pour la colonne. S'il n'y a pas de valeur par défaut pour une colonne, alors la valeur par défaut est NULL.
La clause optionnelle INHERITS spécifie une liste des tables à partir desquelles la nouvelle table hérite automatiquement de toutes les colonnes.
L'utilisation d'INHERITS crée une relation persistante entre la nouvelle table enfant et sa table parent. Les modifications du schéma au(x) parent(s) se propagent normalement aussi aux enfants et, par défaut, les données de la table enfant sont inclus dans les parcours de(s) parent(s).
Si le même nom de colonne existe dans plus d'une table parente, une erreur est rapportée sauf si les types de données des colonnes correspondent à chacune des tables parentes. S'il n'y a aucun conflit, alors les colonnes dupliquées sont assemblées pour former une seule colonne dans la nouvelle table. Si la liste de noms de colonnes de la nouvelle table contient un nom de colonne qui est aussi héritée, le type de données doit correspondre aux colonnes héritées et les définitions de la colonne sont assemblées en une seule. Néanmoins, les déclarations des colonnes héritées et nouvelles du même nom ont besoin de ne pas spécifier des contraintes identiques : toutes les contraintes fournies par toute déclaration sont assemblées et sont toutes appliquées à la nouvelle table. Si la nouvelle table spécifie explicitement une valeur par défaut pour la colonne, cette valeur surcharge toute valeur par défaut des déclarations héritées pour la colonne.Sinon, tout parent spécifiant des valeurs par défaut pour la colonne doit spécifier la même valeur par défaut. Sinon une erreur sera rapportée.
La clause LIKE spécifie une table à partir de laquelle la nouvelle table copie automatiquement tous les noms de colonnes, leur types de données et les contraintes non NULL.
Contrairement à INHERITS, la nouvelle table et la table héritée sont complètement découplées après la fin de la création. Les modifications sur la table originale ne sont pas appliquées à la nouvelle table et il n'est pas possible d'inclure les données de la nouvelle table dans des parcours de l'ancienne table.
Les expressions par défaut pour les définitions des colonnes héritées seront seulement copiées si INCLUDING DEFAULTS est spécifié. Le comportement par défaut est d'exclure les expressions par défaut, ce qui a pour résultat d'avoir des valeurs par défaut NULL pour toutes les colonnes de la nouvelle table.
Cette clause optionnelle spécifie si les lignes de la nouvelle table devraient avoir des OID (identifiants d'objets) qui leur sont affectés. Si ni WITH OIDS ni WITHOUT OIDS ne sont spécifiés, la valeur par défaut dépend du paramètre de configuration default_with_oids. (Si la nouvelle table hérite d'autres tables possédant des OID, alors WITH OIDS est forcé même si la commande indique WITHOUT OIDS.)
Si WITHOUT OIDS est spécifié ou implicite, la nouvelle table ne stocke pas les OID aucun OID ne sera affecté pour une ligne insérée dans cette table. Ceci est généralement considéré comme intéressant pour les grosses tables car il réduit la consommation d'OID et, du coup, annule pour cette table le problème du retour à zéro du compteur d'OID. Une fois que le compteur est revenu à zéro, l'unicité des OID ne peut plus être garantie, ce qui réduit considérablement leur utilité. De plus, exclure les OID d'une table réduit aussi l'espace requis pour stocker la table sur disque de quatre octets par ligne de la table (sur la plupart des machines), améliorant ainsi leur performance.
Pour supprimer les OID d'une table après qu'elle ait été créée, utilisez ALTER TABLE.
Un nom optionnel pour une contrainte de colonne ou de table. S'il n'est pas spécifié, le système génère un nom.
La colonne n'est pas autorisée à contenir des valeurs NULL.
La colonne est autorisée pour contenir des valeurs NULL. Ceci est la valeur par défaut.
Cette clause est seulement fournie pour la compatibilité avec les bases de données SQL non standards. Son utilisation n'est pas encouragée dans les nouvelles applications.
La contrainte UNIQUE spécifie qu'un groupe d'une ou plusieurs colonnes d'une table pourrait seulement contenir des valeurs uniques. Le comportement de la contrainte de table unique est le même que pour les contraintes de colonnes avec la capacité supplémentaire de diviser les colonnes multiples.
Dans le but d'une contrainte unique, les valeurs NULL ne sont pas considérées égales.
Chaque contrainte de table unique doit nommer un ensemble de colonnes qui est différent de l'ensemble des colonnes nommées par toute autre contrainte unique ou de clé primaire définie pour la table. (Sinon, cela pourrait être juste la même contrainte donnée deux fois.)
La contrainte de clé primaire spécifie qu'une ou plusieurs colonnes d'une table pourraient contenir seulement des valeurs uniques, non NULL. Techniquement, PRIMARY KEY est simplement une combinaison de UNIQUE et NOT NULL, mais identifier un ensemble de colonnes comme clé primaire fournit aussi des métadonnées sur le concept du schéma, car une clé primaire implique que d'autres tables pourraient se lier à cet ensemble de colonnes comme un unique identifiant pour les lignes.
Seule une clé primaire peut être spécifiée pour une table, s'il s'agit d'une contrainte de colonne ou de table.
La contrainte de clé primaire devrait nommer un ensemble de colonnes qui est différent des autres ensembles de colonnes nommés par une contrainte unique définie pour la même table.
La clause CHECK spécifie une expression produisant un résultat booléen que les nouvelles lignes ou que les lignes mises à jour doivent satisfaire pour qu'une opération d'insertion ou de mise à jour réussisse. Les expressions évaluant à TRUE ou UNKNOWN réussissent. Si une ligne d'une opération d'insertion ou de mise à jour produit un résultat FALSE, une exception est levée et l'insertion ou la mise à jour ne modifie pas la base de données. Une contrainte de vérification spécifiée comme une contrainte de colonne devrait seulement référencer la valeur de la colonne alors qu'une expression apparaissant dans une contrainte de table pourrait référencer plusieurs colonnes.
Actuellement, les expressions CHECK ne peuvent ni contenir des sous-requêtes ni se référer à des variables autres que les colonnes de la ligne actuelle.
Ces clauses spécifient une contrainte de clé étrangère, ce qui requiert qu'un groupe d'une ou plusieurs colonnes de la nouvelle table doit seulement contenir des valeurs correspondant aux valeurs dans le(s) colonne(s) référencée(s) de quelques lignes de la table référencée. Si colonne_reference est omis, la clé primaire de la table_reference est utilisée. Les colonnes référencées doivent être les colonnes d'une contrainte unique ou de clé primaire dans la table référencée.
Une valeur insérée dans les colonnes référencées est comparée aux valeurs de la table référencée et des colonnes référencées en utilisant le type correspondant donné. Il existe trois types de correspondance : MATCH FULL, MATCH PARTIAL et MATCH SIMPLE, qui est aussi la valeur par défaut. MATCH FULL n'autorisera pas une colonne d'une clé étrangère composée de plusieurs colonnes pour être NULL sauf si les colonnes de clés étrangères sont nulles. MATCH SIMPLE autorise quelques colonnes de clé étrangère pour être NULL alors que les autres parties de la clé étrangère ne sont pas nulles. MATCH PARTIAL n'est pas encore implémenté.
En plus, lorsque les données des colonnes référencées sont modifiées, certaines actions sont réalisées sur les données dans les colonnes de cette table. La clause ON DELETE spécifie l'action à réaliser lorsqu'une ligne référencée de la table référencée est en cours de suppression. De la même façon, la clause ON UPDATE spécifie l'action à réaliser lorsqu'une colonne référencée dans la table référencée est en cours de mise à jour pour une nouvelle valeur. Si la ligne est mise à jour mais la colonne référencée n'est pas réellement modifiée, aucune action n'est réalisée. Les actions référencées autres que la vérification de contrainte NO ACTION ne peuvent pas être déferrées même si la contrainte est déclarée comme déferrable. Il existe les actions possibles suivantes pour chaque clause :
Produit une erreur indiquant que la suppression ou la mise à jour créerait une violation de la contrainte de clé étrangère. Si la contrainte est déferrée, cette erreur sera produite au moment de la vérification de la contrainte s'il existe toujours des lignes de référence. Ceci est l'action par défaut.
Produit une erreur indiquant que la suppression ou mise à jour créera une violation de la contrainte de clé étrangère. Ceci est identique à NO ACTION sauf que la vérification n'est pas déferrable.
Supprime toute ligne référençant la ligne supprimée ou met à jour la valeur de la colonne référencée avec la nouvelle valeur de la colonne référencée, respectivement.
Initialise la colonne de référence à NULL.
Initialise la colonne de référence à sa valeur par défaut.
Si les colonnes référencées sont modifiées fréquemment, il pourrait être conseillé d'ajouter un index vers la colonne de clé étrangère de façon à ce que les actions référentielles associées avec la colonne de clé étrangère puissent être réalisées avec efficacité.
Ceci contrôle si la contrainte peut être déferrée. Une contrainte qui n'est pas déferrable sera vérifiée immédiatement après chaque commande. La vérification des contraintes qui sont déferrables pourraient attendre la fin de la transaction (en utilisant la commande SET CONSTRAINTS). NOT DEFERRABLE est la valeur par défaut. Seulement des contraintes de clé étrangère acceptent réellement cette clause. Tous les autres types de contraintes ne sont pas déferrables.
Si une contrainte est déferrable, cette clause spécifie le temps par défaut pour vérifier la contrainte. Si la contrainte est INITIALLY IMMEDIATE, elle est vérifiée après chaque instruction. Si la contrainte est INITIALLY DEFERRED, elle est vérifiée seulement à la fin de la transaction. Le moment de vérification de la contrainte peut être modifié avec la commande SET CONSTRAINTS.
Le comportement des tables temporaires à la fin d'un bloc de transaction peut se contrôler en utilisant ON COMMIT. Les trois options sont
Aucune action n'est prise à la fin des transactions. Ceci est le comportement par défaut.
Toutes les lignes dans la table temporaire seront détruites à la fin de chaque bloc de transaction. En fait, un TRUNCATE automatique est réalisé à chaque validation.
La table temporaire sera supprimée à la fin du bloc de transaction.
Le tablespace est le nom du tablespace dans lequel est créée la nouvelle table. Si elle n'est pas spécifiée, default_tablespace est utilisée ou le tablespace par défaut de la base de données si default_tablespace est une chaîne vide.
Cette clause permet la sélection du tablespace dans lequel les index associés à une contrainte UNIQUE ou PRIMARY KEY seront créés. Si elle n'est pas spécifiée, default_tablespace est utilisée ou le tablespace par défaut de la base de données si default_tablespace est une chaîne vide.
Utiliser les OID dans les nouvelles applications n'est pas recommandé : si possible, utilisez de préférence un SERIAL ou un autre générateur de séquence comme clé primaire de la table. Néanmoins, si votre application utilise les OID pour identifier des lignes spécifiques d'une table, il est recommandé de créer une contrainte unique sur la colonne oid de cette table pour s'assurer que les OID de la table identifieront les lignes réellement de façon unique même après une remise à zéro du compteur. Évitez d'assumer que les OID sont uniques pour les différentes tables ; si vous avez besoin d'un identifiant unique sur la base de données, utilisez une combinaison de tableoid et de l'OID de la ligne dans ce but.
Astuce : L'utilisation de WITHOUT OIDS n'est pas recommandée pour les tables sans clé primaire car, sans soit un OID soit une clé de données unique, il est difficile d'identifier des lignes spécifiques.
PostgreSQL crée automatiquement un index pour chaque contrainte unique et pour chaque contrainte de clé étrangère pour renforcer l'unicité. Du coup, il n'est pas nécessaire de créer un index spécifiquement pour les colonnes de clés primaires. (Voir CREATE INDEX pour plus d'informations.)
Les contraintes uniques et les clés primaires ne sont pas héritées dans l'implémentation actuelle. Ceci rend la combinaison de l'héritage et des contraintes uniques assez disfonctionnelle.
Une table ne peut pas avoir plus de 1600 colonnes (en pratique, la limite réelle est plus basse à cause de contraintes sur la longueur des lignes).
Créez une table films et une table distributeurs :
CREATE TABLE films (
code char(5) CONSTRAINT premierecle PRIMARY KEY,
titre varchar(40) NOT NULL,
did integer NOT NULL,
date_prod date,
genre varchar(10),
duree interval hour to minute
);
CREATE TABLE distributeurs (
did integer PRIMARY KEY DEFAULT nextval('serial'),
nom varchar(40) NOT NULL CHECK (nom <> '')
);
Crée une table avec un tableau à deux dimensions :
CREATE TABLE array_int (
vecteur int[][]
);
Définir une contrainte unique de table pour la table films. Les contraintes uniques de table peuvent être définies sur une ou plusieurs colonnes de la table.
CREATE TABLE films (
code char(5),
titre varchar(40),
did integer,
date_prod date,
genre varchar(10),
duree interval hour to minute,
CONSTRAINT production UNIQUE(date_prod)
);
Définir une contrainte de colonne de vérification :
CREATE TABLE distributeurs (
did integer CHECK (did > 100),
nom varchar(40)
);
Définir une contrainte de table de vérification :
CREATE TABLE distributeurs (
did integer,
nom varchar(40)
CONSTRAINT con1 CHECK (did > 100 AND nom <> '')
);
Définir une contrainte de clé primaire sur la table films. Les contraintes de clé primaire peuvent être définies sur une ou plusieurs colonnes de la table.
CREATE TABLE films (
code char(5),
titre varchar(40),
did integer,
date_prod date,
genre varchar(10),
duree interval hour to minute,
CONSTRAINT code_titre PRIMARY KEY(code,titre)
);
Définir une contrainte de clé primaire pour la table distributeurs. Les deux exemples suivants sont équivalents, le premier utilisant la syntaxe de contrainte de la table, le second la syntaxe de contrainte de la colonne.
CREATE TABLE distributeurs (
did integer,
nom varchar(40),
PRIMARY KEY(did)
);
CREATE TABLE distributeurs (
did integer PRIMARY KEY,
nom varchar(40)
);
Ceci affecte une valeur par défaut pour la colonne nom, arrange la valeur par défaut de la colonne did pour être générée en sélectionnant la prochaine valeur d'un objet séquence et fait que la valeur par défaut de modtime soit le moment où la ligne est insérée.
CREATE TABLE distributeurs (
name varchar(40) DEFAULT 'Luso Films',
did integer DEFAULT nextval('distributeurs_serial'),
modtime timestamp DEFAULT current_timestamp
);
Définir deux contraintes de colonnes NOT NULL sur la table
distributeurs, dont une se voit donner explicitement
un nom :
CREATE TABLE distributeurs (
did integer CONSTRAINT no_null NOT NULL,
nom varchar(40) NOT NULL
);
Définit une contrainte unique pour la colonne nom :
CREATE TABLE distributeurs (
did integer,
nom varchar(40) UNIQUE
);Ce qui se trouve ci-dessus est équivalent à ce qui suit, spécifié comme une contrainte de table :
CREATE TABLE distributeurs (
did integer,
nom varchar(40),
UNIQUE(nom)
);
Créer une table cinemas dans le tablespace diskvol1 :
CREATE TABLE cinemas (
id serial,
nom text,
emplacement text
) TABLESPACE diskvol1;
La commande CREATE TABLE se conforme à SQL-92 et à un sous-ensemble de SQL:1999, avec les exceptions indiquées ci-dessous.
Bien que la syntaxe de CREATE TEMPORARY TABLE ressemble à celle du SQL standard, l'effet n'est pas le même. Dans le standard, les tables temporaires sont définies seulement une fois et existent automatiquement (en commençant avec un contenu vide) dans chaque session qui en a besoin. À la place, PostgreSQL requiert que chaque session lance sa propre commande CREATE TEMPORARY TABLE pour chaque table temporaire à utiliser. Ceci permet à différentes sessions d'utiliser le même nom de table temporaire dans des buts différents alors que l'approche du standard contraint toutes les instances d'un nom de table temporaire donné pour avoir la même structure de table.
La définition du standard pour le comportement des tables temporaires est largement ignorée. Le comportement de PostgreSQL sur ce point est similaire à celui de nombreuses autres bases de données SQL.
La distinction du standard entre tables temporaires globales et locales n'est pas dans PostgreSQL car cette distinction dépend du concept de modules, que PostgreSQL ne possède pas. Pour le bien de la compatibilité, PostgreSQL acceptera les mots clés GLOBAL et LOCAL dans la déclaration d'une table temporaire mais cela n'aura aucun effet.
La clause ON COMMIT pour les tables temporaires ressemble aussi au standard SQL mais a quelques différences. Si la clause ON COMMIT est omise, SQL spécifie que le comportement par défaut est ON COMMIT DELETE ROWS. Néanmoins, le comportement par défaut dans PostgreSQL est ON COMMIT PRESERVE ROWS. L'option ON COMMIT DROP n'existe pas en SQL.
Le standard SQL dit que les contraintes de vérification CHECK de colonne pourraient seulement référencer la colonne à laquelle elles s'appliquent ; seulement les contraintes de tables CHECK pourraient se référencer à de nombreuses colonnes. PostgreSQL ne force pas cette restriction ; il traite de la même façon les contraintes de vérifications des colonnes et des tables.
La << contrainte >> NULL (réellement une non-contrainte) est une extension PostgreSQL au standard SQL qui est inclus pour des raisons de compatibilité avec quelques autres systèmes de bases de données (et pour la symétrie avec la contrainte NOT NULL). Comme ceci est la valeur par défaut de cette colonnes, sa présence est un simple bruit.
Plusieurs héritages via la clause INHERITS est une extension du langage PostgreSQL. SQL:1999 (et non pas SQL-92) définit un héritage simple en utilisant une syntaxe et des sémantiques différentes. L'héritage style SQL:1999 n'est pas encore supporté par PostgreSQL.
PostgreSQL autorise la création de tables sans colonnes (par exemple, CREATE TABLE foo();). Ceci est une extension du standard SQL, qui ne le permet pas. Les tables sans colonnes ne sont pas très utiles mais les désactiver pourrait apporter quelques cas bizarres spéciaux pour ALTER TABLE DROP COLUMN, donc il semble plus propre d'ignorer la restriction de cette spécification.
| Précédent | Sommaire | Suivant |
| CREATE SEQUENCE | Niveau supérieur | CREATE TABLE AS |