Discussion :

[MaitrePylos]

Bonjour,
J'ai un peu de mal à écrire ma DDL concernant le cas de figure suivant (notez que je suis peut-être à côté de mon sujet).

J'ai une table user classique (id,nom,prenom....).
Les users peuvent être prof et/ou eleve et/ou administratif ou les trois ensembles ou aucun d'eux.

J'ai dessiné comme ceci :

En PostgreSQL, j'ai créé comme ceci :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
 
CREATE TABLE public.users(
	id_user serial NOT NULL,
	nom varchar(50) NOT NULL,
	prenom varchar(50),
	matricule integer NOT NULL,
	CONSTRAINT users_pk PRIMARY KEY (id_user)
 
);
 
-- object: public.professeurs | type: TABLE --
-- DROP TABLE IF EXISTS public.professeurs CASCADE;
CREATE TABLE public.professeurs(
	id_professeur serial NOT NULL,
-- 	id_user integer NOT NULL,
-- 	nom varchar(50) NOT NULL,
-- 	prenom varchar(50),
-- 	matricule integer NOT NULL,
	CONSTRAINT proffeseurs_pk PRIMARY KEY (id_professeur)
 
) INHERITS(public.users)
;
-- ddl-end --
ALTER TABLE public.professeurs OWNER TO postgres;
-- ddl-end --
 
-- object: public.eleves | type: TABLE --
-- DROP TABLE IF EXISTS public.eleves CASCADE;
CREATE TABLE public.eleves(
	id_eleve serial NOT NULL,
--	id_user integer NOT NULL,
-- 	nom varchar(50) NOT NULL,
-- 	prenom varchar(50),
-- 	matricule integer NOT NULL,
	CONSTRAINT eleves_pk PRIMARY KEY (id_eleve)
 
) INHERITS(public.users)
;
 
CREATE TABLE public.administratif(
	id_administratif serial NOT NULL,
-- 	id_user integer NOT NULL,
-- 	nom varchar(50) NOT NULL,
-- 	prenom varchar(50),
-- 	matricule integer NOT NULL,
	CONSTRAINT administratifs_pk PRIMARY KEY (id_administratif)
 
) INHERITS(public.users)
;

Cela fonctionne comme voulu, sauf que si je crée un prof ok, c'est bien hérité dans users, par contre si je crée le même users dans eleves, alors il se duplique dans users avec la même PK dans users ????

Voici un select de ma table user :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
id_user |nom        |prenom   |matricule |
--------|-----------|---------|----------|
3       |Marchal    |Philippe |8745      |
4       |Ismael     |Aras     |784       |
1       |Ernaelsten |Gérard   |1254      |
2       |Devos      |Denis    |2541      |
4       |Ismael     |Aras     |784       |

On voit que j'ai deux fois id_user : 4

Pouvez-vous me donner une piste de là où je me trompe ?
D'avance je vous remercie.

[CinePhil]

Bonjour,

En principe, lorsqu'on modélise un héritage dans un MCD, on ne met pas d'identifiant dans les entité-types filles puisque celles-ci-héritent en principe de l'identifiant de la table mère.

user (id, nom, prenom) => Ici, id peut être serial
eleve (id_user...) => ici, id n'est qu'un entier

Je pense donc que ton problème vient du serial sur les identifiants des tables filles.

Par contre, je n'ai jamais utilisé l'héritage de PostgreSQL. J'ai toujours eu des doutes sur la duplication de données entre la table mère et la table fille.

[MaitrePylos]

OK, pour le id dans les tables filles.
L'héritage , fonctionne plutôt bien sur PostgreSQL en dehors de cette duplication de données sur une PK.
En gros si je comprend bien, j'ai bon et c'est le moteur qui déconne ?

[CinePhil]

Non, dans ton MCD, tu ne devrais pas mettre de propriété id dans les entités-types filles.

Et du coup, si j'ai bien compris l'héritage de PostgreSQL que je viens de consulter de nouveau, les tables filles ne devraient (bizarrement) avoir aucune colonne, ou en tout cas pas de colonne identifiant puisque c'est celui de la table mère. Je ne sais pas si c'est faisable par contre de définir une table sans aucune colonne.

Regarde l'exemple de la doc de PostgreSQL avec les villes et les capitales et imagine que la table cities ait un identifiant serial. La table capitals ne changerait pas et hériterait de la colonne id comme elle hérite déjà des autres colonnes.

[MaitrePylos]

Ok, mais les id des tables filles ne pose pas de soucis, cela reste juste un identifiant de table et n’interagit pas, soit disons que je les supprimes?
Si vous regarder mon DDL, vous remarquerez que mes colonnes de tables filles sont en commentaires, mais quand j’exécute ce DDL , les colonnes de users, sont également créer dans les tables filles.
J'imagine que c'est le principe de

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

   INHERITS(public.users)

