Discussion :

[CinePhil]

Bonjour,

Soit l'association suivante :

Passage_diplome -0,n----prouver----0,1- Fichier

Traduction :
Un fichier peut prouver un passage de diplôme et un passage de diplôme peut être prouvé par plusieurs fichiers.

J'ai donc une table associative pour matérialiser l'association "prouver". Dans cette table, j'ai évidemment des clés étrangères référençant d'une part le passage de diplôme et d'autre part le fichier.
Si je supprime un fichier, la clé étrangère supprime la ligne correspondante dans la table associative.
Si je supprime un passage de diplôme, la clé étrangère supprime la ligne correspondante dans la table associative.

Mais dans ce dernier cas, je veux aussi supprimer le fichier s'il existe puisqu'il ne prouve plus rien.
J'ai donc créé un trigger dans la table associative :

Code :

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
DELIMITER //
CREATE TRIGGER pef_public.tg_fpp_after_delete 
	AFTER DELETE ON tj_fic_prouver_pdi_fpp 
	FOR EACH ROW
/****************************************************************************************************************
Base :			pef_public
Objet :			Trigger
Auteur :		Philippe Leménager
Version :		V0.1 - plemenager - 2020-04-28 - Création
Description : 	Supprime le fichier prouvant le passage d'un diplôme
				Utile si la suppression de la ligne dans tj_fic_prouver_pdi_fpp vient en cascade d'une supression dans te_passage_diplome_pdi
Utilise :		te_fichier_fic 		Table des fichiers joints
Historique :	
*****************************************************************************************************************/
BEGIN
	DELETE FROM te_fichier_fic
	WHERE fic_id = OLD.fpp_id_fichier;
END //
DELIMITER ;

Mais c'est sans effet ; j'ai toujours la ligne dans la table des fichiers. C'est comme si le trigger ne se déclenchait pas en cas de suppression par la cascade d'une clé étrangère.
Ou alors j'ai mal programmé mon trigger ?

[SQLpro]

...
Passage_diplome -0,n----prouver----0,1- Fichier

...

J'ai donc une table associative pour matérialiser l'association "prouver".

Soit tu t'es trompé dans ton modèle car ce que tu donne est en 1:n et non en n:m, donc pas de table associative !

Soit ….

Avec le DDL des tables, c'est mieux !

A +

[CinePhil]

Je ne me suis pas trompé !

Le fichier peut aussi être associé à d'autres tables (pièce jointe à une inscription, attestation du statut de l'étudiant...) donc j'ai à chaque fois une table associative et le même phénomène.

DDL de la table des passages de diplome :

