Discussion :

[contremaitre]

Bonjour

Soit trois tables A B C qui héritent d'une table X (avec exclusion mutuelle)

D'après ce que j'ai compris, lorsque je veux créer un enregistrement dans A il faut d'abord que je crée un enregistrement dans X puis ensuite dans A avec le même id. C'est bien ça ?

ensuite, le nombre de tables X sera très important et j'aurai assez souvent à faire une requete pour obtenir toutes les tables X et afficher les atrtibuts qui seront stockés dans A B ou C.
Est ce que je peux faire ça en une seule requete, ou est ce que je devrai faire une requete pour chaque élément de X pour aller cherche la table fille ?
Si oui comment ?
Et est ce que ca sera efficace? Est ce que ajouter un champ type dans X pour dire si X est de type A B ou C peut aider ?

merci

[Chtulus]

Bonjour,

SQLpro a écrit de très belle choses la-dessus :
http://sqlpro.developpez.com/cours/m...tion/heritage/



Bon courage !

[contremaitre]

merci mais je l'ai déjà lu avec attention.
Cet article ne traite pas des questions que j'ai posé à savoir :
* efficacité des requetes
* comment récuperer tous les enregistrement d'un coup en ayant à la fois les champs de la table mere et fille. (il y a un exemple avec l'héritage conditionnel avec vue mais pas pour les autres cas).

[fsmrel]

Bonsoir pasdeface,

Soit trois tables A B C qui héritent d'une table X (avec exclusion mutuelle)
D'après ce que j'ai compris, lorsque je veux créer un enregistrement dans A il faut d'abord que je crée un enregistrement dans X puis ensuite dans A avec le même id. C'est bien ça ?

C’est une solution, mais vous pouvez tout faire d’un coup par le truchement des vues.

Je reprends un exemple de spécialisation présenté par skalpFR (la discussion a malheureusement disparu).
skalpFR présentait les tables suivantes, qui parlent d’elles-mêmes :

ANIMAL
NameId
Age
TypeAnimal

ELEPHANT
NameId
tuskSize

DOG
NameId
toothSize

Je lui avais proposé le jeu d’instructions suivant (SQL Server 2005) :

1) Création des tables de base :

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
Create Table Animal (
     AnimalId      INTEGER       NOT NULL
  ,  Age           INTEGER       NOT NULL
 , CONSTRAINT AnimalPK PRIMARY KEY (AnimalId) ;
 
CREATE TABLE Elephant (
     AnimalId      INTEGER     NOT NULL
  ,  tuskSize      INTEGER     NOT NULL
 , CONSTRAINT ElephantPK PRIMARY KEY (AnimalId)
 , CONSTRAINT ElephantFK1 FOREIGN KEY (AnimalId) REFERENCES Animal
        ON DELETE CASCADE
        ON UPDATE NO ACTION) ;
 
CREATE TABLE Dog (
     AnimalId    INTEGER      NOT NULL
  ,  toothSize   INTEGER      NOT NULL
  ,  ToutouNom   VARCHAR(32)  NOT NULL
  ,  Proprio     VARCHAR(32)  NOT NULL
 , CONSTRAINT DogPK PRIMARY KEY (AnimalId)
 , CONSTRAINT DogAK1 UNIQUE (ToutouNom)
 , CONSTRAINT DogFK1 FOREIGN KEY (AnimalId) REFERENCES Animal
        ON DELETE CASCADE  
        ON UPDATE NO ACTION) ;
GO

2) Création des vues suivantes :

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE VIEW Elephantidae AS
  SELECT x.AnimalId, x.Age, y.tuskSize
  FROM   Animal AS x INNER JOIN Elephant AS y ON x.AnimalId = y.AnimalId ;
GO
CREATE VIEW Toutou AS
  SELECT x.AnimalId, x.Age, y.toothSize, y.ToutouNom, y.Proprio
  FROM   Animal AS x INNER JOIN Dog AS y ON x.AnimalId = y.AnimalId ;
GO

