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, elevation 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 aux lignes d'une table ou à celles d'une table et de ses descendantes. Ce dernier comportement est celui par défaut.
Par exemple, la requête suivante retourne les noms et elevations de toutes les villes, y compris les capitales, situées à une élévation supérieure à 500 pieds :
SELECT nom, elevation FROM villes WHERE elevation > 500;
Avec les données du tutoriel de PostgreSQL (voir Section 2.1), ceci renvoie :
nom | elevation -----------+----------- Las Vegas | 2174 Mariposa | 1953 Madison | 845
D'un autre côté, la requête suivante retourne les noms et elevations de toutes les villes, qui ne sont pas des capitales, situées à une élévation supérieure à 500 pieds :
SELECT nom, elevation FROM ONLY villes WHERE elevation > 500; nom | elevation -----------+----------- 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
.
Vous pouvez aussi écrire le nom de la table avec une *
à la fin pour indiquer spécifiquement que les tables filles sont
incluses :
SELECT name, elevation FROM cities* WHERE elevation > 500;
Écrire *
n'est pas nécessaire, puisque ce comportement
est toujours le comportement par défaut. Toutefois, cette syntaxe est
toujours supportée pour raison de compatibilité avec les anciennes versions
où le comportement par défaut pouvait être changé.
Dans certains 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.elevation FROM villes v WHERE v.elevation > 500;
qui renvoie :
tableoid | nom | elevation ----------+-----------+----------- 139793 | Las Vegas | 2174 139793 | Mariposa | 1953 139798 | Madison | 845
(Reproduire cet exemple conduit 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.elevation FROM villes v, pg_class p WHERE v.elevation > 500 AND v.tableoid = p.oid;
ce qui retourne :
relname | nom | elevation -----------+-----------+----------- villes | Las Vegas | 2174 villes | Mariposa | 1953 capitales | Madison | 845
Une autre manière d'obtenir le même effet est d'utiliser le
pseudo-type regclass
qui affichera l'OID de la table de
façon symbolique :
SELECT v.tableoid::regclass, v.nom, v.elevation FROM villes v WHERE v.elevation > 500;
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, elevation, etat) VALUES ('Albany', NULL, NULL, 'NY');
On pourrait espérer que les données soient magiquement routées vers la table
capitales
, mais ce n'est pas le cas :
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 40). 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,
sauf si elles sont spécifiées explicitement avec des clauses NO
INHERIT
.
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 mères. Toute colonne déclarée dans la définition de la table enfant est ajoutée à cette dernière. Si le même nom de colonne apparaît dans plusieurs tables mères, ou à la fois dans une table mère 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. Les contraintes de vérification et les contraintes non NULL héritables sont assemblées de façon similaire. De ce fait, par exemple, une colonne assemblée sera marquée non NULL si une des définitions de colonne d'où elle provient est marquée non NULL. Les contraintes de vérification sont assemblées si elles ont le même nom, et l'assemblage échouera si leurs conditions sont différentes.
L'héritage de table est établi à la création de la table
enfant, à l'aide de la clause INHERITS
de l'instruction
CREATE TABLE.
Alternativement, il est possible d'ajouter à une table, 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.10).
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êmes
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 mère ne peut pas être supprimée tant qu'elle a des enfants.
Pas plus que les colonnes ou les contraintes de vérification des tables
enfants ne peuvent être supprimées ou modifiées si elles sont héritées.
La suppression d'une table et de tous ses descendants peut être aisément
obtenue en supprimant la table mère avec l'option
CASCADE
(voir Section 5.13).
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 qui dépendent d'autres tables mères 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 requêtes sur tables héritées réalisent des vérifications de droit sur
la table parent seulement. De ce fait, par exemple, donner le droit
UPDATE
sur la table villes
implique que les droits de mise à jour des lignes dans la table
capitales
soient elles aussi vérifiées quand elles
sont accédées via la table villes
. Ceci préserve
l'apparence que les données proviennent (aussi) de la table parent. Mais la
table capitales
ne pouvait pas être mise à jour
directement sans droit supplémentaire. Deux exceptions à
cette règle sont TRUNCATE
et LOCK
TABLE
, où les droits sur les tables filles sont toujours
vérifiées qu'elles soient traitées directement ou par récursivité via les
commandes réalisées sur la table parent.
De façon similaire, les politiques de sécurité au niveau ligne de la table parent (voir Section 5.7) sont appliquées aux lignes provenant des tables filles avec une requête héritée. Les politiques de tables enfant sont appliquées seulement quand la table enfant est explicitement nommée dans la requête. Dans ce cas, toute politique attachée à ses parents est ignorée.
Les tables distantes (voir Section 5.11) peuvent aussi participer aux hiérarchies d'héritage, soit comme table parent, soit comme table enfant, comme les tables standards peuvent l'être. Si une table distante fait partie d'une hiérarchie d'héritage, toutes les opérations non supportées par la table étrangère ne sont pas non plus supportées sur l'ensemble de la hiérarchie.
Notez que toutes les commandes SQL fonctionnent avec les
héritages. Les commandes utilisées pour récupérer des données, pour modifier
des données ou pour modifier le schéma (autrement dit
SELECT
, UPDATE
, DELETE
,
la plupart des variantes de ALTER TABLE
, mais pas
INSERT
ou ALTER TABLE ... RENAME
)
incluent par défaut les tables filles et supportent la notation
ONLY
pour les exclure. Les commandes qui font de la
maintenance de bases de données et de la configuration (par exemple
REINDEX
, VACUUM
) fonctionnent
typiquement uniquement sur les tables physiques, individuelles et ne
supportent pas la récursion sur les tables de l'héritage. Le comportement
respectif de chaque commande individuelle est documenté dans la référence
(Commandes SQL).
Il existe une réelle limitation à la fonctionnalité d'héritage : les index (dont les contraintes d'unicité) et les contraintes de clés étrangères ne s'appliquent qu'aux tables mères, pas à leurs héritiers. Cela est valable 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, si
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
;
si une autre table indique REFERENCES villes(nom)
,
cela l'autorise à contenir les noms des villes, mais pas les noms des
capitales. Il n'existe pas de contournement efficace de ce cas.
Certaines fonctionnalités non implémentées pour les hiérarchies d'héritage sont disponibles pour le partitionnement déclaratif. Il est de ce fait nécessaire de réfléchir consciencieusement à l'utilité de l'héritage pour une application donnée.