Code :

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
CREATE TABLE `te_passage_diplome_pdi` (
 `pdi_id_candidat` int(11) NOT NULL COMMENT 'Identifiant du candidat',
 `pdi_id_diplome` int(11) NOT NULL COMMENT 'Identifiant du diplome',
 `pdi_id_ville` int(11) NOT NULL COMMENT 'Identifiant de la ville où est passé le diplôme',
 `pdi_id_mention` tinyint(4) NOT NULL COMMENT 'Identifiant de la mention obtenue',
 `pdi_id_position_diplome` tinyint(4) NOT NULL COMMENT 'Identifiant de la position du diplôme pour le candidat',
 `pdi_annee` smallint(6) NOT NULL COMMENT 'Année de passage ou d''étude en cours',
 `pdi_etablissement` varchar(96) NOT NULL DEFAULT '' COMMENT 'Nom de l''établissement où est passé le diplôme',
 PRIMARY KEY (`pdi_id_candidat`,`pdi_id_diplome`),
 KEY `x_pdi_id_diplome` (`pdi_id_diplome`),
 KEY `x_pdi_id_ville` (`pdi_id_ville`),
 KEY `x_pdi_id_mention` (`pdi_id_mention`),
 KEY `x_pdi_id_position_diplome` (`pdi_id_position_diplome`),
 CONSTRAINT `fk_pdi_id_candidat` FOREIGN KEY (`pdi_id_candidat`) REFERENCES `th_candidat_can` (`can_id_personne_physique`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `fk_pdi_id_diplome` FOREIGN KEY (`pdi_id_diplome`) REFERENCES `te_diplome_dpl` (`dpl_id`) ON UPDATE CASCADE,
 CONSTRAINT `fk_pdi_id_mention` FOREIGN KEY (`pdi_id_mention`) REFERENCES `referentiel`.`tr_mention_diplome_mdp` (`mdp_id`) ON UPDATE CASCADE,
 CONSTRAINT `fk_pdi_id_position_diplome` FOREIGN KEY (`pdi_id_position_diplome`) REFERENCES `referentiel`.`tr_position_diplome_pdp` (`pdp_id`) ON UPDATE CASCADE,
 CONSTRAINT `fk_pdi_id_ville` FOREIGN KEY (`pdi_id_ville`) REFERENCES `te_ville_vil` (`vil_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Passage d''un diplôme par un candidat'

DDL de la table des fichiers :

Code :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE `te_fichier_fic` (
 `fic_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Identifiant du fichier',
 `fic_id_objet` tinyint(4) NOT NULL COMMENT 'Identifiant de l''objet du fichier',
 `fic_nom` varchar(48) NOT NULL COMMENT 'Nom du fichier',
 PRIMARY KEY (`fic_id`),
 UNIQUE KEY `xu_fic_nom` (`fic_nom`) USING BTREE,
 KEY `x_fic_id_objet` (`fic_id_objet`) USING BTREE,
 CONSTRAINT `fk_fic_id_objet` FOREIGN KEY (`fic_id_objet`) REFERENCES `referentiel`.`tr_objet_fichier_ofc` (`ofc_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COMMENT='Fichier joint'

DDL de la table associative :

Code :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE `tj_fic_prouver_pdi_fpp` (
 `fpp_id_fichier` int(11) NOT NULL COMMENT 'Identifiant du fichier du diplôme',
 `fpp_id_candidat` int(11) NOT NULL COMMENT 'Identifiant du candidat',
 `fpp_id_diplome` int(11) NOT NULL COMMENT 'Identifiant du diplôme obtenu par le candidat',
 PRIMARY KEY (`fpp_id_fichier`),
 UNIQUE KEY `x_fpp_candidat_diplome` (`fpp_id_candidat`,`fpp_id_diplome`),
 CONSTRAINT `fk_fpp_candidat_diplome` FOREIGN KEY (`fpp_id_candidat`, `fpp_id_diplome`) REFERENCES `te_passage_diplome_pdi` (`pdi_id_candidat`, `pdi_id_diplome`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `fk_fpp_id_fichier` FOREIGN KEY (`fpp_id_fichier`) REFERENCES `te_fichier_fic` (`fic_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Preuve de l''obtention d''un diplôme par un candidat'

[CinePhil]

Bon ben c'est ce que je craignais :

Triggers are not activated by foreign key actions.

Va falloir que je fasse de la procédure de suppression.

[escartefigue]

Je ne comprends pas cette explication :

Je ne me suis pas trompé !
Le fichier peut aussi être associé à d'autres tables (pièce jointe à une inscription, attestation du statut de l'étudiant...) donc j'ai à chaque fois une table associative et le même phénomène.

Car en ce cas le modèle devient tout simplement :
Passage_diplome -0,n----prouver----0,1- Fichier -0,x--- attester --- 0,n Autre_entité
Et toujours pas besoin de table associative...
Et ce d'autant moins que dans le DDL de la table associative, on voit que la PK est composée notamment de l'identifiant du diplôme, tout à fait normal jusque là, mais qui n'a pas de sens s'il s'agit d'attester une inscription !

[CinePhil]

Voir mon article "Quand faut-il une table associative ?"

Cas 04) A -0,1—-associer—-1,n- B
La clé étrangère ne peut pas aller dans B puisque un B peut être associé à plusieurs A. Mais elle ne peut pas non plus aller dans A puisque un A peut ne pas être associé à un B. Il faut donc une table associative. Et comme un A sera associé au maximum une seule fois à un B, la clé primaire de la table associative sera la clé étrangère référençant A.

Cas 03) A -0,1—-associer—-0,n- B
Aucune des deux entités n’est systématiquement associée à l’autre (cardinalités minimales à 0). Il faut donc une table associative dont la clé primaire sera, pour la même raison que dans le cas 04, la clé étrangère référençant A.

[escartefigue]

Je ne suis pas adepte de la chasse au "null" dans ce cas précis, une FK nullable est tout à fait acceptable et correspond justement à ce cas de figure.
Quoi qu'il en soit, l'usage pour d'autres tables ne convient pas (cas de la pièce jointe à une inscription), quelque chose m'échappe dans les explications