root/traduc/branches/bv747/manuel/ddl.sgml

<!-- $Header: /var/lib/cvs/pgsql-fr/sgml/ddl.sgml,v 1.6.2.3 2005/07/15 06:33:36 guillaume Exp $ -->

<chapter id="ddl">
 <title>Définition des données</title>

 <para>
  Ce chapitre couvre la création de structures de données qui contiendront
  les données de quelqu'un. Dans une base relationnelle, les données brutes
  sont stockées dans des tables. Du coup, la plus grande partie de ce chapitre
  sera consacrée à l'explication de la création et de la modification des tables 
  et des fonctions disponibles pour contrôler les données stockées dans les tables.
  Ensuite, nous discuterons de l'organisation de tables en schémas, et comment
  des droits peuvent être attribués aux tables. Enfin,
  nous verrons brièvement d'autres fonctionnalités, tel que les vues, les fonctions
  et les déclencheurs.
 </para>

 <sect1 id="ddl-basics">
  <title>Bases sur les tables</title>

  <indexterm zone="ddl-basics">
   <primary>table</primary>
  </indexterm>

  <indexterm>
   <primary>rangée</primary>
  </indexterm>

  <indexterm>
   <primary>colonne</primary>
  </indexterm>

  <para>
   Une table dans une base relationnelle ressemble beaucoup à un tableau
   sur papier&nbsp;: elle est constituée de rangées et de colonnes. Le nombre
   et l'ordre des colonnes sont fixés et chaque colonne a un nom. Le
   nombre de rangées est variable -- il représente la quantité de données
   stockées à un moment donné. SQL n'apporte aucune garantie sur l'ordre des
   rangées dans une table. Quand une table est lue, les rangées
   apparaîtront dans un ordre aléatoire sauf si un tri est demandé
   explicitement. Ceci est couvert dans <xref linkend="queries">. De plus,
   SQL n'attribue pas d'identifiant unique aux rangées. Du coup, il est possible
   d'avoir plusieurs rangées complètement identiques dans une table. Ceci
   est une conséquence du modèle mathématique sur lequel repose SQL mais
   n'est habituellement pas désiré.
   Plus tard dans ce chapitre, nous verrons comment traiter ce problème.
  </para>

  <para>
   Chaque colonne a un type de donnée. Ce type de donnée restreint la série
   de valeurs possibles qui peuvent être attribuées à une colonne et attribue
   des sémantiques à la donnée stockée dans la colonne pour qu'elles puissent
   être utilisées pour des calculs. Par exemple, une colonne déclarée comme
   étant d'un type numérique n'acceptera pas une chaîne arbitraire de texte,
   et les données stockées dans une telle table peuvent être utilisées dans
   des calculs mathématiques.
   Par opposition, une colonne déclarée comme étant de type chaîne de
   caractères acceptera pratiquement n'importe quel type de donnée mais ne
   se prêtera pas à des calculs mathématiques bien que d'autres opérations
   tel que la concaténation des chaînes sont disponibles.
  </para>

  <para>
   <productname>PostgreSQL</productname> inclut une série conséquente de types
   de données intégrés qui correspondent à plusieurs applications. Les
   utilisateurs peuvent aussi définir leurs propres types de données. La
   plupart des types de données intégrés ont des noms et des sémantiques
   évidents alors nous reportons une explication détaillée à <xref
   linkend="datatype">. Quelques-uns des types les plus utilisés sont
   <type>integer</type> pour les entiers, <type>numeric</type> pour
   les nombres pouvant être fractionnels, <type>text</type> pour les chaînes de
   caractères, <type>date</type> pour les dates, <type>time</type> pour
   les valeurs de type heure et <type>timestamp</type> pour les valeurs
   contenant et une date et une heure.
  </para>

  <indexterm>
   <primary>table</primary>
   <secondary>création</secondary>
  </indexterm>

  <para>
   Pour créer une table, il faut utiliser la commande bien nommée <literal>CREATE
   TABLE</literal>. Dans cette commande, vous devez spécifier au moins le nom de la
   nouvelle table, les noms des colonnes et le type de données pour chacune des
   colonnes. Par exemple&nbsp;:
<programlisting>
CREATE TABLE ma_premiere_table (
    premiere_colonne text,
    deuxieme_colonne integer
);
</programlisting>
   Ceci crée une table nommée <literal>ma_premiere_table</literal> avec
   deux colonnes. La première colonne est nommée
   <literal>premiere_colonne</literal> et a un type de données
   <type>text</type>&nbsp;; la seconde colonne porte le nom
   <literal>deuxieme_colonne</literal> et le type <type>integer</type>.
   Les noms de table et colonnes suivent la syntaxe d'identification expliquée
   dans <xref linkend="sql-syntax-identifiers">.  Les noms des types sont souvent
   aussi des identifiants mais il y a des exceptions. Notez que la liste des
   colonnes est séparée par des virgules et entourée par des parenthèses.
  </para>

  <para>
   Bien sur, l'exemple précédant est un peu tiré par les cheveux. Normalement,
   on donne aux tables et aux colonnes des noms indiquant quels types de données
   ils stockent. Alors voyons un exemple plus réaliste&nbsp;:
<programlisting>
CREATE TABLE produits (
    no_produit integer,
    nom text,
    prix numeric
);
</programlisting>
   (Le type <type>numeric</type> peut stocker des composants fractionnels
   comme on pourrait s'y attendre de montants monétaires.)
  </para>

  <tip>
   <para>
    Quand vous créez des tables liées entre elles, il est prudent de choisir
    des règles de nommage pour les tables et les colonnes. Par exemple, il
    peut y avoir le choix d'utiliser des noms au pluriel ou au singulier
    pour les noms de table, chaque choix ayant les faveurs d'un théoricien
    ou d'un autre.
   </para>
  </tip>

  <para>
   Il y a une limite sur le nombre de colonnes qu'une table peut contenir.
   Suivant le type de colonne, elle peut être entre 250 et 1600.
   Par contre, définir une table avec un nombre de colonnes proche de ceux-ci
   est très inhabituel et est souvent la preuve d'une conception douteuse.
  </para>

  <indexterm>
   <primary>table</primary>
   <secondary>suppression</secondary>
  </indexterm>

  <para>
   Si vous n'avez plus besoin d'une table, vous pouvez la retirer en utilisant
   la commande <command>DROP TABLE</command>. Par exemple&nbsp;:
<programlisting>
DROP TABLE ma_premiere_table;
DROP TABLE produits;
</programlisting>
   Tenter de supprimer une table qui n'existe pas est une erreur.
   Malgré cela, il est habituel dans des fichiers de scripts SQL d'essayer
   de supprimer chaque table avant de la créer, tout en ignorant les messages
   d'erreur.
  </para>

  <para>
   Si vous avez besoin de modifier une table qui existe déjà, regardez <xref
   linkend="ddl-alter"> plus loin dans ce chapitre.
  </para>

  <para>
   Avec les outils dont nous avons déjà discuté, vous pouvez créer des tables
   fonctionnelles. Le reste de ce chapitre est consacré à l'ajout de fonctionnalités,
   à la définition de tables pour garantir l'intégrité des données, la sécurité
   ou la facilité.  Si vous êtes impatients de remplir vos tables avec des données
   tout de suite, vous pouvez sauter au <xref linkend="dml"> et lire le reste de
   ce chapitre plus tard.
  </para>
 </sect1>

 <sect1 id="ddl-system-columns">
  <title>Colonnes Systèmes</title>

  <para>
   Chaque table a plusieurs <firstterm>colonnes systèmes</firstterm> qui sont
   implicitement définis par le système.  De ce fait, ces noms ne peuvent être
   utilisés comme noms de colonnes définis par l'utilisateur. (Notez que ces
   restrictions sont différentes, si le nom est un mot-clé ou pas; citez un
   nom ne vous permettra pas d'échapper à ces restrictions.) Vous n'avez pas
   vraiment besoin de vous préoccuper de ces colonnes, simplement savoir qu'elles
   existent.
  </para>

  <indexterm>
   <primary>colonne</primary>
   <secondary>colonne système</secondary>
  </indexterm>

  <variablelist>
   <varlistentry>
    <term><structfield>oid</structfield></term>
    <listitem>
     <para>
      <indexterm>
       <primary>OID</primary>
       <secondary>colonne</secondary>
      </indexterm>
      L'identifiant objet (object ID) d'une rangée. Ceci est un numéro de série
      qui est automatiquement rajouté par
      <productname>PostgreSQL</productname> à toutes les rangées de tables (sauf
      si la table a été créée avec <literal>WITHOUT OIDS</literal>, auquel cas
      cette colonne n'est pas présente). Cette colonne est de type
      <type>oid</type> (même nom que la colonne); voir <xref
      linkend="datatype-oid"> pour plus d'informations sur ce type.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><structfield>tableoid</structfield></term>
    <listitem>
     <indexterm>
      <primary>tableoid</primary>
     </indexterm>

     <para>
      L' OID de la table contenant cette rangée. Cette colonne est
      particulièrement utile pour les requêtes qui sélectionnent de
      hiérarchies héritées, puisque sans elle, il est difficile de
      dire de quelle table vient une rangée. Le
      <structfield>tableoid</structfield> peut être joint à la
      colonne <structfield>oid</structfield> de
      <structname>pg_class</structname> pour obtenir le nom de la table.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><structfield>xmin</structfield></term>
    <listitem>
     <indexterm>
      <primary>xmin</primary>
     </indexterm>

     <para>
      L'identité (transaction ID) de la transaction d'insertion de cette
      version de la rangée. (Une version de rangée est un état individuel
      d'une rangée; chaque mise à jour d'une rangée crée une nouvelle version
      de rangée pour la même rangée logique.)
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><structfield>cmin</structfield></term>
    <listitem>
     <indexterm>
      <primary>cmin</primary>
     </indexterm>

     <para>
      L'identifiant de commande (à partir de zéro) au sein de la transaction
      d'insertion.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><structfield>xmax</structfield></term>
    <listitem>
     <indexterm>
      <primary>xmax</primary>
     </indexterm>

     <para>
      L'identité (transaction ID) de la transaction de suppression, ou
      zéro pour une version de rangée non effacée. Il est possible pour
      cette colonne d'être non nulle dans une version de rangée visible: Ceci
      indique normalement que la transaction de suppression n'a pas été
      effectuée, ou qu'une tentative de suppression a été annulée.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><structfield>cmax</structfield></term>
    <listitem>
     <indexterm>
      <primary>cmax</primary>
     </indexterm>

     <para>
      L'identifiant de commande au sein d'une transaction de suppression, ou zéro.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><structfield>ctid</structfield></term>
    <listitem>
     <indexterm>
      <primary>ctid</primary>
     </indexterm>

     <para>
      La localisation physique de la version de rangée au sein de sa table. Notez que
      bien que le <structfield>ctid</structfield> peut être utilisé pour
      trouver la version de rangée très rapidement, le <structfield>ctid</structfield>
      d'une rangée changera chaque fois qu'il est mis à jour ou déplacé par
      <structfield>ctid</structfield> changera à chaque lancement de la commande
      <command>VACUUM FULL</command>. Donc
      <structfield>ctid</structfield> est inutile en tant qu'identifiant de rangée
      à long terme. L'OID, ou encore mieux un numéro de série définie par
      l'utilisateur, devrait être utilisé pour identifier des rangées logiques.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>

   <para>
    Les OIDs sont des nombres de 32 bits et sont attribués d'un seul compteur sur tout
    le cluster.  Dans une base de données grande ou vieille, il est possible que le
    compteur boucle sur lui-même. Donc il est peu pertinent de partir du principe
    que les OIDs sont uniques, sauf si vous prenez les précautions nécessaires.
    La marche a suivre recommandée lorsqu'on utilise les OIDs pour l'identification
    de rangée est de créer une contrainte unique sur la colonne OID de chaque table
    pour laquelle l'OID sera utilisé. Ne jamais supposer que les OIDs sont uniques
    sur toutes les tables; utilisez la combinaison de <structfield>tableoid</structfield>
    et OID de rangée si vous avez besoin d'un identifiant unique sur toute la base.
    (Les versions futures de <productname>PostgreSQL</productname> auront
    probablement un compteur OID pour chaque table, pour que le <structfield>tableoid</structfield>
    <emphasis>puisse</emphasis> être inclus pour arriver à un identifiant unique globale.)
   </para>

   <para>
    Les identifiants de transaction sont aussi des nombres de 32 bits. Dans une
    base de données de longue vie, il est possible pour les ID de transaction de
    boucler sur eux-mêmes. Ceci n'est pas un problème fatal avec des procédures
    de maintenance appropriées; voir <xref linkend="maintenance"> pour les détails.
    Il est, par contre, imprudent de dépendre de l'aspect unique des ID de
    transaction à long terme (plus d'un milliard de transactions).
   </para>

   <para>
    Les identifiants de commande sont aussi des nombres de 32 bits. Ceci
    crée une limite dure de 2<superscript>32</superscript> (4 milliards) commandes
    <acronym>SQL</acronym> au sein d'une seule transaction. En pratique,
    cette limite n'est pas un problème --- notez que la limite est sur le nombre
    de commandes <acronym>SQL</acronym>, pas le nombre de rangées traitées.
   </para>
 </sect1>

 <sect1 id="ddl-default">
  <title>Valeurs par défaut</title>

  <indexterm zone="ddl-default">
   <primary>valeur par défaut</primary>
  </indexterm>

  <para>
   On peut attribuer une valeur par défaut à une colonne. Quand une nouvelle
   rangée est créée et qu'aucune valeur n'est spécifiée pour certaines de ses
   colonnes, celles-ci sont remplies avec leur valeur par défaut respective.
   Une commande de manipulation de données peut aussi demander explicitement
   qu'une colonne soit mise à sa valeur par défaut sans connaître la valeur
   en question. (Les détails sur les commandes de manipulation de données
   sont dans <xref linkend="dml">.)
  </para>

  <para>
   <indexterm><primary>valeur nulle</primary><secondary>valeur par défaut</secondary></indexterm>
   Si aucune valeur par défaut n'est déclarée explicitement, la valeur
   par défaut est la valeur nulle. Ceci est d'habitude cohérent car on
   peut imaginer que la valeur nulle représente des données inconnues.
  </para>

  <para>
   Dans une définition de table, les valeurs par défaut sont listées après
   le type de donnée de la colonne. Par exemple:
<programlisting>
CREATE TABLE produits (
    no_produit integer,
    nom text,
    prix numeric <emphasis>DEFAULT 9.99</emphasis>
);
</programlisting>
  </para>

  <para>
   La valeur par défaut peut être une expression scalaire, qui
   sera évaluée à l'insertion de la valeur par défaut
   (<emphasis>pas</emphasis> à la création de la table.)
  </para>
 </sect1>

 <sect1 id="ddl-constraints">
  <title>Contraintes</title>

  <indexterm zone="ddl-constraints">
   <primary>contrainte</primary>
  </indexterm>

  <para>
   Les types de données sont un moyen de limiter ce qui peut être stocké
   dans une table. Pour beaucoup d'applications, par contre, la contrainte
   qu'elles appliquent sont trop fortes. Par exemple, une colonne qui
   contient le prix d'un produit ne devrait accepter que des valeurs
   positives. Mais il n'y a pas de type de données qui n'acceptent que
   des valeurs positives. Un autre problème est le fait de vouloir limiter
   les données d'une colonne par rapport à d'autres colonnes ou rangées.
   Par exemple, dans une table contenant des informations de produit, il
   ne devrait y avoir qu'une rangée pour chaque numéro de produit.
  </para>

  <para>
   Dans ce but, SQL vous permet de définir les contraintes sur les colonnes
   et les tables. Les contraintes vous donnent autant de contrôle sur les
   données de vos tables que vous désirez. Si un utilisateur tente de stocker
   des données dans une colonne qui violerait un contrainte, une erreur est
   soulevée. Ceci s'applique même si la valeur vient de la définition de la
   valeur par défaut.
  </para>

  <sect2>
   <title>Contraintes de Vérification</title>

   <indexterm>
    <primary>contrainte de vérification</primary>
   </indexterm>

   <indexterm>
    <primary>contrainte</primary>
    <secondary>vérification</secondary>
   </indexterm>

   <para>
    Une contrainte de vérification est le type de contrainte le plus
    générique qui soit. Elle vous permet de spécifier l'expression
    que doit satisfaire la valeur d'une certaine colonne. Par exemple,
    pour obliger des prix de produits positifs, on pourrait utiliser:
<programlisting>
CREATE TABLE produits (
    no_produit integer,
    nom text,
    prix numeric <emphasis>CHECK (prix > 0)</emphasis>
);
</programlisting>
   </para>

   <para>
    Comme vous pouvez le voir, la définition de contrainte vient après
    le type de données comme les définitions de valeur par défaut. Les
    valeurs par défaut et les contraintes peuvent être données dans
    n'importe quel ordre. Une contrainte de vérification s'utilise avec
    le mot clé <literal>CHECK</literal> suivi d'une expression entre
    parenthèses. L'expression de contrainte de vérification peut
    impliquer la colonne ainsi contrainte, sinon la contrainte n'aurait
    pas beaucoup de sens.
   </para>

   <indexterm>
    <primary>contrainte</primary>
    <secondary>nom</secondary>
   </indexterm>

   <para>
    Vous pouvez aussi donner à la contrainte un nom diffèrent. Ceci
    clarifie les messages d'erreur et vous permet de faire référence
    à la contrainte lorsque vous avez besoin de la modifier.
    La syntaxe est:
<programlisting>
CREATE TABLE produits (
    no_produit integer,
    nom text,
    prix numeric <emphasis>CONSTRAINT positive_price</emphasis> CHECK (prix > 0)
);
</programlisting>
    Alors, pour spécifier une contrainte nommée, utilisez le mot-clé
    <literal>CONSTRAINT</literal> suivi d'un identifiant et de la
    définition de contrainte.
   </para>

   <para>
    Une contrainte de vérification peut faire référence à plusieurs
    colonnes. Admettons que vous souhaitez stocker un prix normal et un
    prix de promotion et, être sur que le prix de
    promotion soit inférieur au prix normal.
<programlisting>
CREATE TABLE produits (
    no_produit integer,
    nom text,
    prix numeric CHECK (prix > 0),
    prix_solde numeric CHECK (prix_solde > 0),
    CHECK (prix > prix_solde)
);
</programlisting>
   </para>

   <para>
    Les deux premières contraintes devrait vous être familières. La troisième
    utilise une nouvelle syntaxe. Elle n'est pas attachée à une colonne
    particulière, elle apparaît comme un élément distinct dans
    la liste de colonnes séparées par des virgules. Les définitions de
    colonnes et ces définitions de contraintes peut être définies dans
    un ordre quelconque.
   </para>

   <para>
    On dit que les deux premières contraintes sont des contraintes de
    colonnes tandis que la troisième est une contrainte de table parce
    qu'elle est écrite séparément, des définitions de colonnes tandis que
    l'inverse n'est pas forcément possible. L'exemple ci-dessus aurait
    pu s'écrire:
<programlisting>
CREATE TABLE produits (
    no_produit integer,
    nom text,
    prix numeric,
    CHECK (prix > 0),
    prix_solde numeric,
    CHECK (prix_solde > 0),
    CHECK (prix > prix_solde)
);
</programlisting>
    ou même
<programlisting>
CREATE TABLE produits (
    no_produit integer,
    nom text,
    prix numeric CHECK (prix > 0),
    prix_solde numeric,
    CHECK (prix_solde > 0 AND prix > prix_solde)
);
</programlisting>
    C'est une question de goût.
   </para>

   <indexterm>
    <primary>valeur nulle</primary>
    <secondary sortas="check constraints">avec contraintes de vérification</secondary>
   </indexterm>

   <para>
    Il faut noter qu'une contrainte de vérification est satisfaite si
    l'expression est évaluée à vrai ou la valeur nulle. Puisque la
    plupart des expressions seront évaluées à la valeur nulle si l'un
    des opérandes est NULL, elles n'empêchent pas les valeurs nulles
    dans les colonnes contraintes. Pour s'assurer qu'une colonne ne
    contient pas de valeurs nulles, la contrainte non-nulle décrite
    dans la section suivante devrait être utilisée.
   </para>
  </sect2>

  <sect2>
   <title>Contraintes Non Nulles</title>

   <indexterm>
    <primary>contrainte non nulle</primary>
   </indexterm>

   <indexterm>
    <primary>contrainte</primary>
    <secondary>NOT NULL</secondary>
   </indexterm>

   <para>
    Une contrainte non nulle dit simplement qu'une colonne ne peut
    pas prendre la valeur nulle. Un exemple de syntaxe:
<programlisting>
CREATE TABLE produits (
    no_produit integer <emphasis>NOT NULL</emphasis>,
    nom text <emphasis>NOT NULL</emphasis>,
    prix numeric
);
</programlisting>
   </para>

   <para>
    Une contrainte non nulle est toujours écrite comme une contrainte de
    colonne. Une contrainte non nulle est l'équivalente fonctionnelle de
    créer une contrainte <literal>CHECK (<replaceable>nom_colonne</replaceable>
    IS NOT NULL)</literal>, mais dans <productname>PostgreSQL</productname>,
    créer une contrainte explicitement non nulle est plus efficace.
    L'inconvénient est que vous ne pouvez pas donner de noms explicites
    à des contraintes non nulles créées de cette manière.
   </para>

   <para>
    Bien sur, une colonne peut avoir plus d'une contrainte. Écrivez juste
    les contraintes les unes après les autres:
<programlisting>
CREATE TABLE produits (
    no_produit integer NOT NULL,
    nom text NOT NULL,
    prix numeric NOT NULL CHECK (prix > 0)
);
</programlisting>
    L'ordre n'importe pas. Il ne détermine pas dans quel ordre les contraintes
    seront vérifiées.
   </para>

   <para>
    La contrainte <literal>NOT NULL</literal> a un opposé; la contrainte
    <literal>NULL</literal> . Ceci ne veut pas dire que la colonne doit
    être NULL, ce qui serait inutile. Au lieu, ceci définit le comportement
    par défaut que la colonne doit être nulle. La contrainte <literal>NULL
    </literal> n'est pas définie dans le standard SQL et ne devrait pas
    être utilisé dans des applications portables. (Elle n'a été ajoutée
    dans <productname>PostgreSQL</productname> que pour assurer la
    compatibilité avec d'autres bases de données.) Certains utilisateurs
    l'apprécient car elle facilite le fait d'activer une contrainte
    dans un fichier de script. Par exemple, vous pourriez commencer avec:
<programlisting>
CREATE TABLE produits (
    no_produit integer NULL,
    nom text NULL,
    prix numeric NULL
);
</programlisting>
    et puis insérer le mot-clé <literal>NOT</literal> suivant vos besoins.
   </para>

   <tip>
    <para>
     Dans beaucoup de conceptions de bases de données, la majorité des
     colonnes devraient être marquées non nulle.
    </para>
   </tip>
  </sect2>

  <sect2>
   <title>Contraintes Uniques</title>

   <indexterm>
    <primary>contrainte unique</primary>
   </indexterm>

   <indexterm>
    <primary>contrainte</primary>
    <secondary>unique</secondary>
   </indexterm>

   <para>
    Les contraintes uniques garantissent que les données contenues dans
    la colonne ou un groupe de colonnes est unique par rapport à toutes
    les rangées dans la table. La syntaxe est:
<programlisting>
CREATE TABLE produits (
    no_produit integer <emphasis>UNIQUE</emphasis>,
    nom text,
    prix numeric
);
</programlisting>
    est écrit comme contrainte de colonne et
<programlisting>
CREATE TABLE produits (
    no_produit integer,
    nom text,
    prix numeric,
    <emphasis>UNIQUE (no_produit)</emphasis>
);
</programlisting>
    est écrit comme contrainte de table.
   </para>

   <para>
    Si une contrainte unique fait référence à un groupe de colonnes,
    celles-ci sont listées séparées par des virgules:
<programlisting>
CREATE TABLE exemple (
    a integer,
    b integer,
    c integer,
    <emphasis>UNIQUE (a, c)</emphasis>
);
</programlisting>
   </para>

   <para>
    Il est aussi possible d'attribuer des noms à des contraintes uniques:
<programlisting>
CREATE TABLE produits (
    no_produit integer <emphasis>CONSTRAINT must_be_different</emphasis> UNIQUE,
    nom text,
    prix numeric
);
</programlisting>
   </para>

   <indexterm>
    <primary>valeur nulle</primary>
    <secondary sortas="unique constraints">avec contrainte unique</secondary>
   </indexterm>

   <para>
    En général, une contrainte unique est violée lorsqu'il y'a (au
    moins) deux rangées dans une table ou la valeur de chacune des
    colonnes correspondantes qui font partie de la contrainte sont
    égales. Par contre, les valeurs nulles ne sont pas assimilées
    à une égalités dans cette situation. Ceci veut dire qu'il est possible
    de stocker un nombre illimité de rangées qui contiennent une
    valeur nulle dans au moins l'une des colonnes contraintes. Ce
    comportement est conforme au standard SQL mais nous avons
    été informé que d'autres bases SQL ne suivent pas cette règle. Alors,
    soyez prudents en développant des applications qui sont prévues
    pour être portable.
   </para>
  </sect2>

  <sect2>
   <title>Clés Primaires</title>

   <indexterm>
    <primary>clé primaire</primary>
   </indexterm>

   <indexterm>
    <primary>contrainte</primary>
    <secondary>clé primaire</secondary>
   </indexterm>

   <para>
    Techniquement, une contrainte de clé primaire est tout
    simplement une combinaison d'une contrainte unique et
    d'une contrainte non nulle. Donc, les définitions de
    tables suivantes accepteront les mêmes données:
<programlisting>
CREATE TABLE produits (
    no_produit integer UNIQUE NOT NULL,
    nom text,
    prix numeric
);
</programlisting>

<programlisting>
CREATE TABLE produits (
    no_produit integer <emphasis>PRIMARY KEY</emphasis>,
    name text,
    prix numeric
);
</programlisting>
   </para>

   <para>
    Les clés primaires peuvent contraindre sur plus d'une colonne; la
    syntaxe est semblable aux contraintes uniques:
<programlisting>
CREATE TABLE exemple (
    a integer,
    b integer,
    c integer,
    <emphasis>PRIMARY KEY (a, c)</emphasis>
);
</programlisting>
   </para>

   <para>
    Une clé primaire indique qu'une colonne ou un groupe de colonnes peut
    être utilisé comme identifiant unique pour les rangées de la table. (Ceci
    est une conséquence directe de la définition d'une clé primaire. Notez
    qu'une contrainte unique ne donne pas par elle-même, un identifiant unique
    car elle n'exclut pas les valeurs nulles.) Ceci est pratique à la fois
    pour des raisons de documentation et pour les applications clientes. Par
    exemple, une application graphique qui permet de modifier les valeurs de
    rangées a probablement besoin de connaître la clé primaire d'une table pour
    pouvoir identifier les rangées de manière unique correctement.
   </para>

   <para>
    Une table peut avoir au mieux une clé primaire (tandis qu'elle peut
    avoir plusieurs contraintes uniques et non nulles). La théorie des
    bases de données relationnelles dit que chaque table doit avoir
    une clé primaire. Cette règle n'est pas appliquée par
    <productname>PostgreSQL</productname>, mais il vaut mieux la respecter
    autant que possible.
   </para>
  </sect2>

  <sect2 id="ddl-constraints-fk">
   <title>Clés Étrangères</title>

   <indexterm>
    <primary>clé étrangère</primary>
   </indexterm>

   <indexterm>
    <primary>contrainte</primary>
    <secondary>clé étrangère</secondary>
   </indexterm>

   <indexterm>
    <primary>intégrité référentielle</primary>
   </indexterm>

   <para>
    Une contrainte de clé étrangère stipule que les valeurs dans cette
    colonne (ou un groupe de colonnes) doit correspondre aux valeurs
    apparaissant dans des rangées d'une autre table.
    Nous disons que ceci maintient l'<firstterm>intégrité
    référentielle</firstterm> entre deux tables liées.
   </para>

   <para>
    Disons que vous avez la table de produits que nous avons déjà utilisé plusieurs fois:
<programlisting>
CREATE TABLE produits (
    no_produit integer PRIMARY KEY,
    name text,
    prix numeric
);
</programlisting>
    Disons aussi que vous avez une table stockant les commandes de
    ces produits. Nous voulons aussi nous assurer que la table des
    commandes ne contienne que des commandes concernant des produits
    qui existent réellement. Alors, nous définissons une contrainte
    de clé étrangère dans la table des commandes qui référence la
    table produit:
<programlisting>
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    no_produit integer <emphasis>REFERENCES produits (no_produit)</emphasis>,
    quantity integer
);
</programlisting>
    Maintenant, il est impossible de créer des commandes avec une entrée
    <literal>no_produit</literal> qui n'apparaît pas dans la table produits.
   </para>

   <para>
    Nous disons que, dans cette situation, la table de commandes est la table
    <firstterm>référente</firstterm> et la table produits est la table
    <firstterm>référée</firstterm>.  De la même façon, il y a des colonnes
    référentes et des colonnes référées.
   </para>

   <para>
    On peut aussi raccourcir la commande ci-dessus en
<programlisting>
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    no_produit integer REFERENCES produits,
    quantity integer
);
</programlisting>
    parce qu'en l'absence d'une liste de colonnes, la clé primaire de la
    table référente est utilisée comme colonne référée.
   </para>

   <para>
    Une clé étrangère peut aussi contraindre et référencer un groupe de colonnes.
    Comme d'habitude, il faut aussi l'écrire sous forme de contrainte de table.
    Voici un exemple de syntaxe:
<programlisting>
CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  <emphasis>FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)</emphasis>
);
</programlisting>
    Bien sur, le nombre et le type des colonnes contraintes doit correspondre
    aux nombres et types des colonnes référées.
   </para>

   <para>
    Une table peut contenir plus d'une contrainte de clé étrangère. Ceci peut
    être utilisé pour implémenter des relations n à n entre tables. Disons
    que vous avez des tables contenant des produits et des commandes mais vous
    voulez maintenant autoriser une commande qui contient peut-être beaucoup
    de produits (ce que la structure ci-dessus ne permet pas). On pourrait
    utiliser cette structure de table:
<programlisting>
CREATE TABLE produits (
    no_produit integer PRIMARY KEY,
    name text,
    prix numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    no_produit integer REFERENCES produits,
    order_id integer REFERENCES orders,
    quantity integer,
    PRIMARY KEY (no_produit, order_id)
);
</programlisting>
    Notez aussi que la clé primaire chevauche les clés étrangères dans la dernière table.
   </para>

   <indexterm>
    <primary>CASCADE</primary>
    <secondary>action clé étrangère</secondary>
   </indexterm>

   <indexterm>
    <primary>RESTRICT</primary>
    <secondary>action clé étrangère</secondary>
   </indexterm>

   <para>
    Nous savons que les clés étrangères n'autorisent pas la création
    de commandes qui ne sont pas liés à un produit. Et si un produit
    est retiré après qu'une commande qui y réfère soit créée ? SQL vous
    permet aussi de le spécifier. Intuitivement, nous avons plusieurs options:
    <itemizedlist spacing="compact">
     <listitem><para>Interdire d'effacer un produit référé</para></listitem>
     <listitem><para>Effacer aussi les commandes</para></listitem>
     <listitem><para>Autre chose ?</para></listitem>
    </itemizedlist>
   </para>

   <para>
    Pour illustrer ce cas, implémentons la politique suivante sur
    l'exemple de relations n à n évoquée plus haut: Quand quelqu'un
    veut retirer un produit qui est encore référencé par un ordre
    (via <literal>ordre_items</literal>), on l'interdit. Si quelqu'un
    retire une commande, les éléments de l'ordre sont aussi retirés.
<programlisting>
CREATE TABLE produits (
    no_produit integer PRIMARY KEY,
    name text,
    prix numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    no_produit integer REFERENCES produits <emphasis>ON DELETE RESTRICT</emphasis>,
    order_id integer REFERENCES orders <emphasis>ON DELETE CASCADE</emphasis>,
    quantity integer,
    PRIMARY KEY (no_produit, order_id)
);
</programlisting>
   </para>

   <para>
    Restreindre et mettre en cascade les effacements sont les deux options les plus récurrentes.
    <literal>RESTRICT</literal> peut aussi s'écrire <literal>NO
    ACTION</literal> et c'est aussi le cas par défaut si on ne
    précise rien du tout. Il y'a deux autres options pour ce qui devrait
    se passer sur les colonnes de clé étrangère lorsqu'une clé primaire
    est effacée :
    <literal>SET NULL</literal> et <literal>SET DEFAULT</literal>.
    Notez que ceci ne vous permet pas de passer outre ces contraintes.
    Par exemple, si une action précise <literal>SET DEFAULT</literal>
    mais que la valeur par défaut ne satisfait pas la clé étrangère, la
    suppression de la clé primaire peut échouer.
   </para>

   <para>
    Sur le même principe que <literal>ON DELETE</literal>, il y a aussi
    <literal>ON UPDATE</literal> qui est évoqué lorsqu'une clé primaire
    est modifiée (mise à jour). Les actions possibles sont les mêmes.
   </para>

   <para>
    Il y a plus d'informations sur la mise à jour et la suppression de données dans <xref
    linkend="dml">.
   </para>

   <para>
    Enfin, nous devrions dire que la clé étrangère peut référencer
    des colonnes qui sont soit une clé primaire ou former une contrainte unique.
    Si la clé étrangère référence une contrainte unique, il y a des
    possibilités supplémentaires selon que l'on souhaite faire correspondre
    les valeurs nulles.
    Ceux-ci sont expliqués dans la documentation de référence pour
    <xref linkend="sql-createtable" endterm="sql-createtable-title">.
   </para>
  </sect2>
 </sect1>

 <sect1 id="ddl-inherit">
  <title>Héritage</title>

  <comment>Cette section doit être repensée. Une partie de cette
  information doit aller dans les chapitres suivants.</comment>

  <para>
   Créons deux tables. La table capitales contient les capitales
   d'état qui sont aussi des villes. Naturellement, la table
   capitales doit hériter de villes.

<programlisting>
CREATE TABLE villes (
    name            text,
    population      float,
    altitude        int     -- (in ft)
);

CREATE TABLE capitales (
    state           char(2)
) INHERITS (villes);
</programlisting>

   Dans ce cas une rangée de capitales <firstterm>hérite</firstterm> tout
   les attributs  (nom,  population, et altitude) de son parent
   villes. Le type de l'attribut name est
   <type>text</type>,  un type natif de <productname>PostgreSQL</productname>
   pour des chaînes de caractères de longueur variable. Le type de l'attribut
   population est
   <type>float</type>, un type natif de <productname>PostgreSQL</productname>
   pour les nombres flottants de précision double. Les capitales d'états ont
   un attribut supplémentaire state qui donne leur état. Dans <productname>PostgreSQL</productname>,
   une table peut hériter de zéro tables ou plus et une requête peut référencer
   toutes les rangées d'une table ou toutes les rangées d'une table plus celles de
   ses descendants.

   <note>
    <para>
     La hiérarchie d'héritage est en fait un graphe acyclique dirigé.
    </para>
   </note>
  </para>

  <para>
    Par exemple, la requête suivante cherche les noms de toutes les villes,
    y compris les capitales d'état, qui se situent à une altitude de plus
    de 500 pieds:

<programlisting>
SELECT name, altitude
    FROM villes
    WHERE altitude &gt; 500;
</programlisting>

   qui retourne:

<programlisting>
   name    | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953
 Madison   |      845
</programlisting>
  </para>

  <para>
    D'un autre côté, la requête suivante cherche toutes
    les villes qui ne sont pas des capitales d'état et
    qui sont situés à une altitude de plus de 500 pieds:

<programlisting>
SELECT name, altitude
    FROM ONLY villes
    WHERE altitude &gt; 500;

   name    | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953
</programlisting>         
  </para>

  <para>
   Ici, le <quote>ONLY</quote> avant villes indique que la requête ne devrait
   être lancée que sur villes et non les tables en dessous de villes dans la
   hiérarchie d'héritage.  Beaucoup des commandes donc nous avons déjà discuté
   -- <command>SELECT</command>, <command>UPDATE</command> et <command>DELETE</command> --
   gèrent cette syntaxe <quote>ONLY</quote>.
  </para>

  <para>
  Dans certain cas, vous souhaiterez savoir dans quel table
  provient une rangée donnée. Il y a une colonne système appelée
  <structfield>TABLEOID</structfield> dans chaque table qui peut vous
  donner la table d'origine:

<programlisting>
SELECT c.tableoid, c.name, c.altitude
FROM villes c
WHERE c.altitude &gt; 500;
</programlisting>

   qui renvoie :

<programlisting>
 tableoid |   name    | altitude
----------+-----------+----------
   139793 | Las Vegas |     2174
   139793 | Mariposa  |     1953
   139798 | Madison   |      845
</programlisting>

   (Si vous essayez de reproduire cet exemple, vous obtiendrez probablement des OIDs numériques différents.)
   En faisant une jointure avec <structname>pg_class</structname>, vous pourrez voir les noms de tables actuelles:

<programlisting>
SELECT p.relname, c.name, c.altitude
FROM villes c, pg_class p
WHERE c.altitude &gt; 500 and c.tableoid = p.oid;
</programlisting>

   ce qui retourne:

<programlisting>
 relname   |   name    | altitude
-----------+-----------+----------
 villes    | Las Vegas |     2174
 villes    | Mariposa  |     1953
 capitales | Madison   |      845
</programlisting>
   
  </para>

  <note>
   <title>Obsolète</title> 
   <para>
     Dans les versions précédentes de <productname>PostgreSQL</productname>, le
     comportement par défaut était de ne pas inclure les tables filles dans les requêtes.
     Ceci provoquait des erreurs et était aussi en violation du standard SQL99.
     Sous l'ancienne syntaxe, pour avoir les sous-tables, il fallait
     ajouter <literal>*</literal> au nom de la table.
     Par exemple
<programlisting>
SELECT * from villes*;
</programlisting>
     Vous pouvez toujours préciser explicitement les tables filles en rajoutant
     <literal>*</literal> en plus de spécifier explicitement de ne pas parcourir
     les tables filles en écrivant <quote>ONLY</quote>. Mais, a partir de la
     version 7.1, le comportement par défaut pour un nom de table brut est de
     parcourir aussi ses tables filles bien qu'avant, la norme était de ne
     pas le faire. Pour retrouver l'ancien comportement, mettez l'option
     de configuration <literal>SQL_Inheritance</literal> à off, i.e.,
<programlisting>
SET SQL_Inheritance TO OFF;
</programlisting>
     ou ajoutez une ligne dans votre fichier <filename>postgresql.conf</filename>.
   </para>
  </note>

  <para>
   Une limite de la fonctionnalité d'héritage est que les indexes (y compris
   les contraintes uniques) et les contraintes de clés étrangères ne s'appliquent
   qu'aux tables seules et non leurs descendants d'héritage. Donc, dans l'exemple
   précédant, spécifier qu'une colonne d'une autre table <literal>REFERENCES villes(name)</literal>
   autorisera l'autre table a contenir des noms de villes mais pas de noms de capitales.
   Cette lacune sera probablement corrigée dans une future version.
  </para>
 </sect1>

 <sect1 id="ddl-alter">
  <title>Modification des Tables</title>

  <indexterm zone="ddl-alter">
   <primary>table</primary>
   <secondary>modification</secondary>
  </indexterm>

  <para>
   Quand on crée une table et qu'on se rend compte qu'on a fait une erreur
   ou que les besoins de l'application ont changés, on peut alors effacer
   la table et la recréer. Mais ceci n'est pas pratique si la table contient
   déjà des données ou si la table est référencée par d'autres objets de la base</