Bonsoir Aizen,
Comme vous utilisez MySQL et parce que vous voulez que la suppression d’une organisation entraîne celle de sa descendance, vous avez en fait intérêt à utiliser une auto-référence, avec CASCADE :
CREATE TABLE ORGANISATION
(
ID INT NOT NULL,
NOM VARCHAR(64) NOT NULL,
ID_ORGANISATION_PARENTE INT NOT NULL,
CONSTRAINT ORGANISATION_PK PRIMARY KEY (ID),
CONSTRAINT ORGANISATION_ORGANISATION_FK FOREIGN KEY (ID_ORGANISATION_PARENTE)
REFERENCES ORGANISATION (ID) ON DELETE CASCADE
) ;
Un jeu d’essai pour vérifier :
INSERT INTO ORGANISATION VALUES (1, 'org 1', 1) ;
INSERT INTO ORGANISATION VALUES (11, 'org 11', 1) ;
INSERT INTO ORGANISATION VALUES (111, 'org 111', 11) ;
INSERT INTO ORGANISATION VALUES (112, 'org 112', 11) ;
INSERT INTO ORGANISATION VALUES (113, 'org 113', 11) ;
INSERT INTO ORGANISATION VALUES (12, 'org 11', 1) ;
INSERT INTO ORGANISATION VALUES (121, 'org 111', 12) ;
INSERT INTO ORGANISATION VALUES (122, 'org 112', 12) ;
INSERT INTO ORGANISATION VALUES (123, 'org 113', 12) ;
INSERT INTO ORGANISATION VALUES (2, 'org 2', 2) ;
INSERT INTO ORGANISATION VALUES (21, 'org 21', 2) ;
INSERT INTO ORGANISATION VALUES (211, 'org 211', 21) ;
INSERT INTO ORGANISATION VALUES (212, 'org 212', 21) ;
INSERT INTO ORGANISATION VALUES (213, 'org 213', 21) ;
INSERT INTO ORGANISATION VALUES (22, 'org 22', 2) ;
INSERT INTO ORGANISATION VALUES (221, 'org 221', 22) ;
INSERT INTO ORGANISATION VALUES (222, 'org 222', 22) ;
INSERT INTO ORGANISATION VALUES (223, 'org 223', 22) ;
Visualisation du contenu de la table :
ID NOM ID_ORGANISATION_PARENTE
-- ---------- -----------------------
1 org 1 1
11 org 11 1
111 org 111 11
112 org 112 11
113 org 113 11
12 org 12 1
121 org 121 12
122 org 122 12
123 org 123 12
2 org 2 2
21 org 21 2
211 org 211 21
212 org 212 21
213 org 213 21
22 org 22 2
221 org 221 22
222 org 222 22
223 org 223 22
En passant, attention aux effets secondaires de l’auto-référence. Par exemple, si l’utilisateur veut savoir quelles sont les organisations qui dépendent de 'org 1', la requête suivante :
SELECT y.*
FROM ORGANISATION as x JOIN ORGANISATION as y on x.ID = y.ID_ORGANISATION_PARENTE
WHERE x.NOM = 'org 1' ;
ID NOM ID_ORGANISATION_PARENTE
-- ----------- -----------------------
1 org 1 1
11 org 11 1
12 org 12 1
De même, si l’on compte le nombre d’enfants de 'org 1' :
SELECT COUNT(*) FROM ORGANISATION WHERE ID_ORGANISATION_PARENTE = 1 ;
Autrement dit, à chaque fois il faut veiller à la pertinence des résultats. Ainsi, la requête précédente est à enrichir :
SELECT COUNT(*) FROM ORGANISATION WHERE ID_ORGANISATION_PARENTE = 1 AND ID_ORGANISATION_PARENTE <> ID ;
On peut aussi choisir de dériver de Charybde vers Scylla, c'est-à-dire ouvrir la porte au redoutable petit bonhomme Null...
CREATE TABLE ORGANISATION
(
ID INT NOT NULL,
NOM VARCHAR(64) NOT NULL,
ID_ORGANISATION_PARENTE INT ,
CONSTRAINT ORGANISATION_PK PRIMARY KEY (ID),
CONSTRAINT ORGANISATION_ORGANISATION_FK FOREIGN KEY (ID_ORGANISATION_PARENTE)
REFERENCES ORGANISATION (ID) ON DELETE CASCADE
) ;
INSERT INTO ORGANISATION VALUES (1, 'org 1', NULL) ;
INSERT INTO ORGANISATION VALUES (11, 'org 11', 1) ;
INSERT INTO ORGANISATION VALUES (111, 'org 111', 11) ;
INSERT INTO ORGANISATION VALUES (112, 'org 112', 11) ;
INSERT INTO ORGANISATION VALUES (113, 'org 113', 11) ;
INSERT INTO ORGANISATION VALUES (12, 'org 12', 1) ;
INSERT INTO ORGANISATION VALUES (121, 'org 121', 12) ;
INSERT INTO ORGANISATION VALUES (122, 'org 122', 12) ;
INSERT INTO ORGANISATION VALUES (123, 'org 123', 12) ;
INSERT INTO ORGANISATION VALUES (2, 'org 2', NULL) ;
INSERT INTO ORGANISATION VALUES (21, 'org 21', 2) ;
INSERT INTO ORGANISATION VALUES (211, 'org 211', 21) ;
INSERT INTO ORGANISATION VALUES (212, 'org 212', 21) ;
INSERT INTO ORGANISATION VALUES (213, 'org 213', 21) ;
INSERT INTO ORGANISATION VALUES (22, 'org 22', 2) ;
INSERT INTO ORGANISATION VALUES (221, 'org 221', 22) ;
INSERT INTO ORGANISATION VALUES (222, 'org 222', 22) ;
INSERT INTO ORGANISATION VALUES (223, 'org 223', 22) ;
Quels que soient nos choix, tentons maintenant de supprimer l’organisation 'org 1' :
DELETE FROM ORGANISATION WHERE ID = 1 ;
Au résultat, on constate que 'org 1' et sa descendance ont disparu de la table, on ne trouve plus que les lignes suivantes :
ID NOM ID_ORGANISATION_PARENTE
-- ----------- -----------------------
2 org 2 2
21 org 21 2
211 org 211 21
212 org 212 21
213 org 213 21
22 org 22 2
221 org 221 22
222 org 222 22
223 org 223 22
Notez à toutes fins utiles que si vous voulez connaître la descendance complète d’une organisation, vous n’échapperez pas à la mise en œuvre d’une procédure récursive, voyez par exemple le comptage des rivets par aile d’avion...
Passons au problème que vous posent les clés étrangères :
Envoyé par
Aizen64
Je reviens sur la réflexive, le fait est que j'ai du mal à poser des mots sur la problématique ce qui me pose des soucis de compréhension.
Ce que je comprends :
- Si une organisation est parente, ses enfants doivent être supprimés lors de sa suppression ce qui explique le ON DELETE CASCADE.
Le contraire n'est pas valable, forcément. Une organisation fille supprimée n'a aucune incidence sur la parente.
[...] Je m'embrouille avec les clés étrangères.
On est d’accord. Mais les SGBD ne nous facilitent pas la tâche, parce qu’ils n’ont pas tous le même comportement.. 
Revenons sur l’organisation des structures hiérarchiques, et supposons qu’on modélise une hiérarchie de la façon suivante, comme on vient de le faire ci-dessus :
Je me souviens des années quatre-vingt-dix, quand je « bouffais » du DB2 à longueur de temps : lorsque, comme ci-dessus, une table s’auto-référençait, il y avait un diktat (contestable, plus ou moins justifié), CASCADE était obligatoire, ce qui était fort gênant quand justement on ne voulait pas que la suppression d’une organisation entraînât celle de sa descendance ! Et puis DB2 (à partir de sa version 5) s’est conformé à la norme SQL, c'est-à-dire que la règle a été adoucie, on a pu aussi coder NO ACTION en plus de CASCADE.
Mais, vérité en deçà du territoire d’un SGBD, erreur au-delà... Si l’on passe à SQL Server, on se fait jeter si on code CASCADE dans le cas de l’auto-référence ! Encore un diktat, aussi contestable, et aussi peu justifié que dans le cas de DB2 !
Pour en revenir à DB2, avant la V5, si l’on ne voulait pas de la suppression en cascade d’une hiérarchie ou d’une nomenclature, la structure suivante s’imposait donc :
Mais, pour les contraintes correspondant respectivement aux rôles « organisation parente » et « organisation fille », si l’on codait CASCADE d’un côté, on devait aussi coder CASCADE de l’autre, et si l’on codait NO ACTION d’un côté, on devait aussi coder NO ACTION de l’autre. Et bien entendu, pour confirmer le dicton « vérité en deçà du territoire d’un SGBD, erreur au-delà » : en passant à SQL Server, à nouveau une surprise, interdiction de coder CASCADE des deux côtés à la fois ! (Ce qui ne ferait pas votre affaire... 
)
Mais puisque vous utilisez MySQL, voyons voir ce qui se passe avec ce SGBD et laissons des deux autres se contredire mutuellement, à coups d'arguments spécieux...
Partons du script SQL suivant (analogue au vôtre quant au choix des clauses CASCADE et NO ACTION) :
CREATE TABLE ORGANISATION
(
ID INT NOT NULL,
NOM VARCHAR(64) NOT NULL,
CONSTRAINT ORGANISATION_PK PRIMARY KEY (ID)
) ;
CREATE TABLE HIERARCHIE
(
ID_ORGANISATION INT NOT NULL,
ID_ORGANISATION_PARENTE INT NOT NULL,
CONSTRAINT HIERARCHIE_PK PRIMARY KEY (ID_ORGANISATION),
CONSTRAINT HIERARCHIE_ORGANISATION_FK
FOREIGN KEY (ID_ORGANISATION) REFERENCES ORGANISATION (ID)
ON DELETE CASCADE,
CONSTRAINT HIERARCHIE_ORGANISATION_PARENTE_FK
FOREIGN KEY (ID_ORGANISATION_PARENTE) REFERENCES ORGANISATION (ID)
ON DELETE NO ACTION
) ;
Analysons les effets de la clause CASCADE.
(1) En ce qui concerne a contrainte HIERARCHIE_ORGANISATION_FK, on code « ON DELETE CASCADE », ce qui est logique, puisque si l’on veut supprimer l’organisation 'org 1', elle ne peut pas s’opposer par elle-même à sa destruction.
(2) A supposer qu’on ait voulu empêcher la suppression d’une organisation parce que d’autres organisations en dépendent, pour la contrainte HIERARCHIE_ORGANISATION_PARENTE_FK, on aurait dû alors coder « ON DELETE NO ACTION » (ce qui est du reste la clause par défaut).
(3) En fait, comme vous voulez que la suppression d’une organisation entraîne celle de sa descendance, alors pour la contrainte HIERARCHIE_ORGANISATION_PARENTE_FK, en toute logique « ON DELETE CASCADE » s’impose, 'org 11', 'org 12' ne peuvent pas s’opposer à leur propre suppression quand leur mère 'org 1' doit être supprimée.
Rien de tel qu’un bon coup d’œil plutôt qu’une mauvaise impasse, voyons voir ce qui se passe réellement avec « ON DELETE CASCADE »...
Créons un certain nombre d’organisations :
INSERT INTO ORGANISATION VALUES (1, 'org 1') ;
INSERT INTO ORGANISATION VALUES (11, 'org 11') ;
INSERT INTO ORGANISATION VALUES (111, 'org 111') ;
INSERT INTO ORGANISATION VALUES (112, 'org 112') ;
INSERT INTO ORGANISATION VALUES (113, 'org 113') ;
INSERT INTO ORGANISATION VALUES (12, 'org 12') ;
INSERT INTO ORGANISATION VALUES (121, 'org 121') ;
INSERT INTO ORGANISATION VALUES (122, 'org 122') ;
INSERT INTO ORGANISATION VALUES (123, 'org 123') ;
INSERT INTO ORGANISATION VALUES (2, 'org 2') ;
INSERT INTO ORGANISATION VALUES (21, 'org 21') ;
INSERT INTO ORGANISATION VALUES (211, 'org 211') ;
INSERT INTO ORGANISATION VALUES (212, 'org 212') ;
INSERT INTO ORGANISATION VALUES (213, 'org 213') ;
INSERT INTO ORGANISATION VALUES (22, 'org 22') ;
INSERT INTO ORGANISATION VALUES (221, 'org 221') ;
INSERT INTO ORGANISATION VALUES (222, 'org 222') ;
INSERT INTO ORGANISATION VALUES (223, 'org 223') ;
SELECT * FROM ORGANISATION ;
Et créons les liens pour constituer une hiérarchie de ces organisations :
INSERT INTO HIERARCHIE VALUES (11, 1) ;
INSERT INTO HIERARCHIE VALUES (111, 11) ;
INSERT INTO HIERARCHIE VALUES (112, 11) ;
INSERT INTO HIERARCHIE VALUES (113, 11) ;
INSERT INTO HIERARCHIE VALUES (12, 1) ;
INSERT INTO HIERARCHIE VALUES (121, 12) ;
INSERT INTO HIERARCHIE VALUES (122, 12) ;
INSERT INTO HIERARCHIE VALUES (123, 12) ;
INSERT INTO HIERARCHIE VALUES (21, 2) ;
INSERT INTO HIERARCHIE VALUES (211, 21) ;
INSERT INTO HIERARCHIE VALUES (212, 21) ;
INSERT INTO HIERARCHIE VALUES (213, 21) ;
INSERT INTO HIERARCHIE VALUES (22, 2) ;
INSERT INTO HIERARCHIE VALUES (221, 22) ;
INSERT INTO HIERARCHIE VALUES (222, 22) ;
INSERT INTO HIERARCHIE VALUES (223, 22) ;
Visualisons le contenu de la table HIERARCHIE :
ID_ORGANISATION ID_ORGANISATION_PARENTE
--------------- -----------------------
11 1
111 11
112 11
113 11
12 1
121 12
122 12
123 12
21 2
211 21
212 21
213 21
22 2
221 22
222 22
223 22
Supprimons maintenant l’organisation 'org 1' :
DELETE FROM ORGANISATION WHERE ID = 1 ;
Au résultat :
ID_ORGANISATION ID_ORGANISATION_PARENTE
--------------- -----------------------
111 11
112 11
113 11
121 12
122 12
123 12
21 2
211 21
212 21
213 21
22 2
221 22
222 22
223 22
Avec cette structure, dans le cas de DB2 et SQL Server, on s’en sort sans problème avec une jointure récursive. MySQL n’offrant pas cette possibilité, vouloir supprimer la descendance de 'org 1', nécessite que l’on sorte l’artillerie lourde, à savoir une procédure stockée récursive...
En ce qui vous concerne, l’auto-référence s’impose donc, avec, comme on l’a vu, toutes les précautions à prendre pour qu’une organisation ne soit pas considérée comme étant sa propre fille...
En passant, expulsez l’attribut TYPE_ORGANISATION_ID de la clé primaire de votre table ORGANISATION, sinon cela veut dire, très formellement, qu’une organisation peut avoir plus d’un type...
Je vais essayer de regarder vos autres problèmes.
N.B. Si d’aventure telle ou telle réponse a pu vous aider, n’hésitez pas à voter...
Base de données de contacts
Répondre avec citation
Partager