Documentation PostgreSQL 7.2
<<< PreviousNext >>>

CREATE TABLE

Name

CREATE TABLE -- définit une nouvelle table

Synopsis

CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
    { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
    | table_constraint }  [, ... ]
)
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]

where column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | UNIQUE | PRIMARY KEY |
  CHECK (expression) |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
    [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and table_constraint is:

[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) |
  PRIMARY KEY ( column_name [, ... ] ) |
  CHECK ( expression ) |
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

Description

CREATE TABLE insérera une nouvelle table dans la base courante. La table sera propriété de l'utilisateur ayant généré la commande.

CREATE TABLE crée aussi automatiquement un type qui représente le type tuple (type structure) correspondant à une ligne de la table. Donc, les tables ne peuvent pas voir le même nom qu'un type déja existant.

Une table ne peut pas avoir plus de 1600 colonnes. (En pratique, la limite effective est plus basse à cause des contraintes de longueur de tuples). Une table ne peut pas avoir le même nom qu'une table du catalogue système.

Les clauses de contrainte optionnelles spécifient les contraintes (ou les tests) que les lignes nouvelles ou mises à jour doivent satisfaire pour qu'une opération d'insertion ou de mise à jour soit réussie. Une contrainte est une règle nommée : un objet SQL qui aide à définir des ensembles valides de valeurs en mettant des limites aux résultats d'insertion, mise à jour, ou validation effectuées sur une table.

Il y a deux moyens de définir les contraintes : les contraintes de table et les contraintes de colonnes. Une contrainte de colonne est définie comme une partie d'une définition de colonne. Une définition de contrainte de table n'est pas liée à une colonne particulière, et elle peut renfermer plus d'une colonne. Chaque contrainte de colonne peut aussi être écrite comme une contrainte de table; une contrainte de colonne n'est qu'une convention de notation si la contrainte affecte seulement une colonne.

Paramètres

[LOCAL] TEMPORARY ou [LOCAL] TEMP

Si spécifié, la table est créée comme table temporaire. Les tables remporaires sont automatiquement supprimées à la fin de la session. L'existence de tables permanentes avec le même nom ne sont pas visibles par la session courante alors que les tables temporaires existent. Certains indexes créés sur une table temporaire sont automatiquement temporaires aussi.

Le mot LOCAL est optionnel. Mais voir Compatibilité.

table_name

Le nom de la table à créer.

column_name

Le nom d'une colonne à créer dans la nouvelle table.

data_type

Le type de la colonne. Ceci peut inclure des spécificateurs de tableaux. Voir le Guide de l'utilisateur pour plus d'information sur les types et les tableaux.

DEFAULT default_expr

La clause DEFAULT assigne une valeur par défaut pour une colonne dont la définition apparaît en interne. La valeur est une expression de variable libre (les sous-sélections et références croisées aux autres colonnes dans la table courante ne sont pas permises). Le type de l'expression par défaut doit s'apparier au type de la colonne.

L'expression par défaut sera utilisée dans des opérations d'insertion qui ne spécifient pas de valeur pour la colonne. S'il n'y a pas de valeur par défaut pour une colonne, cette valeur est NULL.

INHERITS ( parent_table [, ... ] )

La clause optionnelle INHERITS spécifie une liste de tables depuis lesquelles la nouvelle table hérite de toutes les colonnes. Si le même nom de colonne existe dans plus d'une table parente, une erreur est rapportée à moins que les types des colonnes s'apparient dans chacune des tables parentes. S'il n'y a pas de conflit, les colonnes dupliquées sont fusionnées pour former une colonne unique dans la nouvelle table. Si la liste des noms de colonne de la nouvelle table contient une colonne qui est aussi héritée, le type doit également apparier le(s) colonne(s) héritée(s), et les définitions de colonnes sont fusionnées en une seule. Cependant, les héritées et les nouvelles déclarations de colonnes de même nom n'ont pas besoin de spécifier des contraintes identiques : toutes les contraintes issues des diverses déclarations sont fusionnées ensembles 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 remplacera les valeurs par défaut des déclarations héritées de la colonne. Autrement, certains parents qui spécifient des valeurs par défaut pour la colonne doivent toutes spécifier la même valeur par défaut, ou une erreur sera renvoyée.

WITH OIDS ou WITHOUT OIDS

Cette clause optionnelle spécifie si les lignes de la nouvelle table doivent avoir des OID (identifiants objet) qui lui sont assignés. Par défaut c'est d'avoir des OID. (Si la nouvelle table hérite de tables ayant des OID, alors WITH OIDS est forcé même si la commande indique WITHOUT OIDS).

Spécifier WITHOUT OIDS permet à l'utilisateur de supprimer la génération d'OID pour les lignes d'une table. Ceci peut valoir le coup pour de grosses tables, car il réduira la consommation d'OID et par ce moyen ajourne le bouclage du compteur OID 32-bit. Une fois le compteur bouclé, l'unicité des OID ne peut pas être supposée, ce qui réduit considérablement leur utilité.

CONSTRAINT constraint_name

Un nom optionnel pour une contrainte de table ou de colonne. Si non spécifié, le système génère un nom.

NOT NULL

La colonne ne peut pas contenir des valeurs NULL. Ceci est équivalent à la contrainte de colonne CHECK (column NOT NULL).

NULL

La colonne peut contenir des valeurs NULL. Utilisation par défaut.

Cette clause n'est disponible que pour la compatibilité avec les bases non-standard SQL. Son usage est découragé dans les nouvelles applications.

UNIQUE (column constraint), UNIQUE ( column_name [, ... ] ) (table constraint)

La contrainte UNIQUE spécifie comme règle qu'un groupe d'une ou plusieurs colonnes distinctes d'une table ne peut contenir que des valeurs uniques. Le comportement de la contrainte de table unique est le même que celui des contraintes de colonnes, avec la possibilité supplémentaire de s'étendre sur des colonnes multiples.

En parlant de contrainte unique, les valeurs NULL ne sont pas considérées comme égales.

Chaque contrainte de table unique doit nommer un ensemble de colonnes différent de l'ensemble de colonnes nommé par une autre contrainte unique ou clé primaire définie pour la table. (Autrement ce sera la même contrainte listée en double).

PRIMARY KEY (column constraint), PRIMARY KEY ( column_name [, ... ] ) (table constraint)

La contrainte clé primaire spécifie qu'une colonne ou des colonnes d'une table ne peuvent contenir que des uniques (non-dupliquées), valeurs non-NULL. Techniquement, PRIMARY KEY est simplement une combinaison de UNIQUE et NOT NULL, mais l'identification d'un ensemble de colonnes comme clé primaire génère des meta-données au niveau de la conception du schéma, une clé primaire implique que les autres tables doivent se relier à cet ensemble de colonnes comme identifiant unique pour les lignes.

Une seule clé primaire peut être spécifiée pour une table, soit comme contrainte de colonne soit comme contrainte de table.

La contrainte clé primaire doit nommer un ensemble de colonnes différent des autres ensembles de colonnes nommés par une contrainte unique définie pour la même table.

CHECK (expression)

Les clauses CHECK spécifient des contraintes d'intégrité ou de tests dont les lignes nouvelles ou mises à jour doivent satisfaire une opération d'insertion ou de mise à jour. Chaque contrainte doit être une expression produisant un résultat booléen. Une condition apparaissant dans une définition de colonne doit référencer la valeur de cette colonne uniquement, tandis qu'une condition apparaissant comme contrainte de table peut référencer des colonnes multiples.

Habituellement, les expressions CHECK ne peuvent pas contenir de sous-sélections ni se référer aux variables autres que les colonnes de la ligne courante.

REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (column constraint), FOREIGN KEY ( column [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (table constraint)

La contrainte de colonne REFERENCES spécifie qu'un groupe d'une ou plusieurs colonnes de la nouvelle table doit seulement contenir des valeurs qui s'apparient aux valeurs des colonnes référencées refcolumn de la table référencée reftable. Si refcolumn est omis, la clé primaire de la reftable est utilisée. Les colonnes référencées doivent être les colonnes d'une contrainte unique ou clé primaire dans la table référencée.

Une valeur ajoutée à ces colonnes est appariée aux valeurs de la table et des colonnes référencées en utilisant le type d'appariement donné. Il y a trois types d'appariement : MATCH FULL, MATCH PARTIAL, et un type par défaut non spécifié. MATCH FULL ne permet pas qu'une colonne d'une clé étrangère multicolonne soit NULL à moins que les colonnes de la clé étrangère soient NULL. Le type appariement par défaut admet que certaines colonnes de clé étrangère soient NULL tandis que les autres parties de la clé étrangère ne sont pas NULL. MATCH PARTIAL n'est pas encore implémenté.

De plus, quand les données de la colonne référencée sont modifiées, certaines actions sont exécutées sur les données dans les colonnes de la table. La clause ON DELETE spécifie l'action à mener quand une ligne référencée dans la table référencée va être supprimée. De même, la clause ON UPDATE spécifie l'action à exécuter quand une colonne référencée dans une table référencée va être mise à jour vers une nouvelle valeur. Si la ligne est mise à jour, mais que la colonne référencée n'est pas modifiée, aucune action n'est faite. Actions possibles pour chaque clause :

NO ACTION

Produit une erreur indiquant que la suppression ou la mise à jour créera une violation de contrainte de clé étrangère. C'est l'action par défaut.

RESTRICT

Identique à NO ACTION.

CASCADE

Supprime certaines lignes référençant les lignes supprimées, ou met à jour la valeur de la colonne référencée en nouvelle valeur de la colonne référencée, respectivement.

SET NULL

Place les valeurs des colonnes de référencement à NULL.

SET DEFAULT

Place les valeurs des colonnes de référencement à leur valeur par défaut.

Si la colonne de clé primaire est mise à jour fréquemment, il peut être prudent d'ajouter un index à la colonne REFERENCES ainsi les actions NO ACTION et CASCADE associées à la colonne REFERENCES seront exécutées de façon plus efficace.

DEFERRABLE ou NOT DEFERRABLE

Ceci contrôle comment la contrainte peut différée. Une contrainte qui n'est pas différable sera vérifiée immédiatement après chaque commande. La vérification de contrainte différables peut être ajournée jusqu'à la fin de la transaction (en utilisant la commande SET CONSTRAINTS). NOT DEFERRABLE est par défaut. Seules les contraintes de clé étrangère acceptent actuellement cette clause. Tous les autres types de contraintes ne sont pas différables.

INITIALLY IMMEDIATE ou INITIALLY DEFERRED

Si une contrainte est différable, cette clause spécifie le moment par défaut pour vérifier la contrainte. Si la contrainte est INITIALLY IMMEDIATE, elle est vérifiée après chaque instruction. Ce qui est le comportement par défaut. Si la contrainte est INITIALLY DEFERRED, elle est vérifiée seulement à la fin de la transaction. La contrainte de vérification du moment peut être modifiée par la commande SET CONSTRAINTS.

Diagnostics

CREATE

Message retourné si la table a été créée avec succès.

ERROR

Message retourné si la création a échoué. Accompagné habituellement par un texte descriptif, comme : ERROR: Relation 'table' already exists, qui apparaît si la table spécifiée existe déja dans la base.

Notes

Exemples

Créons une table films et une table distributors: 

CREATE TABLE films (
    code        CHARACTER(5) CONSTRAINT firstkey PRIMARY KEY,
    title       CHARACTER VARYING(40) NOT NULL,
    did         DECIMAL(3) NOT NULL,
    date_prod   DATE,
    kind        CHAR(10),
    len         INTERVAL HOUR TO MINUTE
);
CREATE TABLE distributors (
     did    DECIMAL(3) PRIMARY KEY DEFAULT NEXTVAL('serial'),
     name   VARCHAR(40) NOT NULL CHECK (name <> '')
);

Créons une table avec un tableau à deux dimensions : 

CREATE TABLE array (
    vector  INT[][]
);

Définissons une contrainte de table unique pour la table films. Des contraintes unique de table peuvent être définies sur une ou plusieurs colonnes de la table : 

CREATE TABLE films (
    code        CHAR(5),
    title       VARCHAR(40),
    did         DECIMAL(3),
    date_prod   DATE,
    kind        VARCHAR(10),
    len         INTERVAL HOUR TO MINUTE,
    CONSTRAINT production UNIQUE(date_prod)
);

Définissons une contrainte de vérification de colonne : 

CREATE TABLE distributors (
    did     DECIMAL(3) CHECK (did > 100),
    name    VARCHAR(40)
);

Définissons une contrainte de vérification de table : 

CREATE TABLE distributors (
    did     DECIMAL(3),
    name    VARCHAR(40)
    CONSTRAINT con1 CHECK (did > 100 AND name <> '')
);

Définissons une contrainte de table clé primaire pour la table films. Les contraintes de table clé primaires peuvent être définies sur une ou plusieurs colonnes de la table. 

CREATE TABLE films (
    code        CHAR(5),
    title       VARCHAR(40),
    did         DECIMAL(3),
    date_prod   DATE,
    kind        VARCHAR(10),
    len         INTERVAL HOUR TO MINUTE,
    CONSTRAINT code_title PRIMARY KEY(code,title)
);

Définissons une contrainte clé primaire pour la table distributors. Les deux exemples suivants sont équivalents, le premier utilisant la syntaxe de contrainte de table, le second la notation de contrainte de colonne. 

CREATE TABLE distributors (
    did     DECIMAL(3),
    name    CHAR VARYING(40),
    PRIMARY KEY(did)
); 
CREATE TABLE distributors (
    did     DECIMAL(3) PRIMARY KEY,
    name    VARCHAR(40)
);

Ceci assigne une valeur constante littérale par défaut pour la colonne name, et s'arrange pour que la valeur par défaut de la colonne soit soit générée en sélectionnant la prochaine valeur d'une séquence d'objet. La valeur par défaut de modtime sera le moment pendant lequel la ligne est insérée. 

CREATE TABLE distributors (
    name      VARCHAR(40) DEFAULT 'luso films',
    did       INTEGER DEFAULT NEXTVAL('distributors_serial'),
    modtime   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Définissons deux contraintes de colonnes NOT NULL sur la table distributors, une de celles-ci est explicitement nommée : 

CREATE TABLE distributors (
    did     DECIMAL(3) CONSTRAINT no_null NOT NULL,
    name    VARCHAR(40) NOT NULL
);

Définissons une contrainte unique pour la colonne name: 

CREATE TABLE distributors (
    did     DECIMAL(3),
    name    VARCHAR(40) UNIQUE
);
La suivante est équivalente à celles spécifiées comme contrainte de table : 
CREATE TABLE distributors (
    did     DECIMAL(3),
    name    VARCHAR(40),
    UNIQUE(name)
);

Compatibilité

Le CREATE TABLE est conforme au SQL92 et au sous-ensemble du SQL99, avec les exceptions listées ci-dessous et précédemment.

Tables temporaires

En plus de la table locale temporaire, SQL92 définit aussi l'instruction CREATE GLOBAL TEMPORARY TABLE. Les tables temporaires globales sont aussi visibles par les autres sessions.

Pour les tables temporaires, il existe une clause optionnelle ON COMMIT : 

CREATE { GLOBAL | LOCAL } TEMPORARY TABLE table ( ... ) [ ON COMMIT { DELETE | PRESERVE } ROWS ]
La clause ON COMMIT spécifie si oui ou non la table temporaire sera vidée de ses lignes chaque fois que COMMIT est exécuté. Si le clause ON COMMIT est omise, SQL92 spécifie que par défaut on applique ON COMMIT DELETE ROWS. Cependant, le comportement de PostgreSQL est toujours identique à ON COMMIT PRESERVE ROWS.

"Contrainte"NULL

La "contrainte"NULL (actuellement une non-contrainte) est une extension PostgreSQL au SQL92 qui est inclu pour la compatibilité avec certains autres SGBDR (et pour la symétrie avec la contrainte NOT NULL).

Assertions

Une assertion est un type de contrainte d'intégrité spéciale qui partage le même espace de nommage que les autres contraintes. Cependant, une assertion n'est pas nécessairement dépendante d'une table comme le sont les contraintes, aussi SQL92 fournit l'instruction CREATE ASSERTION comme une méthode alternative de définition d'une contrainte. 

CREATE ASSERTION name CHECK ( condition )

PostgreSQL n'implémente pas les assertions actuellement.

Héritage

L'héritage multiple par la clause INHERITS est une extension du langage PostgreSQL. SQL99 (mais pas SQL92) définit l'héritage unique en utilisant une syntaxe et une sémantique différente. L'héritage style SQL99 n'est pas encore supporté par PostgreSQL.

Object ID

Le concept des OID de PostgreSQL n'est pas standard.

Voir aussi

ALTER TABLE , DROP TABLE
<<< PreviousHomeNext >>>
CREATE SEQUENCEUpCREATE TABLE AS