Discussion :

[Ashraam]

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 !

[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 acquereurs et fichiers, plutôt qu'une énumération (fichier1, fichier2, fichierX)

[Ashraam]

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 :

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

[skuatamad]

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.

[Ashraam]

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


Merci

[Artemus24]

Salut Ashraam.

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

Les triggers ne servent à rien dans ce cas.

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 :

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

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

@+