Héritage et utilisation de la pk du parent comme une fk

Version imprimable

05/02/2019, 15h52
cryptom

3 pièce(s) jointe(s)

Héritage et utilisation de la pk du parent comme une fk

Bonjour,
J'ai mis en place un héritage dans la ma base, concrètement le voici. Je la manipule avec pgadmin
la table parents s'appelle "entité", elle ne possède qu'une colonne pk "id_entité" de type serial.
la table enfant s'appelle "agent", elle hérite de la table parent et reprend donc le "id_entité", je lui ai toutefois ajouté une colonne pk "id_agent" de type serial, puis des colonnes "nom", "prénom", etc...

Dans une troisième table "intervention", qui prend "id_entite" de la table "entite" comme clé étrangère (fk).

Je crée plusieurs agents qui prennent comme pk 1,2,3,4. Automatiquement des pk sont générés sur la table parent également, comme elle est vierge il s'agit aussi de 1,2,3,4

ci-dessous la table agent
Pièce jointe 447547

Lorsque je veux utiliser 1,2 ou 3 comme fk dans la table "intervention", faisant donc référence au "id_entite" de la table "entite", il me dit que la clé n'existe pas, en gros que ni 1, ni 2 ni 3 n'existent dans la table 'entite'.

ci-dessous le refus d'insertion sur intervention
Pièce jointe 447553

Et effectivement alors que pgadmin me les affiche lorsque je fais un select, je peux recréer les entrées 1,2,3 ou 4 dans 'entite" sans que cela ne lui pose de problème.

ci-dessous la table entite avec des doublons sur la pk qui ne le dérange pas...
Pièce jointe 447549

J'ai forcément raté quelque chose sur l'héritage, mais quoi ? Si vous pouviez m'éclairer cela m'aiderait beaucoup. Merci d'avance.
05/02/2019, 15h58
MaitrePylos

Bonjour,
Le message dit que entite_id = 4 n'existe pas dans la table entite, correcte car dans cette table c'est id_entite.
05/02/2019, 16h21
cryptom

2 pièce(s) jointe(s)

elle s'appelle bien entite_id dans ma table intervention, la contrainte sur la clé étrangère est definie comme suit :
Pièce jointe 447561

Il ne semble pas que l'on soit obligé de donner le même nom, si ? Je n'ai jamais eu de souci...

J'ai refait un test en nommant ma colonne id_entite (ci-dessous), même problèmePièce jointe 447563

Bizarre, je ne vois pas bien vous pouvez essayer ceci pour être sur

Code:

1
2
3
4
5
 
 
ALTER TABLE public.intervention2 DROP CONSTRAINT intervention_entite_id_fkey;
 
ALTER TABLE public.intervention2  ADD CONSTRAINT intervention_entite_id_fkey FOREIGN KEY (entite_id) REFERENCES entite(id_entite) MATCH FULL ON UPDATE CASCADE ON DELETE SET NULL

05/02/2019, 17h01
cryptom

2 pièce(s) jointe(s)

Merci pour ces conseils... ça ne fonctionne toutefois toujours pas.
J'ai supprimé la clé étrangère, puis recréé avec vos commandes sur la table intervention2
Pièce jointe 447615

Par contre, c'est bien un problème avec l'héritage, peut-être tout bête parce que je n'ai pas l'habitude.
J'ai créé une table agent2, sans héritage, et là les insertions fonctionnent sans problème.
Pièce jointe 447616

Ma colonne fk est au format integer et je lui applique la contrainte indiquée plus haut.
Est-ce que les pk d'une table parent seraient d'un autre format ? Ce qui expliquerait que je puisse recréer la même entrée sans qu'il ne me fasse d'erreur, l'une serait en integer (celle que j'ajoute manuellement) et l'autre j'en sais rien un autre format ?
05/02/2019, 21h48
cryptom

