MySQL clés etrangères DELETE en cascade, petit soucis

Bonjour tout le monde,

J'ai un petit soucis que je n'arrive pas à résoudre, peut-etre avez vous la solution.
Je vous explique le contexte.

J'ai une base de données avec:
- une table Voitures [id, type, motorisation, ...]
- une tables Acquereurs [id, nom, prenom, fichier1, fichier2, fichierX, ..., id_voiture]
- une table Fichiers [id, mimtype, chemin, ...] (qui regroupe de des chemins vers des fichiers. Par contre il y a des fichiers en rapport avec la partie Acquéreurs mais aussi d'autres parties dont je ne parle pas)

Les clés étrangères:
voiture.id => acquereurs.id_voiture
acquereurs.fichier1 => fichiers.id
acquereurs.fichier2 => fichiers.id
acquereurs.fichierX => fichiers.id


Quand je supprime une voiture, les acquéreurs sont bien supprimés par contre les fichiers acquéreurs ne sont pas supprimés.

Quel est la solution pour faire supprimer les fichiers associés a l'acquéreur lors de la suppression d'une voiture ? En sachant que je ne peux pas mettre de clé étrangère id_voiture dans la table fichiers.


Me suis-je bien fait comprendre ?



Merci à vous !

Quels sont les codes de créations des clés étrangères ?

Au passage, il est préférable d'avoir une table d'association entre les tables acquereurs et fichiers, plutôt qu'une énumération (fichier1, fichier2, fichierX)

Citation:

---

Envoyé par skuatamad

Quels sont les codes de créations des clés étrangères ?

Au passage, il est préférable d'avoir une table d'association entre les tables acquéreurs et fichiers, plutôt qu'une énumération (fichier1, fichier2, fichierX)

---

Voici le code de création de la table acquéreurs avec les clés étrangères

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

CREATE TABLE `test_foreign`.`acquereurs` ( `id` INT NOT NULL AUTO_INCREMENT, `id_fichier1` INT NULL, `id_fichier2` INT NULL, `id_voiture` INT NULL, PRIMARY KEY (`id`), INDEX `fk_voiture_idx` (`id_voiture` ASC), INDEX `fk_fichier1_idx` (`id_fichier1` ASC), INDEX `fk_fichier2_idx` (`id_fichier2` ASC), CONSTRAINT `fk_voiture` FOREIGN KEY (`id_voiture`) REFERENCES `test_foreign`.`voitures` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `fk_fichier1` FOREIGN KEY (`id_fichier1`) REFERENCES `test_foreign`.`fichiers` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `fk_fichier2` FOREIGN KEY (`id_fichier2`) REFERENCES `test_foreign`.`fichiers` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION);

---

Je pense que je vais faire des tables d'associations pour les fichiers, mais déjà j'aimerai savoir faire fonctionner cette version.

Acquereur est la table fille, la contrainte ON DELETE CASCADE se propage depuis la table mère vers la table fille et non l'inverse.

Si le fichier est supprimé alors les lignes le référençant dans acquereurs le seront (ce qui n'est d'ailleurs probablement pas l'effet souhaité)

Vous devez supprimer les fichiers par du code au sein d'une transaction, potentiellement dans un trigger.

Ok, je vais essayer de regarder du côté des triggers.


Merci

Salut Ashraam.

Citation:

---

Envoyé par Ashraam

Ok, je vais essayer de regarder du côté des triggers.

---

Les triggers ne servent à rien dans ce cas.

Citation:

---

Envoyé par Ashraam

Quand je supprime une voiture, les acquéreurs sont bien supprimés par contre les fichiers acquéreurs ne sont pas supprimés.

---

Il faut utiliser une "foreign key", entre les tables voiture et acquéreurs mais aussi une "foreign key" entre acquéreurs et fichiers.

Dans les deux cas, la "foreign key" est avec "on delete cascade on update cascade".

Si tu supprimes une ligne dans la table 'père', la propagation se fait de la table 'père' vers 'fils', puis de la table 'fils' vers 'petit-fils' et ainsi de suite.

Voici un exemple avec trois tables :

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 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 126 127 128 129

-------------- SET AUTOCOMMIT = 0 --------------   -------------- START TRANSACTION --------------   -------------- DROP DATABASE IF EXISTS `base` --------------   -------------- CREATE DATABASE `base` DEFAULT CHARACTER SET `latin1` DEFAULT COLLATE `latin1_general_ci` --------------   -------------- DROP TABLE IF EXISTS `pere` --------------   -------------- CREATE TABLE `pere` ( `pere_id` integer unsigned NOT NULL, `libelle` char(20) NOT NULL, PRIMARY KEY (`pere_id`) ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED --------------   -------------- INSERT INTO `pere` (`pere_id`,`libelle`) VALUES (1, 'rouge'), (2, 'vert'), (3, 'jaune'), (4, 'bleu') --------------   -------------- select * from `pere` --------------   +---------+---------+ | pere_id | libelle | +---------+---------+ | 1 | rouge | | 2 | vert | | 3 | jaune | | 4 | bleu | +---------+---------+ -------------- DROP TABLE IF EXISTS `fils` --------------   -------------- CREATE TABLE `fils` ( `fils_id` integer unsigned NOT NULL, `pere_id` integer unsigned NOT NULL, `nuance` char(20) NOT NULL, PRIMARY KEY (`fils_id`), CONSTRAINT `FK_01` FOREIGN KEY (`pere_id`) REFERENCES `pere` (`pere_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED --------------   -------------- INSERT INTO `fils` (`fils_id`,`pere_id`,`nuance`) VALUES (1, 1, 'carmin'), (2, 1, 'cinabre'), (3, 2, 'pomme'), (4, 3, 'citron'), (5, 3, 'ambre'), (6, 3, 'moutarde') --------------   -------------- select * from `fils` --------------   +---------+---------+----------+ | fils_id | pere_id | nuance | +---------+---------+----------+ | 1 | 1 | carmin | | 2 | 1 | cinabre | | 3 | 2 | pomme | | 4 | 3 | citron | | 5 | 3 | ambre | | 6 | 3 | moutarde | +---------+---------+----------+ -------------- DROP TABLE IF EXISTS `association` --------------   -------------- CREATE TABLE `association` ( `assoc_id` integer unsigned NOT NULL, `fils_id` integer unsigned NOT NULL, `refer` char(20) NOT NULL, PRIMARY KEY (`assoc_id`), CONSTRAINT `FK_02` FOREIGN KEY (`fils_id`) REFERENCES `fils` (`fils_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED --------------   -------------- INSERT INTO `association` (`assoc_id`,`fils_id`,`refer`) VALUES (1, 3, 'd\'amour'), (2, 4, 'lemon'), (3, 5, 'amora') --------------   -------------- select * from `association` --------------   +----------+---------+---------+ | assoc_id | fils_id | refer | +----------+---------+---------+ | 1 | 3 | d'amour | | 2 | 4 | lemon | | 3 | 5 | amora | +----------+---------+---------+ -------------- COMMIT --------------   -------------- SET AUTOCOMMIT = 1 --------------     Appuyez sur une touche pour continuer...

---

Et voici l'exécution. Je supprime une ligne de la table père dont l'identifiant est le '3'.

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 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

-------------- select * from pere --------------   +---------+---------+ | pere_id | libelle | +---------+---------+ | 1 | rouge | | 2 | vert | | 3 | jaune | | 4 | bleu | +---------+---------+ -------------- select * from fils --------------   +---------+---------+----------+ | fils_id | pere_id | nuance | +---------+---------+----------+ | 1 | 1 | carmin | | 2 | 1 | cinabre | | 3 | 2 | pomme | | 4 | 3 | citron | | 5 | 3 | ambre | | 6 | 3 | moutarde | +---------+---------+----------+ -------------- select * from association --------------   +----------+---------+---------+ | assoc_id | fils_id | refer | +----------+---------+---------+ | 1 | 3 | d'amour | | 2 | 4 | lemon | | 3 | 5 | amora | +----------+---------+---------+ -------------- select tb1.pere_id, tb1.libelle, tb2.fils_id, tb2.nuance, tb3.assoc_id, tb3.refer from pere as tb1 inner join fils as tb2 on tb2.pere_id = tb1.pere_id inner join association as tb3 on tb3.fils_id = tb2.fils_id --------------   +---------+---------+---------+--------+----------+---------+ | pere_id | libelle | fils_id | nuance | assoc_id | refer | +---------+---------+---------+--------+----------+---------+ | 2 | vert | 3 | pomme | 1 | d'amour | | 3 | jaune | 4 | citron | 2 | lemon | | 3 | jaune | 5 | ambre | 3 | amora | +---------+---------+---------+--------+----------+---------+ -------------- delete from pere where pere_id = 3 --------------   -------------- select tb1.pere_id, tb1.libelle, tb2.fils_id, tb2.nuance, tb3.assoc_id, tb3.refer from pere as tb1 inner join fils as tb2 on tb2.pere_id = tb1.pere_id inner join association as tb3 on tb3.fils_id = tb2.fils_id --------------   +---------+---------+---------+--------+----------+---------+ | pere_id | libelle | fils_id | nuance | assoc_id | refer | +---------+---------+---------+--------+----------+---------+ | 2 | vert | 3 | pomme | 1 | d'amour | +---------+---------+---------+--------+----------+---------+ -------------- select * from pere --------------   +---------+---------+ | pere_id | libelle | +---------+---------+ | 1 | rouge | | 2 | vert | | 4 | bleu | +---------+---------+ -------------- select * from fils --------------   +---------+---------+---------+ | fils_id | pere_id | nuance | +---------+---------+---------+ | 1 | 1 | carmin | | 2 | 1 | cinabre | | 3 | 2 | pomme | +---------+---------+---------+ -------------- select * from association --------------   +----------+---------+---------+ | assoc_id | fils_id | refer | +----------+---------+---------+ | 1 | 3 | d'amour | +----------+---------+---------+   Appuyez sur une touche pour continuer...

---

@+