Fonctionnalités et limites de l'héritage dans postgresql

Publié le 16 mars 2008 par greg

Aujourd'hui, comment récupérer le type d'un enregistrement issu d'un héritage et quelle est la grosse limitation de l'héritage avec postgresql.

Je vous ai parlé la dernière fois de l'héritage dans postgresql. Reprenons le cas de notre bibliothèque avec des ouvrages qui peuvent être des magazines ou des livres.

CREATE TABLE ouvrage (
    id integer NOT NULL,
    created_at timestamp without time zone DEFAULT now() NOT NULL
);
CREATE TABLE livre (
    isbn character varying(255) NOT NULL,
    title character varying(255) NOT NULL,
    author character varying(255) NOT NULL
)
INHERITS (ouvrage);
CREATE TABLE magazine (
    title character varying(255) NOT NULL,
    published_at timestamp without time zone,
    issue integer,
    CONSTRAINT magazine_check CHECK (((published_at IS NOT NULL) OR (number IS NOT NULL))),
    CONSTRAINT magazine_issue_check CHECK ((issue > 0))
)
INHERITS (ouvrage);

Ajoutons rapidement deux magazines et un livre :

INSERT INTO livre (isbn, title, author) VALUES ('2840117495', 'L''élégance du hérisson', 'Muriel Barbery');
INSERT INTO magazine (title, published_at, issue) VALUES ('Linux magazine', '2008-03-01', 103), ('linux magazine', '2008-02-01', 102);

et jettons maintenant un coup d'oeil à notre table ouvrage :

SELECT * FROM ouvrage;
test=> SELECT * FROM ouvrage;
id | created_at
----+----------------------------
7 | 2008-03-16 23:55:13.463453
8 | 2008-03-16 23:56:01.075842
9 | 2008-03-16 23:56:01.075842

Impossible à partir de cette table de savoir qui est quoi. Heureusement, Postgresql nous permet de retrouver nos petits grâce à l'utilisation de tables internes :

SELECT ouvrage.*, pg_class.relname AS "type" FROM ouvrage, pg_class WHERE ouvrage.tableoid = pg_class.oid;
id | created_at | type
----+----------------------------------------+----------
7 | 2008-03-16 23:55:13.463453 | livre
8 | 2008-03-16 23:56:01.075842 | magazine
9 | 2008-03-16 23:56:01.075842 | magazine

Nous voulons maintenant créer une table qui retrace les emprunts des différents ouvrages :

CREATE TABLE emprunt (id SERIAL PRIMARY KEY, ouvrage_id INT NOT NULL REFERENCES ouvrage (id) ON DELETE CASCADE, borrowed_at TIMESTAMP NOT NULL DEFAULT now(), returned_at TIMESTAMP , CONSTRAINT returned_after_borrowed CHECK (returned_at > borrowed_at));

Essayons maintenant d'emprunter l'édition numéro 103 de «linux magazine» :

INSERT INTO emprunt (ouvrage_id) VALUES (8);
ERROR: insert or update on table "emprunt" violates foreign key constraint "emprunt_ouvrage_id_fkey"
DETAIL: Key (ouvrage_id)=(8) is not present in table "ouvrage".

Postgresql nous dit que l'ouvrage avec id=8 n'existe pas dans la table ouvrage, et pour cause cet id n'existe réellement que dans la table magazine et ne vérifie donc pas la clé étrangère sur ouvrage quelque soit le résultat affiché d'un SELECT sur ouvrage.

Cette limitation de postgresql est sérieuse et il n'existe pas aujourd'hui de parade heureuse. Il est indispensable de prendre cette contrainte en compte lorsque vous faite votre schéma de base de données.

Sans cette fonctionnalité, mon projet de model orienté objet basé sur postgresql tombe à l'eau. Allez, si j'ai le courage, la prochaine fois, je vous parlerai des schémas.