Ces vues permettent d’éviter de programmer un insert dans la table Animal, suivi d’un insert dans la table Dog quand il s’agit de créer un toutou. On peut se contenter d’utiliser la vue ad-hoc et n'effectuer qu'un seul insert, à charge du SGBD de ventiler les données dans les deux tables de base. En ce sens, il faut noter que selon la théorie relationnelle, la mise à jour d’une vue de jointure est possible. La norme SQL a suivi. Certains SGBD (Oracle par exemple) le permettent aussi (avec certaines réserves) et d’autres s’y refusent (SQL Server 2005 par exemple), au prétexte que l’on peut malgré tout rendre transparentes les mises à jour par le biais de triggers (c’est fastidieux, mais on s’en sort...)

3) Définition des triggers ad-hoc si le SGBD ne permet pas la mise à jour des vues de jointure (ex. : SQL Server 2005, code améliorable) :

Code SQL :

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

1
2
3
4
5
6
7
/* --------------------------------------------------------------
   Ventilation des opérations de type INSERT pour la vue Toutou
*/ --------------------------------------------------------------
Create Trigger ToutouInsert On Toutou Instead Of Insert As
  Insert Into Animal Select AnimalId, Age From Inserted ; 
  Insert Into Dog Select AnimalId, toothSize, ToutouNom, Proprio From Inserted ;
GO

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
/* --------------------------------------------------------------
   Ventilation des opérations de type UPDATE pour la vue Toutou
*/ --------------------------------------------------------------
Create trigger ToutouUpdate On Toutou Instead Of Update As
IF Update(AnimalId)
     Raiserror ('Modification interdite de la clé primaire',10,1) 
  IF Update(Age)
     Update Animal Set Age = (Select Distinct Age 
                              From   Inserted)
                   Where Exists (Select x.Age 
                                 From   Deleted x 
                                 Where  x.AnimalId = Animal.AnimalId) ;
  IF Update(toothSize)
     Update Dog Set toothSize = (Select Distinct toothSize 
                                 From   Inserted)
                Where Exists (Select x.toothSize  
                              From   Deleted x  
                              Where  x.AnimalId = Dog.AnimalId) ;
  IF Update(ToutouNom)
     Update Dog Set ToutouNom = (Select Distinct ToutouNom 
                                 From   Inserted)
                Where Exists (Select x.ToutouNom  
                              From   Deleted x  
                              Where  x.AnimalId = Dog.AnimalId) ;
  IF Update(Proprio)
     Update Dog Set Proprio = (Select Distinct Proprio 
                               From   Inserted)
               Where Exists (Select x.Proprio  
                             From   Deleted x  
                             Where  x.AnimalId = Dog.AnimalId) ;
GO

Code SQL :

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

1
2
3
4
5
6
7
8
9
/* --------------------------------------------------------------
   Ventilation des opérations de type DELETE pour la vue Toutou
*/ --------------------------------------------------------------
 
Create Trigger ToutouDelete On Toutou Instead Of Delete As
       Delete  From Animal 
               Where Exists (Select * From Deleted x 
                             Where  Animal.AnimalId = x.AnimalId)  ;
GO

4) Pour créer des toutous :

Code SQL :

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

1
2
3
INSERT INTO Toutou VALUES (1, 5, 15, 'Fido', 'Léo') ;
INSERT INTO Toutou VALUES (2, 9, 17, 'Foulcan', 'Léo') ;
INSERT INTO Toutou VALUES (3, 5, 15, 'Bobby', 'Léo') ;

5) Pour voir tous les toutous de Léo :

Code SQL :

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

1
2
3
SELECT Age, toothSize, ToutouNom
From   Toutou 
Where  Proprio = 'Léo'

6) Pour modifier l’âge et le propriétaire d’un toutou :

Code SQL :

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

1
2
3
Update Toutou
   set  Age = 7, Proprio = 'Léa' 
   where ToutouNom = 'Fido'

7) Pour supprimer tous les toutous de Léo

Code SQL :

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

1
2
Delete FROM Toutou 
   WHERE proprio = 'Léo'

Etc.

Observations :
L’attribut TypeAnimal n’est pas nécessaire, dans la mesure où l’on connaît la vue que l’on va utiliser.

En complément, on peut juger prudent de n’autoriser les utilisateurs qu’à manipuler les vues :

Code SQL :

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

1
2
3
4
5
6
7
 
GRANT INSERT, UPDATE, DELETE ON Toutou TO Public ;
GRANT INSERT, UPDATE, DELETE ON Elephantidae TO Public ;
 
