| Documentation PostgreSQL 7.4.29 | ||||
|---|---|---|---|---|
| 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 } ]
où contrainte_colonne
est :
[ CONSTRAINT nom_contrainte ]
{ NOT NULL | NULL | UNIQUE | PRIMARY KEY |
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 [, ... ] ) |
PRIMARY KEY ( nom_colonne [, ...
] ) |
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.
Une table ne peut pas avoir plus de 1600 colonnes. (En pratique, la limite effective est plus basse à cause des contraintes de longueur de la ligne).
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 si 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.
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 LIKE spécifie une table à partir de laquelle la nouvelle table hérite 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 données insérées dans la nouvelle table ne sont pas reflétées dans la table parent.
Les expressions par défaut pour les définitions des colonnes héritées seront seulement incluses si INCLUDING DEFAULTS est spécifié. Par défaut, il faut exclure les expressions par défaut.
La clause optionnelle INHERITS spécifie une liste des tables à partir desquelles la nouvelle table hérite automatiquement de toutes les colonnes. 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 une 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.
Cette clause optionnelle spécifie si les lignes de la nouvelle table devraient avoir des OID (identifiants d'objets) qui leur sont affectés. Par défaut, il y a des OID. (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.)
Spécifier WITHOUT OIDS autorise l'utilisateur à supprimer la génération des OIDs pour les lignes d'une table. Ceci pourrait être 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é. Spécifier WITHOUT OIDS réduit aussi l'espace requis pour stocker la table sur disque de quatre octets par ligne de la table, améliorant ainsi leur performance.
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 disponible 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. 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 spécifie 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) colonne_reference de la table référencée table_reference. 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 ces colonnes 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. 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. Ceci est l'action par défaut.
De même que NO ACTION sauf que cette action ne sera pas déferrée même si le reste de la contrainte est déferrable et déferrée.
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 les valeurs de la colonne de référence à NULL.
Initialise les valeurs de la colonne de référence à leur valeur par défaut.
Si la clé primaire est mise à jour fréquemment, il pourrait être conseillé d'ajouter un index vers la colonne de clé étrangère de façon à ce que les actions NO ACTION et CASCADE 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.
À chaque fois qu'une 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. (Il est probable que les versions futures de PostgreSQL utiliseront un compteur OID séparé pour chaque table, de façon à ce qu'il soit nécessaire, et non pas optionnel, d'inclure tableoid pour avoir un identifiant unique pour la base de données.)
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écifique 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.
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 (
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 notation 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)
);
La commande CREATE TABLE se conforme à SQL92 et à un sous-ensemble de SQL99, 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. SQL99 (et non pas SQL92) définit un héritage simple en utilisant une syntaxe et des sémantiques différentes. L'héritage style SQL99 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 |