Documentation PostgreSQL 8.2.23 > Langage SQL > Définition des données > L'héritage | |
Schémas | Partitionnement |
PostgreSQL™ implante l'héritage des tables, qui peut s'avérer très utile pour les concepteurs de bases de données. (SQL:1999 et les versions suivantes définissent une fonctionnalité d'héritage de type qui diffère par de nombreux aspects des fonctionnalités décrites ici.)
Soit l'exemple d'un modèle de données de villes. Chaque état comporte plusieurs villes mais une seule capitale. Pour récupérer rapidement la ville capitale d'un état donné, on peut créer deux tables, une pour les capitales et une pour les villes qui ne sont pas des capitales. Mais, que se passe-t'il dans le cas où toutes les données d'une ville doivent être récupérées, qu'elle soit une capitale ou non ? L'héritage peut aider à résoudre ce problème. La table capitales est définie pour hériter de villes :
CREATE TABLE villes ( nom text, population float, altitude int -- (en pied) ); CREATE TABLE capitales ( etat char(2) ) INHERITS (villes);
Dans ce cas, la table capitales hérite de toutes les colonnes de sa table parent, villes. Les capitales ont aussi une colonne supplémentaire, etat, qui indique l'état dont elles sont capitales.
Dans PostgreSQL™, une table peut hériter de zéro à plusieurs autres tables et une requête faire référence à toutes les lignes d'une table ou à toutes les lignes d'une table plus celles des descendantes. Ce dernier comportement est celui par défaut.
Par exemple, la requête suivante retourne les noms et altitudes de toutes les villes, y compris les capitales, situées à une altitude supérieure à 500 pieds :
SELECT nom, altitude FROM villes WHERE altitude > 500;
Étant donné les données du tutoriel de PostgreSQL™ (voir Section 2.1, « Introduction »), ceci renvoie :
nom | altitude -----------+---------- Las Vegas | 2174 Mariposa | 1953 Madison | 845
D'un autre côté, la requête suivante retourne les noms et altitudes de toutes les villes, qui ne sont pas des capitales, situées à une altitude supérieure à 500 pieds :
SELECT nom, altitude FROM ONLY villes WHERE altitude > 500; nom | altitude -----------+---------- Las Vegas | 2174 Mariposa | 1953
Le mot clé ONLY indique que la requête s'applique uniquement aux villes, et non pas à toutes les tables en-dessous de villes dans la hiérarchie de l'héritage. Un grand nombre des commandes déjà évoquées -- SELECT, UPDATE et DELETE -- supportent le mot clé ONLY.
Dans certain cas, il peut être intéressant de savoir de quelle table provient une ligne donnée. Une colonne système appelée TABLEOID présente dans chaque table donne la table d'origine :
SELECT v.tableoid, v.nom, v.altitude FROM villes v WHERE v.altitude > 500;
qui renvoie :
tableoid | nom | altitude ----------+-----------+---------- 139793 | Las Vegas | 2174 139793 | Mariposa | 1953 139798 | Madison | 845
(La reproduction de cet exemple conduira probablement à des OID numériques différents). Une jointure avec pg_class, permet d'obtenir les noms réels des tables :
SELECT p.relname, v.nom, v.altitude FROM villes v, pg_class p WHERE v.altitude > 500 and v.tableoid = p.oid;
ce qui retourne :
relname | nom | altitude -----------+-----------+---------- villes | Las Vegas | 2174 villes | Mariposa | 1953 capitales | Madison | 845
L'héritage ne propage pas automatiquement les données des commandes INSERT ou COPY aux autres tables de la hiérarchie de l'héritage. Dans l'exemple considéré, l'instruction INSERT suivante échoue :
INSERT INTO villes (nom, population, altitude, etat) VALUES ('New York', NULL, NULL, 'NY');
On peut espérer que les données soient magiquement routées vers la table capitales mais cela n'arrive pas : INSERT insère toujours dans la table indiquée. Dans certains cas, il est possible de rediriger l'insertion en utilisant une règle (voir Chapitre 35, Système de règles). Néanmoins, cela n'est d'aucune aide dans le cas ci-dessus car la table villes ne contient pas la colonne etat. La commande est donc rejetée avant que la règle ne soit appliquée.
Toutes les contraintes de vérification et toutes les contraintes NOT NULL sur une table parent sont automatiquement héritées par les tables enfants. Les autres types de contraintes (unicité, clé primaire, clé étrangère) ne sont pas hérités.
Une table peut hériter de plusieurs tables, auquel cas elle possède l'union des colonnes définies par les tables parents. Toute colonne déclarée dans la définition de la table enfant est ajoutée à celles-ci. Si le même nom de colonne apparaît dans plusieurs tables parent, ou à la fois dans une table parent et dans la définition de la table enfant, alors ces colonnes sont « assemblées » pour qu'il n'en existe qu'une dans la table enfant. Pour être assemblées, les colonnes doivent avoir le même type de données, sinon une erreur est levée. La colonne assemblée hérite de toutes les contraintes de vérification en provenance de chaque définition de colonnes dont elle provient, et est marquée NOT NULL si une d'entre elles l'est.
L'héritage de table est typiquement établi lors de la création de la table enfant en utilisant la clause INHERITS de l'instruction CREATE TABLE. Alternativement, il est possible d'ajouter à une table déjà définie de façon compatible une nouvelle relation de parenté à l'aide de la clause INHERIT de ALTER TABLE. Pour cela, la nouvelle table enfant doit déjà inclure des colonnes de mêmes nom et type que les colonnes de la table parent. Elle doit aussi contenir des contraintes de vérification de mêmes nom et expression que celles de la table parent.
De la même façon, un lien d'héritage peut être supprimé d'un enfant à l'aide de la variante NO INHERIT d'ALTER TABLE. Ajouter et supprimer dynamiquement des liens d'héritage de cette façon est utile quand cette relation d'héritage est utilisée pour le partitionnement des tables (voir Section 5.9, « Partitionnement »).
Un moyen pratique de créer une table compatible en vue d'en faire ultérieurement une table enfant est d'utiliser la clause LIKE dans CREATE TABLE. Ceci crée une nouvelle table avec les même colonnes que la table source. S'il existe des contraintes CHECK définies sur la table source, l'option INCLUDING CONSTRAINTS de LIKE doit être indiquée car le nouvel enfant doit avoir des contraintes qui correspondent à celles du parent pour être considéré compatible.
Une table parent ne peut pas être supprimée tant qu'elle a des enfants. Pas plus que les colonnes de tables enfants ne peuvent être supprimées ou modifiées si elles sont héritées d'une table parent. La suppression d'une table et de tous ces descendants peut être aisément obtenue en supprimant la table parent avec l'option CASCADE.
ALTER TABLE propage toute modification dans les définitions des colonnes et contraintes de vérification à travers la hiérarchie d'héritage. Là encore, supprimer des colonnes ou des contraintes sur des tables parentes n'est possible qu'avec l'option CASCADE. ALTER TABLE suit les mêmes règles d'assemblage de colonnes dupliquées et de rejet que l'instruction CREATE TABLE.
Les droits d'accès des tables ne sont pas automatiquement hérités. De ce fait, un utilisateur qui tente d'accéder à une table parent doit, soit avoir les permissions pour réaliser l'opération sur toutes les tables enfants, soit utiliser le mot clé ONLY. Lors de l'ajout d'une nouvelle table enfant à un héritage existant, il faut s'assurer que tous les droits s'y appliquant soient accordés.
Il existe une réelle limitation à la fonctionnalité d'héritage : les index (ce qui inclue les contraintes d'unicité) et les contraintes de clés étrangères ne s'appliquent qu'aux tables, pas à leurs héritiers. Cela est vrai pour le côté référençant et le côté référencé d'une contrainte de clé étrangère. Ce qui donne, dans les termes de l'exemple ci-dessus :
si villes.nom est déclarée UNIQUE ou clé primaire (PRIMARY KEY), cela n'empêche pas la table capitales de posséder des lignes avec des noms dupliqués dans villes. Et ces lignes dupliquées s'affichent par défaut dans les requêtes sur villes. En fait, par défaut, capitales n'a pas de contrainte d'unicité du tout et, du coup, peut contenir plusieurs lignes avec le même nom. Une contrainte d'unicité peut être ajoutée à capitales mais cela n'empêche pas la duplication avec villes ;
de façon similaire, s'il faut préciser que villes.nom fait référence (REFERENCES) à une autre table, cette contrainte n'est pas automatiquement propagée à capitales. Il est facile de contourner ce cas de figure en ajoutant manuellement la même contrainte REFERENCES à capitales ;
l'indication que la colonne d'une autre table REFERENCES villes(nom) autorise l'autre table à contenir les noms des villes mais pas les noms des capitales. Il n'existe pas de contournement efficace de ce cas.
Ces déficiences seront probablement corrigées dans une version future, mais, en attendant, il est obligatoire de réfléchir consciencieusement à l'utilité de l'héritage pour un problème donné.
Dans les versions de PostgreSQL™ antérieures à la 7.1, le comportement par défaut consistait à ne pas inclure les tables enfants dans les requêtes. Il s'est avéré que cela était source d'erreur et violait le standard SQL. Ce comportement peut être retrouvé en désactivant le paramètre sql_inheritance.