[MySQL] ON CASCADE ?

Version imprimable

bonjour à vous !

Voici 3 tables, j'aimerai faire plusieurs choses.

Si je supprime 1 occurrence dans la table test que ça supprime son occurrence qui suit dans test3 et test2.

exemple bidon lol:

test = membre
test2 = chat
test3 = appartient

si je supprime un membre, ça supprime son appartenance avec le chat mais supprime également le chat qui lui ai propre.

mais un membre peut perdre un chat donc si je supprime le chat dans la table chat il faut que ça supprime aussi l'occurrence dans appartient.

vous me suivez 8O ?

je travail sous MySQL
je me suis aidé de cette page pour essayer de comprendre mais je n'ai pas réussi:
http://dev.mysql.com/doc/refman/5.0/...nstraints.html

est-ce que vous pouvez m'aider s'il vous plait ?
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 CREATE TABLE IF NOT EXISTS test3 ( test2_id int(11) NOT NULL, test_id int(11) NOT NULL, PRIMARY KEY (test2_id,test_id), KEY FK_test (test_id), KEY FK_test2 (test2_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS test2 ( test2_id int(11) NOT NULL AUTO_INCREMENT, test2_txt varchar(50) DEFAULT NULL, PRIMARY KEY (test2_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ; CREATE TABLE IF NOT EXISTS test ( test_id int(11) NOT NULL AUTO_INCREMENT, test_txt varchar(50) NOT NULL, PRIMARY KEY (test_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; ALTER TABLE test3 ADD CONSTRAINT FK_test FOREIGN KEY (test_id) REFERENCES test (me_id), ADD CONSTRAINT FK_test2 FOREIGN KEY (test2_id) REFERENCES test2 (test2_id);

10/02/2010, 17h12
CinePhil

Citation:

Envoyé par noobyyy

exemple bidon lol:

test = membre
test2 = chat
test3 = appartient

Ta structure suppose qu'un chat puisse appartenir à plusieurs membres.

Le MCD correspondant est celui-ci :
chat -0,n----appartient----0,n- membre

Citation:

si je supprime un membre, ça supprime son appartenance avec le chat mais supprime également le chat qui lui ai propre.

Et si ce chat appartient aussi à un autre membre, ce second membre a tout à coup perdu son chat ! Il ne va pas être content !

Un peu de théorie maintenant...
ON CASCADE s'applique à la table qui contient la clé étrangère, pas l'inverse, et heureusement !
Je prends un autre cas plus concret :
Règle de gestion :
Une personne peut posséder plusieurs voitures et une voiture est possédée par une seule personne.

MCD :
Personne -0,n----Posséder----1,1- Voiture

Tables :
Personne (p_id, p_nom...)
Voiture (v_id, v_id_personne, ...)

Dans la table Voiture figure la contrainte de clé étrangère référençant l'identifiant de la personne.
Avec ON CASCADE, si je supprime la personne, je supprime la voiture ; pourquoi pas !
Mais si je supprime la voiture et que ça supprime aussi la personne, et que celle-ci possède d'autres voitures, celles-ci se retrouveront sans propriétaire.

Dans une association avec des cardinalités (0,n - 0,n), les clés étrangères sont dans la table associative et c'est la suppression d'une instance d'une des entités qui fera disparaitre l'instance de l'association.
Mais la suppression de l'instance de l'association ne doit jamais supprimer les instances des entités !

Donc ceci :

Citation:

mais un membre peut perdre un chat donc si je supprime le chat dans la table chat il faut que ça supprime aussi l'occurrence dans appartient.

Ne doit jamais arriver avec l'association que tu as décrite.

Donne nous ton besoin réel et nous t'aideront à le modéliser et à l'implanter correctement.
10/02/2010, 17h31
noobyyy

c'est bon j'ai trouvé mon erreur, je te remercie beaucoup !

je relance le topic car je n'arrive pas à faire marcher cette exemple:

source: http://dev.mysql.com/doc/refman/5.0/...nstraints.html

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
                      price DECIMAL,
                      PRIMARY KEY(category, id)) TYPE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
                      PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
                      product_category INT NOT NULL,
                      product_id INT NOT NULL,
                      customer_id INT NOT NULL,
                      PRIMARY KEY(no),
                      INDEX (product_category, product_id),
                      FOREIGN KEY (product_category, product_id)
                        REFERENCES product(category, id)
                        ON UPDATE CASCADE ON DELETE RESTRICT,
                      INDEX (customer_id),
                      FOREIGN KEY (customer_id)
                        REFERENCES customer(id)) TYPE=INNODB;

Impossible de supprimer un client, surement à cause de ON DELETE RESTRICT
mais si je veux le supprimer comment faire ?

merci.

11/02/2010, 17h19
CinePhil

Il n'y a pas d'instruction ON sur la clé étrangère référençant le client.

Je ne trouve pas dans la doc quelle est la valeur par défaut en l'absence d'instruction.

Bonne pratique : toujours spécifier le comportement de l'intégrité référentielle !
11/02/2010, 17h30
noobyyy

est-ce aussi normal alors que si on supprime un produit c'est impossible aussi ? a moins que je me sois trompé dans les valeurs..

c'est pas très clair leur exemple...
12/02/2010, 00h44
CinePhil

Sur le produit il y a ON DELETE RESTRICT.
Donc c'est normal qu'on ne puisse pas supprimer un produit s'il existe une ligne en rapport avec ce produit dans la table product_order.

A la réflexion, pour l'impossibilité de supprimer un client, c'est plutôt dû au fait que la colonne product_order.customer_id est NOT NULL. Si on supprime le client, la référence au client est perdue avec le client dans la table product_order et comme le SGBD ne peut pas mettre cette clé étrangère à NULL, il interdit la suppression du client.
12/02/2010, 11h44
noobyyy
comment trouver un exemple avec 3 tables lorsque qu'on supprime un membre ça fait une cascade ? car sur le site officiel de MySQL j'ai du mal à comprendre !

exemple:

membre ( membre_id, nom )
ville( ville_id, nom )
habite( membre_id,ville_id )

le ON DELETE CASCADE doit se mettre ou ?
Code:

1 2 ALTER TABLE habite ADD CONSTRAINT PK_membre FOREIGN KEY (membre_id, ville_id) REFERENCES membre(membre_id) ON DELETE CASCADE
un genre comme ceci :calim2: ?

La structure que tu présentes :

Citation:

membre ( membre_id, nom )
ville( ville_id, nom )
habite( membre_id,ville_id )

Elle est issue du MCD suivant :
membre -1,n----habiter----0,n- ville

Ce qui se lit (règle de gestion) :
"Un membre habite dans 1 à plusieurs villes (8O) et une ville peut héberger plusieurs membres."
Si tu supprimes un membre, tu ne vas supprimer les villes où il habite car d'autres membres peuvent y habiter aussi !

Un exemple de cascade sur trois table serait plutôt celui-ci...
Règle de gestion :
Un appareil est constitué de un à plusieurs ensembles comprenant de un à plusieurs éléments.

MCD :
appareil -1,n----constituer----1,1- ensemble -1,n----comprendre----1,1- élément

Tables :
appareil (ap_id, ap_nom, ap_ref, ap_num_serie...)
ensemble (en_id, en_id_appareil, en_num_serie...)
element (el_id, el_id_ensemble, el_num_serie...)

Si je supprime l'appareil (parce qu'il est cassé), je supprime tous ses constituants, donc tous ses ensembles et tous les éléments de ces ensembles.

Requêtes de création des 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 CREATE TABLE appareil ( ap_id int(10) unsigned NOT NULL AUTO_INCREMENT, ap_nom varchar(30) COLLATE utf8_unicode_ci NOT NULL, ap_ref varchar(10) COLLATE utf8_unicode_ci NOT NULL, ap_num_serie varchar(10) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (ap_id), KEY ap_nom (ap_nom) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE ensemble ( en_id int(10) unsigned NOT NULL AUTO_INCREMENT, en_id_appareil int(10) unsigned NOT NULL, en_num_serie varchar(10) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (en_id), KEY en_id_appareil (en_id_appareil), CONSTRAINT ensemble_ibfk_1 FOREIGN KEY (en_id_appareil) REFERENCES appareil (ap_id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE element ( el_id int(10) unsigned NOT NULL AUTO_INCREMENT, el_id_ensemble int(10) unsigned NOT NULL, el_num_serie varchar(10) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (el_id), KEY el_id_ensemble (el_id_ensemble), CONSTRAINT element_ibfk_1 FOREIGN KEY (el_id_ensemble) REFERENCES ensemble (en_id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Quelques données...
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 INSERT INTO appareil(ap_nom, ap_ref, ap_num_serie) VALUES ('Biglotron', 'BG00012345', 'BG00000001'), ('Bidule', 'BD01024563', 'BD00000124'); INSERT INTO ensemble (en_id_appareil, en_num_serie) VALUES (1, 'ENS1234567'), (2, 'ENS1245678'), (1, 'ENS0001234'); INSERT INTO element (el_id_ensemble, el_num_serie) VALUES (1, 'EL0123'), (1, 'EL345678'), (2, 'EL456789'), (2, 'EL125487');
Affichons les appreils et tout ce qui les compose :
Code:

1 2 3 4 5 SELECT a.ap_nom, en.en_id, en.en_num_serie, el.el_id, el.el_num_serie FROM appareil AS a INNER JOIN ensemble AS en ON en.en_id_appareil = a.ap_id INNER JOIN element AS el ON el.el_id_ensemble = en.en_id ORDER BY a.ap_nom, en.en_id, el.el_id
Résultat :

Citation:

ap_nom en_id en_num_serie el_id el_num_serie
Bidule 2 ENS1245678 3 EL456789
Bidule 2 ENS1245678 4 EL125487
Biglotron 1 ENS1234567 1 EL0123
Biglotron 1 ENS1234567 2 EL345678

Supprimons un appareil :
Code:

1 2 DELETE FROM appareil WHERE ap_nom = 'Bidule'
Relançons la requête de sélection précédente.
Résultat :

Citation:

ap_nom en_id en_num_serie el_id el_num_serie
Biglotron 1 ENS1234567 1 EL0123
Biglotron 1 ENS1234567 2 EL345678

=> L'appareil Bidule n'est plus là.

Affichons tous les ensembles :
Code:

1 2 SELECT en_id, en_id_appareil, en_num_serie FROM ensemble
Résultat :

Citation:

en_id en_id_appareil en_num_serie
1 1 ENS1234567
3 1 ENS0001234

=> Il n'y a plus l'ensemble (2, 'ENS1245678')

Affichons tous les éléments :
Code:

1 2 SELECT el_id, el_id_ensemble, el_num_serie FROM element
Résultat :

Citation:

el_id el_id_ensemble el_num_serie
1 1 EL0123
2 1 EL345678

=> Il n'y a plus les éléments (2, 'EL456789') et (2, 'EL125487')

En une seule requête de suppression, j'ai bien mis à jour 1 table + deux autres en cascade !

17/02/2010, 14h12
noobyyy

je te remercie beaucoup du temps que tu as passé pour m'expliquer :ccool:
il y a des petites choses que je ne comprends pas.

je vais t'expliquer ce que je veux exactement faire pour mon site:

--

Un membre peut sélectionner plusieurs sites prédéfinis dans son compte pour l'afficher ensuite dans son profil. Ces sites sont dans la table web. Mais le membre s'il ne dépasse pas le nombre de 5 sites, saisie un site par défaut.

exemple:
5 sites par défauts ou 4 sites par défauts et 1 personnalisé.

au début je voulais faire le schéma suivant:

web ( web_id, web_nom, web_lien, web_description )
[PK]web_id

membre ( me_id, me_nom )
[PK]me_id

linked ( web_id, me_id )
[PK]web_id, me_id

* un membre n'est pas obligé d'avoir des liens dans son profil.
* si un membre supprime son compte ça supprime automatiquement ses liens dans linked. donc mettre ON DELETE CASCADE DANS linked
* un membre ayant 4 sites dans linked peut avoir un site personnalisé mais ou le mettre ? dans un nouvelle table ?

Voila mon cas réel.
Merci
17/02/2010, 19h43
CinePhil

Ajoute une colonne dans la table linked indiquant s'il s'agit d'un site personnalisé ou par défaut.

Enfin si j'ai bien compris ton besoin.
18/02/2010, 00h15
noobyyy

bah... chaque membre peuvent mettre un lien personnalisé, je veux dire un lien que personne ne connait. Donc je ne peux pas prévoir à l'avance le lien qu'il va saisir. Donc je crée une nouvelle table peut etre ?

et sinon pour ce qui est de mon schéma en haut il est correct ? aussi bien du on cascade delete que pour la compréhension.

merci
18/02/2010, 08h59
CinePhil

Citation:

chaque membre peuvent mettre un lien personnalisé, je veux dire un lien que personne ne connait.

Un lien que personne ne connait encore, c'est un nouveau lien.
1) Ajouter un lien à la table web et récupérer l'identifiant du nouveau lien.
2) Ajouter l'association entre le nouveau lien et l'utilisateur qui a mis ce nouveau lien.

Citation:

et sinon pour ce qui est de mon schéma en haut il est correct ?

Tu veux parler de ça ?

Citation:

web ( web_id, web_nom, web_lien, web_description )
[PK]web_id

membre ( me_id, me_nom )
[PK]me_id

linked ( web_id, me_id )
[PK]web_id, me_id

Oui c'est correct. :ccool:

Citation:

aussi bien du on cascade delete que pour la compréhension.

Tu veux parler de ça ?

Citation:

* si un membre supprime son compte ça supprime automatiquement ses liens dans linked. donc mettre ON DELETE CASCADE DANS linked

Oui c'est correct. :ccool:
18/02/2010, 12h59
noobyyy

Merci,

Citation:

Envoyé par CinePhil

Un lien que personne ne connait encore, c'est un nouveau lien.
1) Ajouter un lien à la table web et récupérer l'identifiant du nouveau lien.
2) Ajouter l'association entre le nouveau lien et l'utilisateur qui a mis ce nouveau lien.

euh... je ne vois pas comment faire... peux tu me montrer ? car si je rajoute un lien dans web qui va correspondre à un membre il faut que je fasse comme dans linked rajouter on delete cascade ? mais ceux qui sont mis par défaut par moi enfin je ne comprends pas. :calim2:
18/02/2010, 18h20
CinePhil

ON DELETE CASCADE n'est écrit qu'à la création de la clé étrangère, pas à chaque ajout dans la table !

Pas le temps de détailler maintenant mais :

Citation:

1) Ajouter un lien à la table web et récupérer l'identifiant du nouveau lien.

C'est une requete INSERT.
Ton langage de programmation doit etre capable de récupérer l'ID de la ligne ajoutée. En PHP c'est mysql_insert_id.

Citation:

2) Ajouter l'association entre le nouveau lien et l'utilisateur qui a mis ce nouveau lien.

C'est encore une requete INSERT.
18/02/2010, 20h44
noobyyy

je comprends ce que tu veux dire mais le problème c'est que si je supprime un membre son lien personnalisé restera dans la table web ! a moins de faire 2 requêtes bien sur...