Je pense que c'est lié à la question de l'indexation, et du fait que la clé primaire n'est pas indexable sur plusieurs tables, c'est à dire parent et enfants (j'en conclue qu'elle n'est pas indexée du tout sur un parent ?).

Du moins, c'est ce que je comprends de la doc et de mes recherches sur des sites en anglais.

Pour le moment, je retire donc mes contraintes, cela me gène un peu, mais comme je maîtriserai la programmation qui se fera sur cette BDD, je pourrai m'assurer que les contraintes soient respectées.

Toutefois, si quelqu'un trouve une meilleure réponse je reste preneur.
06/02/2019, 08h23
MaitrePylos

Peux-t-on avoir le DDL des tables et contraintes ?

Je n'ai pas tous les éléments de langage, mais ce sont les scripts SQL générés par pgadmin, que vous voulez ?

Pour la table 'entite' :

Code:

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE public.entite
(
    id_entite serial NOT NULL,
    PRIMARY KEY (id_entite)
)
WITH (
    OIDS = FALSE
);
 
ALTER TABLE public.entite
    OWNER to fred;

La table 'agent' :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE public.agent
(
    id_agent serial NOT NULL,
    nom_agent character varying(250),
    prenom_agent character varying(250),
    PRIMARY KEY (id_agent)
)
    INHERITS (public.entite)
WITH (
    OIDS = FALSE
);
 
ALTER TABLE public.agent
    OWNER to fred;

Et la table 'intervention' avec la clé étrangère :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE public.intervention
(
    id_intervention serial NOT NULL,
    entite_id integer,
    PRIMARY KEY (id_intervention),
    FOREIGN KEY (entite_id)
        REFERENCES public.entite (id_entite) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
WITH (
    OIDS = FALSE
);
 
ALTER TABLE public.intervention
    OWNER to fred;

Insertion de 3 noms pour le test :

Code:

1
2
3
INSERT INTO public.agent (nom_agent) VALUES('bob');
INSERT INTO public.agent (nom_agent) VALUES('billy');
INSERT INTO public.agent (nom_agent) VALUES('joe');

Tentative d'insertion dans 'intervention' avec l'erreur indiquée :

Code:

INSERT INTO public.intervention (entite_id) VALUES(1);

12/02/2019, 11h59
cryptom

Bonjour,
je fais juste remonter, même si je suis parti sur une autre manière de procéder.

Mais je me demande :
- soit je fais une mauvaise manip, mais là c'est quand même assez basique comme structure de table, à part l'héritage;
- soit c'est une impossibilité avec postgresql;
- soit, je suis le seul à me poser la question, ce qui signifierait que je suis plutôt en train de faire une grosse erreur de modélisation...

Dans le troisième cas, est-ce que c'est quelque chose qui ne se fait pas, à savoir de faire pointer une fk vers la pk d'une table parent ? Autant que je corrige mon raisonnement, s'il est erroné.

Merci déjà pour les premières réponses.
12/02/2019, 14h22
skuatamad

Citation:

Dans le troisième cas, est-ce que c'est quelque chose qui ne se fait pas, à savoir de faire pointer une fk vers la pk d'une table parent ? Autant que je corrige mon raisonnement, s'il est erroné.

Si, ça c'est ce que tout le monde fait.

Citation:

Tentative d'insertion dans 'intervention' avec l'erreur indiquée :

A quel moment la table public.entite est elle alimentée ?
12/02/2019, 21h10
cryptom

1 pièce(s) jointe(s)

Citation:

Si, ça c'est ce que tout le monde fait.

Donc ça doit être une erreur vraiment de base, désolé d'avance.
C'est la première fois que j'utilise de l'héritage. Je laisse toutes les options par défaut de pgadmin et je fais juste les commandes (DDL) (enfin pgadmin le fait pour moi) indiquées plus haut.

Citation:

A quel moment la table public.entite est elle alimentée ?

Je ne fais pas d'insertion directe dans la table entité, elle ne contient qu'un identifiant 'id_entite' (en tout cas pour le moment).
Elle s'alimente lors d'insertions d'éléments dans la table enfant 'agent' -> la colonne 'id_entite' de 'entite' s'incrémente automatiquement.

Mais comme indiqué plus haut, si après l'auto-incrémentation j'essaie d'insérer un 'id_entite' qui existe déjà, alors que c'est la pk (donc unique), je n'ai pas de message d'erreur... Je peux faire des doublons (pas de triplets par contre). Comme si la valeur pk générée automatiquement était différente d'une valeur fournit manuellement (pas d'espace avant ou après, et juste de l'integer)...
voir image ci-dessous :
Pièce jointe 449588

Citation:

Envoyé par cryptom

Donc ça doit être une erreur vraiment de base, désolé d'avance.
C'est la première fois que j'utilise de l'héritage. Je laisse toutes les options par défaut de pgadmin et je fais juste les commandes (DDL) (enfin pgadmin le fait pour moi) indiquées plus haut.

C'est plutôt moi qui avait mal compris votre notion de table parent.

Bon je ne connais pas trop postgre, je n'ai donc jamais utilisé leur INHERIT.

Dans la doc sur l'héritage on peut lire :

Citation:

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 notre exemple, l'instruction INSERT suivante échouera :

Et en reprenant votre jeu de test :
Code:

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 test=# CREATE TABLE entite test-# ( test(# id_entite serial NOT NULL, test(# PRIMARY KEY (id_entite) test(# ) test-# WITH ( test(# OIDS = FALSE test(# ); CREATE TABLE test=# test=# CREATE TABLE agent test-# ( test(# id_agent serial NOT NULL, test(# nom_agent character varying(250), test(# prenom_agent character varying(250), test(# PRIMARY KEY (id_agent) test(# ) test-# INHERITS (entite) test-# WITH ( test(# OIDS = FALSE test(# ); CREATE TABLE test=# test=# CREATE TABLE intervention test-# ( test(# id_intervention serial NOT NULL, test(# entite_id integer, test(# PRIMARY KEY (id_intervention), test(# FOREIGN KEY (entite_id) test(# REFERENCES entite (id_entite) MATCH SIMPLE test(# ON UPDATE NO ACTION test(# ON DELETE NO ACTION test(# ) test-# WITH ( test(# OIDS = FALSE test(# ); CREATE TABLE test=# test=# INSERT INTO agent (nom_agent) VALUES('bob'); INSERT 0 1 test=# INSERT INTO agent (nom_agent) VALUES('billy'); INSERT 0 1 test=# INSERT INTO agent (nom_agent) VALUES('joe'); INSERT 0 1 test=# test=# INSERT INTO intervention (entite_id) VALUES(1); ERREUR: une instruction insert ou update sur la table « intervention » viole la contrainte de clé étrangère « intervention_entite_id_fkey » DÉTAIL : La clé (entite_id)=(1) n'est pas présente dans la table « entite ». test=# test=#
On constate que la table entite n'est pas alimentée, ce qui correspond bien à ce que dit la doc :
Code:

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 test=# select * from only entite; id_entite ----------- (0 ligne) test=# select * from only agent; id_entite | id_agent | nom_agent | prenom_agent -----------+----------+-----------+-------------- 1 | 1 | bob | 2 | 2 | billy | 3 | 3 | joe | (3 lignes) test=# test=# insert into entite values(1); INSERT 0 1 test=# select * from entite; id_entite ----------- 1 1 2 3 (4 lignes) test=# select * from only entite; id_entite ----------- 1 (1 ligne) test=# INSERT INTO intervention (entite_id) VALUES(1); INSERT 0 1 test=# select * from intervention; id_intervention | entite_id -----------------+----------- 2 | 1 (1 ligne) test=#
Je ne sais donc pas exactement quelle est la plus-value de INHERIT, mais vous devez insérer explicitement des données dans la table ENTITE pour pouvoir les référencer dans INTERVENTION.
Si quelqu'un connait bien la fonctionnalité INHERIT, peut être qu'il pourra vous donner une info supplémentaire sur comment l'utiliser, en l'état, moi je ne suis pas convaincue de la plus-value de INHERIT dans votre cas.