REVOKE INSERT, UPDATE, DELETE ON Animal FROM Public ; 
REVOKE INSERT, UPDATE, DELETE ON Dog FROM Public ; 
REVOKE INSERT, UPDATE, DELETE ON Elephant FROM Public ;

Ce mécanisme d’habilitation forçant à n’utiliser que les vues Toutou et Elephantidae, permet d’éviter des créations incomplètes (par exemple, INSERT d’un toutou dans la table Animal, non suivi d’un INSERT dans la table Dog). Cela permet aussi d’éviter qu’un animal soit hybride, mi-toutou, mi-éléphant (exclusion mutuelle). S'il n'en était pas ainsi, seul serait responsable celui qui gère les habilitations. Cela dit, dans la série ceinture, bretelles et épingle à nourrice, l'exclusion mutuelle peut être assurée par des triggers.

[contremaitre]

Super exemple ! merci beaucoup.

Pour créer un animal/toutou mais sans spécifier d'ID, je suppose qu'il faut récuperer l'id inséré automatiquement dans animal dans le premier insert du trigger c'est ca ?

Et il y a une requete que je ne sais pas faire :
Sélectionner tous les animaux avec tous leurs attributs (quelque soit le nombre de type d'animaux différents).
Est ce que c'est possible ?

Par exemple quelque chose qui ressemble à ça ?

Code :

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

1
2
3
4
5
6
7
8
 
CREATE VIEW Animaux AS
  SELECT *
  FROM   Animal AS x 
	JOIN Elephant AS y ON x.AnimalId = y.AnimalId 
	JOIN Dog      As z ON x.AnimalId = z.AnimalId
	...
GO

[al1_24]

Il faut utiliser des jointures externes !

Code :

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

1
2
3
4
5
6
7
 
CREATE VIEW Animaux AS
  SELECT *
  FROM   Animal AS x 
    LEFT JOIN Elephant AS y ON x.AnimalId = y.AnimalId 
    LEFT JOIN Dog      As z ON x.AnimalId = z.AnimalId
    ...

[contremaitre]

Merci ça marche bien !

Il me reste deux choses à savoir :
est ce que le principe reste le même en cas d'héritage sur plusieurs niveaux (on crée des vues de vues pour chaque niveau ? )

Pour utiliser l'autoincrémentation des clé primaires, j'utilise une fonction qui récupere la dernière clé inseré (last_insert_rowid() en sqlite3) pour la réutiliser dans la table fille lors du second INSERT du trigger.
Est ce que c'est la bonne manière de faire?

[Chtulus]

Bonjour,

est ce que le principe reste le même en cas d'héritage sur plusieurs niveaux (on crée des vues de vues pour chaque niveau ? )

Il est possible de créer des vues à partir d'autre vues, ce n'est pas vraiment recommandé malgrés tout à cause des faibles performances que cela peut engendrer. Par contre tu peux utiliser des tables sous-jacentes.

[fsmrel]

Pour créer un animal/toutou mais sans spécifier d'ID, je suppose qu'il faut récuperer l'id inséré automatiquement dans animal dans le premier insert du trigger c'est ca ?

C’est quelque chose comme ça.

Pour en revenir à SQL Server, l’instruction permettant de définir la table Animal est à aménager pour permettre au SGBD d’incrémenter automatiquement les valeurs de la clé primaire {AnimalId}, au moyen de la clause IDENTITY :

Code SQL :

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

1
2
3
4
5
Create Table Animal (
     AnimalId      INTEGER IDENTITY   not null
  ,  Age           INTEGER   not null
 , Constraint AnimalPK Primary key (AnimalId)
) ;

Le trigger d’insertion d’un toutou peut devenir le suivant :

Code SQL :

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

1
2
3
4
5
6
7
8
Create Trigger ToutouInsert On Toutou Instead Of Insert As
  Insert Into Animal Select Age From Inserted ;
 
  Insert into Dog Select T.AnimalId, I.toothSize, I.ToutouNom, I.Proprio 
                  From  (Select Max(AnimalId) As AnimalId 
                         From   Animal) As T
                       , Inserted As I
GO

C'est-à-dire que dans un premier temps, le SGBD insère une ligne dans la table Animal, tout en attribuant une valeur à la clé primaire (par incrémentation automatique).
Puis dans un deuxième temps, on récupère cette valeur au moyen de la fonction MAX : le SGBD produit en l’occurrence une table T comportant une seule ligne et une seule colonne que l’on tricote (produit cartésien) avec la table Inserted.

Petit bémol : dans la mesure ou la vue Toutou comporte l’attribut AnimalId, l’instruction INSERT doit prévoir de fournir une valeur pour cet attribut, bien que l’on ne s’en serve pas au niveau du trigger et qu'elle passe à la trappe. Ici j’ai systématiquement utilisé la valeur 0 :

Code SQL :

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

1
2
3
  INSERT INTO Toutou VALUES (0, 5, 15, 'Fido', 'Léo') ;
  INSERT INTO Toutou VALUES (0, 9, 17, 'Foulcan', 'Léo') ;
  INSERT INTO Toutou VALUES (0, 5, 15, 'Bobby', 'Léo') ;

Au résultat :

Code :

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

1
2
3
4
5
6
7
Select * from Toutou ;
 
=>
   AnimalId   Age   toothSize   ToutouNom   Proprio
      1        5       15       Fido        Léo
      2        9       17       Foulcan     Léo
      3        5       15       Bobby       Léo

Je ne doute pas que les cracks de SQL sauront améliorer cette approche.

[fsmrel]

Est ce que le principe reste le même en cas d'héritage sur plusieurs niveaux (on crée des vues de vues pour chaque niveau ?)

Pourquoi pas ? Il suffit de compléter le jeu d’instructions figurant dans mon message du 27 juin.
Exemple :

1) On ajoute la définition d’une table DogBoxer, pour gérer le boxer, spécialisation du chien :