[CinePhil]

Voici un exemple que j'utilise d'héritage de données sans utiliser l'héritage de PostgreSQL, expliqué dans mon billet de blog sur les erreurs et exceptions dans les procédures SQL :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
-- Table: public.te_personne_prs
 
-- DROP TABLE public.te_personne_prs;
 
CREATE TABLE public.te_personne_prs
(
  prs_id integer NOT NULL DEFAULT nextval('te_personne_prs_prs_id_seq'::regclass), -- Identifiant de la personne
  prs_nom character varying(40) NOT NULL, -- Nom usuel de la personne
  prs_prenom character varying(40) NOT NULL DEFAULT ''::character varying, -- Prénom usuel de la personne
  prs_date_naissance date NOT NULL DEFAULT ('now'::text)::date,
  CONSTRAINT pk_prs_id PRIMARY KEY (prs_id),
  CONSTRAINT xu_prs_nom_prenom_naissance UNIQUE (prs_nom, prs_prenom, prs_date_naissance)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.te_personne_prs
  OWNER TO postgres;
COMMENT ON TABLE public.te_personne_prs
  IS 'Personnes physiques';
COMMENT ON COLUMN public.te_personne_prs.prs_id IS 'Identifiant de la personne';
COMMENT ON COLUMN public.te_personne_prs.prs_nom IS 'Nom usuel de la personne';
COMMENT ON COLUMN public.te_personne_prs.prs_prenom IS 'Prénom usuel de la personne';
 
-- Table: public.th_utilisateur_uti
 
-- DROP TABLE public.th_utilisateur_uti;
 
CREATE TABLE public.th_utilisateur_uti
(
  uti_id_personne integer NOT NULL, -- Identifiant de la personne
  uti_login character varying(40) NOT NULL, -- Nom d'utilisateur
  uti_mot_passe character varying(40) NOT NULL, -- Mot de passe de l'utilisateur
  uti_date_creation date NOT NULL DEFAULT ('now'::text)::date, -- Date de création de l'utilisateur
  uti_valide boolean NOT NULL DEFAULT false, -- L'utilisateur est-il valide ?
  CONSTRAINT pk_uti_id_personne PRIMARY KEY (uti_id_personne),
  CONSTRAINT fk_uti_id_personne FOREIGN KEY (uti_id_personne)
      REFERENCES public.te_personne_prs (prs_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE, -- Clé étrangère référençant te_personne_prs
  CONSTRAINT xu_uti_login UNIQUE (uti_login)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.th_utilisateur_uti
  OWNER TO postgres;
COMMENT ON TABLE public.th_utilisateur_uti
  IS 'Utilisateurs de l''application';
COMMENT ON COLUMN public.th_utilisateur_uti.uti_id_personne IS 'Identifiant de la personne';
COMMENT ON COLUMN public.th_utilisateur_uti.uti_login IS 'Nom d''utilisateur';
COMMENT ON COLUMN public.th_utilisateur_uti.uti_mot_passe IS 'Mot de passe de l''utilisateur';
COMMENT ON COLUMN public.th_utilisateur_uti.uti_date_creation IS 'Date de création de l''utilisateur';
COMMENT ON COLUMN public.th_utilisateur_uti.uti_valide IS 'L''utilisateur est-il valide ?';
 
COMMENT ON CONSTRAINT fk_uti_id_personne ON public.th_utilisateur_uti IS 'Clé étrangère référençant te_personne_prs';
 
-- Vue des utilisateurs avec leur nom et leur prénom
CREATE VIEW v_utilisateur AS
SELECT p.prs_id, p.prs_nom, p.prs_prenom, 
	u.uti_login
FROM th_utilisateur_uti u
INNER JOIN te_personne_prs p ON p.prs_id = u.uti_id_personne;
 
-- Procédure de création d'un utilisateur
CREATE OR REPLACE FUNCTION pi_ajout_utilisateur
(
	INOUT id_personne BIGINT, -- Identifiant de la personne
	-- Données pour la création éventuelle de la personne
	IN nom_usuel VARCHAR(40), -- Nom usuel de la personne
	IN prenom_usuel VARCHAR(40), -- Prénom usuel de la personne
	IN date_naissance DATE, -- Date de naissance de la personne
	-- Données pour la création de l'utilisateur
	IN login VARCHAR(40), -- Nom d'utilisateur
	IN mot_passe VARCHAR(40) -- Mot de passe de l'utilisateur (on suppose qu'il est crypté par l'application)
	-- Remarque : la date de création et la validité de l'utilisateur sont automatiques lors de sa création
)
AS
$BODY$
DECLARE msg_erreur CHARACTER VARYING DEFAULT ''; -- Message d'erreur éventuel
BEGIN
	-- Vérification de la date de naissance	
	IF date_naissance NOT BETWEEN CURRENT_DATE - INTERVAL '100' YEAR AND CURRENT_DATE - INTERVAL '10' YEAR THEN
		-- Si la date de naissance n'est pas comprise entre il y a 100 ans et il y a 10 ans => Erreur
		RAISE EXCEPTION 'Date de naissance incorrecte'
		USING ERRCODE = 'DVP01';
	END IF;	
 
	IF id_personne IS NULL THEN
		-- Identifiant de la personne non fourni : on crée d'abord la personne
		INSERT INTO te_personne_prs (prs_nom, prs_prenom, prs_date_naissance)
		VALUES (nom_usuel, prenom_usuel, date_naissance);
 
		-- Récupération du prs_id généré par l'auto-incrémentation
		SELECT CURRVAL('te_personne_prs_prs_id_seq') INTO id_personne;
	END IF;
 
	-- Création de l'utilisateur
	INSERT INTO th_utilisateur_uti (uti_id_personne, uti_login, uti_mot_passe, uti_date_creation, uti_valide)
	VALUES (id_personne, login, mot_passe, CURRENT_DATE, FALSE);
EXCEPTION 
    WHEN foreign_key_violation THEN
		-- En cas d'erreur de clé étrangère, on récupère l'erreur et on la personnalise
		GET STACKED DIAGNOSTICS msg_erreur = MESSAGE_TEXT; -- On récupère le message de l'erreur PostgreSQL
 
		IF POSITION('fk_uti_id_personne' IN msg_erreur) > 0 THEN
			-- Erreur de clé étrangère sur l'id_personne
			RAISE EXCEPTION 'Identifiant de la personne non trouvé' -- Notre texte d'erreur personnalisé
			USING ERRCODE = 'DEV01'; -- Notre code d'erreur personnalisé
		END IF;
    WHEN unique_violation THEN
		-- En cas de violation de contrainte d'unicité
		GET STACKED DIAGNOSTICS msg_erreur = MESSAGE_TEXT;
 
		IF POSITION('xu_uti_login' IN msg_erreur) > 0 THEN
			-- Violation de l'unicité du login
			RAISE EXCEPTION 'Login existant'  -- Notre texte d'erreur personnalisé
			USING ERRCODE = 'DEV02';  -- Notre code d'erreur personnalisé
		ELSEIF POSITION('xu_prs_nom_prenom_naissance' IN msg_erreur) > 0 THEN
            -- Violation de l'unicité du triplet {prs_nom, psr_prenom, psr_date_naissance}
			RAISE EXCEPTION 'Une personne portant ce nom, ce prénom et née ce jour existe déjà.' -- Notre texte d'erreur personnalisé
			USING ERRCODE = 'DEV03'; -- Notre code d'erreur personnalisé
		END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

Quand tu écris ça :

Cela fonctionne comme voulu, sauf que si je crée un prof ok, c'est bien hérité dans users, par contre si je crée le même users dans eleves, alors il se duplique dans users avec la même PK dans users ????

Tu veux dire que tu à fait des requêtes INSERT INTO eleves et INSERT INTO profs ?

[MaitrePylos]

Tu veux dire que tu à fait des requêtes INSERT INTO eleves et INSERT INTO profs ?

Exactement.
Donc si je lis ton propre code je dois faire une procédure de validation ?

[CinePhil]

C'était juste une suggestion, une présentation de ce que j'ai publié et qui montre comment je procède sans la fonction d'héritage de PostgreSQL.

Revenons à ton cas...

Cela fonctionne comme voulu, sauf que si je crée un prof ok, c'est bien hérité dans users, par contre si je crée le même users dans eleves, alors il se duplique dans users avec la même PK dans users ????

Comment le SGBD sait que c'est le même user ?

Tu peux donner les deux requêtes d'insertion que tu as faites ?

[MaitrePylos]

En fait j'ai bêtement fais un truc à la main.

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

insert into professeur (nom,prenom,matricule) values ('Ernaelsten','Gérard',1254)

Ceci insert la ligne dans la table professeur et users

ensuite, comme je fais un select dans la table users, j'ai récupéré les infos.

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

insert into eleves (id_user,nom,prenom,matricule) values (1,'Ernaelsten','Gérard',1254)

J' espérait bêtement, que lors de la deuxième insertion, il n'y aurait pas de doublon dans users.

[CinePhil]

Donc PostgreSQL ne tient pas compte de la valeur donnée à id_users et ne reconnaît pas qu'il s'agit du même user.

Insertion => serial => nouvel identifiant et nouvel user !

Quand je lis ça, je me dis que j'ai raison de ne pas utiliser l'héritage de PostgreSQL !
Mais ça ne va pas beaucoup t'aier !

[fsmrel]

On voit que j'ai deux fois id_user : 4
Pouvez-vous me donner une piste de là où je me trompe ?

Ça n’est pas vous qui vous vous trompez, mais en l’occurrence PostgreSQL est coupable de ne pas vérifier systématiquement l’unicité des clés. Ça paraît énorme, mais le constat est là.

Je vous renvoie à la doc officielle (V10), au paragraphe 5.9.1 où on lit que l’héritage de tables (propre à PostgreSQL et non conforme à la norme SQL, ce qui vous lie indéfectiblement à PostgreSQL ) pose quelques problèmes, dont celui que vous évoquez...

Appréciez la conclusion du paragraphe en question :

5.9.1. Caveats [...]

These deficiencies will probably be fixed in some future release, but in the meantime considerable care is needed in deciding whether inheritance is useful for your application.

Quoi qu’il en soit, pour arriver à ne pas avoir de doublons avec la clé primaire, il faut d’abord, comme vous l’avez fait, utiliser le type SERIAL pour la colonne id_user.

Ensuite, dans les inserts des tables dépendantes, il faut nommer toutes les colonnes de la table UTILISATEUR, à l’exception justement de id_user.


Exemple de script en ce sens :

SET SCHEMA 'temp' ;

BEGIN TRANSACTION ;

DROP TABLE IF EXISTS UTILISATEUR CASCADE ;


CREATE TABLE UTILISATEUR
(
        UserId           SERIAL        NOT NULL
      , UserNom          VARCHAR(32)   NOT NULL
      , UserPrenom       VARCHAR(32)   NOT NULL
      , UserMatricule    INT           NOT NULL	  
    , CONSTRAINT UTILISATEUR_PK PRIMARY KEY (UserId)
    , CONSTRAINT UTILISATEUR_MATRICULE_AK UNIQUE (UserMatricule)    
);

CREATE TABLE PROFESSEUR
(
) INHERITS (UTILISATEUR) ;

CREATE TABLE ELEVE
(
) INHERITS (UTILISATEUR) ;

INSERT INTO PROFESSEUR (UserNom, UserPrenom, UserMatricule) VALUES ('Tournesol', 'Tryphon', 1234) ;

INSERT INTO ELEVE (UserNom, UserPrenom, UserMatricule) VALUES ('Dupont', 'Pierre', 1234) ;
INSERT INTO ELEVE (UserNom, UserPrenom, UserMatricule) VALUES ('Durand', 'Marc', 55) ;

ROLLBACK ;

SELECT * FROM UTILISATEUR ;

Au résultat :

Du fait de SERIAL, PostgreSQL a bien calculé tout seul les valeurs de la colonne UserId, sans qu’on s’en mêle.


Mais il reste un gros trou dans la raquette : j’ai défini une contrainte d’unicité pour la colonne UserMatricule de la table UTILISATEUR, mais PostgreSQL la dédaigne : il laisse passer le fait que Tryphon et Pierre ont à tort le même matricule.

Conclusion : faire comme CinePhil, éviter l’héritage de tables de PostgreSQL.

[MaitrePylos]

Merci de vos reflexions, j'avais également tenté de mettre une contrainte sur matricule, mais rien n'y fait.
Après avoir lu la doc, cela ne sert à rien en PostgreSQL (je sais pas si d'autres db le gère d'ailleurs).

mais je peux aussi bien alimentr la table user, que eleve ou autre.
du coup si je veux savoir qui est de la table user, je dois utiliser le mot clé only

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
select * from only users;

et si je veux la table eleve , je dois faire une requete du genre

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
SELECT p.relname, u.nom, u.matricule
FROM users u, pg_class p
WHERE v.tableoid = p.oid
AND p.realname = 'eleve';

Pour arriver à ça autant faire une relation 1-n de users vers les tables pseudo hérité du coup.
Encore un merci de votre aide.

[SQLpro]

Après avoir lu la doc, cela ne sert à rien en PostgreSQL (je sais pas si d'autres db le gère d'ailleurs).

Effectivement l'héritage à la sauce PG est une spécialité qui ne sert à rien, sauf à générer des erreurs. Aucun autre SGBDR ne propose un tel mécanisme absurde.

L'héritage dans un modèle est connu dès l'arrivée de MERISE 2 dans les années 80 et représentable par tous les SGBDR, sans aucun mécanisme autre que des tables et des contraintes.

Lisez l'article que j'ai écrit à ce sujet :
https://sqlpro.developpez.com/cours/...tion/heritage/

A +

[CinePhil]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
FROM users u, pg_class p
WHERE v.tableoid = p.oid

Beurk !
Les jointures s'écrivent depuis 1992 avec l'opérateur JOIN ; il serait temps de s'y mettre !

En plus il y a erreur d'alias !