Code SQL :

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

1
2
3
4
5
6
7
8
Create Table DogBoxer (
     AnimalId    INTEGER    not null
  ,  Robe        CHAR(12)   not null
, Constraint BoxerPK Primary key (AnimalId)
, Constraint BoxerFK1 Foreign key (AnimalId) References Dog
        On delete Cascade
        On update No action
) ;

2) On ajoute la définition d’une vue Boxer :

Code SQL :

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

1
2
3
4
Create View Boxer As
  Select x.AnimalId, x.Age, x.toothSize, x.ToutouNom, x.Proprio, y.Robe 
  From   Toutou As x INNER JOIN DogBoxer As y 
         ON x.AnimalId = y.AnimalId;

3) On ajoute la définition d’un trigger pour INSERT, mettant en jeu les vues Toutou et Boxer, ainsi que la table DogBoxer :

Code SQL :

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

1
2
3
4
5
Create Trigger BoxerInsert On Boxer Instead Of Insert As
  Insert Into Toutou Select AnimalId, Age, toothSize, ToutouNom, Proprio  
                     From Inserted ; 
  Insert Into DogBoxer Select AnimalId, Robe 
                     From Inserted ;

4) On ajoute la définition d’un trigger pour UPDATE, mettant en jeu les vues Toutou et Boxer, ainsi que la table DogBoxer :

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
Create trigger ToutouUpdate On Toutou Instead Of Update As
IF Update(AnimalId)
        Raiserror ('Modification interdite de la clé primaire',10,1) 
IF Update(Age)
     Update Toutou Set Age = (Select Distinct Age 
                              From   Inserted)
                   Where Exists (Select x.Age 
                                 From   Deleted x 
                                 Where  x.AnimalId = Toutou.AnimalId) ;
IF Update(toothSize)
     Update Toutou Set toothSize = (Select Distinct toothSize 
                                    From   Inserted)
                Where Exists (Select x.toothSize  
                              From   Deleted x  
                              Where  x.AnimalId = Toutou.AnimalId) ;
IF Update(ToutouNom)
          Update Toutou Set ToutouNom = (Select Distinct ToutouNom 
                                         From   Inserted)
               Where Exists (Select x.ToutouNom  
                             From   Deleted x  
                             Where  x.AnimalId = Toutou.AnimalId) ;
IF Update(Proprio)
          Update Toutou Set Proprio = (Select Distinct Proprio 
                                       From   Inserted)
               Where Exists (Select x.Proprio  
                             From   Deleted x  
                             Where  x.AnimalId = Toutou.AnimalId) ;
IF Update(Robe)
          Update DogBoxer Set Robe = (Select Distinct Robe 
                                         From   Inserted)
               Where Exists (Select x.Robe  
                             From   Deleted x  
                             Where x.AnimalId = DogBoxer.AnimalId) ;

5) On ajoute la définition d’un trigger pour DELETE, mettant en jeu les vues Toutou et Boxer :

Code SQL :

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

1
2
3
4
5
Create Trigger BoxerDelete On Boxer Instead Of Delete As
      Delete  From Toutou 
              Where Exists (Select * 
                            From   Deleted x 
                            Where  Toutou.AnimalId = x.AnimalId) ;

6) Création de boxers :

Code SQL :

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

1
2
3
4
INSERT INTO Boxer VALUES (1, 5, 15, 'Fido', 'Luc', 'bizarre') ;
INSERT INTO Boxer VALUES (2, 9, 17, 'Foulcan', 'Léa', 'fauve') ;
INSERT INTO Boxer VALUES (3, 7, 15, 'Bobby', 'Léo', 'fauve') ;
INSERT Into Boxer Values (4, 5, 15, 'Bobette', 'Léa', 'bringé') ;

7) Update :

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
Update Boxer
   Set Age = 6
   WHERE Age = 5 ;
 
Update Boxer
   Set Robe = 'fauve'
   WHERE ToutouNom = 'Bobette' ;
 
Update Boxer
   Set ToutouNom = 'ViensIci'
   WHERE ToutouNom = 'Foulcan' ;
 
Update Boxer
   Set Robe = 'fauve'
   WHERE ToutouNom = 'Bobette' ;

8) Delete :

Code SQL :

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

1
2
Delete FROM Boxer 
   WHERE proprio = 'Léo' ;

Le but de la manoeuvre est de montrer que, dans la codification des triggers d'insert et d'update, on peut se dispenser de citer les tables de base autres que celle du dernier niveau (DogBoxer), ce qui permet de garantir une bonne indépendance logique.
Maintenant, si les tests de performance montrent qu'il est préférable de citer les tables plutôt que les vues, les changements à apporter dans les triggers sont extrêmement simples.

[contremaitre]

merci encore pour ces explications bien détaillées.
Il ne me reste plus qu'à essayer tout ça !

[contremaitre]

Je n'arrive pas à interoger plusieurs sous-tables à la fois (mais pas toutes).
Exemple je veux connaitre tous les "Elephant" et "Dog" mais pas les "Cat"

Avec les jointures externes comme ici :

Code :

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

1
2
3
4
  SELECT *
  FROM   Animal AS x 
    LEFT JOIN Elephant AS y ON x.AnimalId = y.AnimalId 
    LEFT JOIN Dog      AS z ON x.AnimalId = z.AnimalId

Le problème c'est qu'il sort tous les animaux (y compris les "Cat") mais sans les attributs des "Cat" vu qu'ils ne font pas partis de la jointure.
Comment faire pour qu'il ne joigne pas les tables "Cat" dans cette requete ?
J'ai passé en revue toutes les jointures et je n'ai pas trouvé. (je ne dispose pas de jointures RIGHT)

Merci

[al1_24]

Pour ne pas sélectionner les cat, tu peux faire ça :

Code :

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

1
2
3
4
5
6
  SELECT *
  FROM   Animal AS x 
    LEFT JOIN Elephant AS y ON x.AnimalId = y.AnimalId 
    LEFT JOIN Dog      AS z ON x.AnimalId = z.AnimalId
    LEFT JOIN Cat      AS w ON x.AnimalId = w.AnimalId
WHERE w.AnimalId IS NULL

[contremaitre]

Alors le problème c'est que si plus tard j'ajoute par exemple une table Bird je devrais modifier toutes mes anciennes requetes qui selectionnaient les Elephant et Dog pour ajouter une jointure sur Bird avec leurs id NULL

[fsmrel]

Pour ne pas citer les minous :

Code sql :

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

1
2
3
4
5
SELECT x.*, y.*, z.* 
  FROM   Animal AS x 
    LEFT JOIN Elephant AS y ON x.AnimalId = y.AnimalId 
    LEFT JOIN Dog      AS z ON x.AnimalId = z.AnimalId
where not (y.Animalid is null and z.Animalid is null)

[contremaitre]

Code sql :

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

1
2
 
where not (y.Animalid is null and z.Animalid is null)

merci