[On delete Cascade] Demande de conseils

09 mai 2007, 16:33

Bonjour,

J'explique mon cas de figure :

J'ai une table Commune(identifiée par le codeINSEE) et une autre table Pompier(identifiée par un ID auto_increment).
L'id de la table Pompier est une clée étrangère de Commune, ce que je veux c'est que lorsque je supprime une caserne de pompier le champ idpompier dans commune soit effacé sans pour autant supprimer la commune.

A l'inverse j'ai une autre table Hôtel(identifiée par un id auto_increment)qui a comme clée étrangère le codeINSEE de Commune.
Je veux que lorsque je supprime une Commune, "codeINSEE" de Hôtel soit supprimé/effacé sans que l'Hotel lui même soit supprimé.

Il me semble qu'il faut créer les tables avec ON DELETE CASCADE et ON UPDATE CASCADE.
Or je me demande si avec ON DELETE CASCADE lorsque je vais supprimer un commune va t-il me supprimer l'Hotel correspondant ou lorsque je supprime une caserne de Pompier va t-il me supprimer la Commune associée ou juste les champs associés ?
Pour mes deux exemples sur quelles tables dois je placer mes ON DELETE CASCADE ?

Merci pour votre aide.

09 mai 2007, 16:41

Les conditions ON DELETE se font sur la contrainte de Foreign Key, ce qui devrait déjà répondre à ta question, à savoir sur quelle table tu dois les jouer

En effet, si une table "fille" a une contrainte d'intégrité vers une autre table "mère", c'est sur la table fille que l'on va ajouter la contrainte et spécifier l'action en cas de suppression du "parent"

La commande ON DELETE CASCADE va faire que si l'enregistrement parent est supprimé, alors l'enregistrement fils est effacé en cascade.

Si tu ne veux pas supprimer l'enregistrement fils lorsque le parent est supprimé, il faut utiliser ON DELETE SET NULL pour supprimer l'id qui n'existera plus dans l'enregistrement fils

Ainsi avec un CASCADE sur la Caserne et un SET NULL sur l'Hotel, lors de la suppression de la commune, la Caserne sera supprimée en revanche l'Hotel sera conservé et son id_commune mis à null.

09 mai 2007, 16:45

En ce qui concerne le placement des ON DELETE j'ai parlé trop vite, tout du moins j'aurais pu réfléchir d'avantage :c.
En revanche tu viens je pense de me donner la solution avec le ON DELETE SET NULL.

En effet même si je supprime une Commune je ne veux pas supprimer la caserne de Pompier, je veux juste que l'idpompier dans Commune soit mis à NULL, il faut donc que je mette ON DELETE SET NULL ?!

Merci de votre aide.

09 mai 2007, 17:39

J'ai un peu de mal à saisir la structure de tes tables et les liens entre elles, mais ça doit être ça

En gros le SET NULL à pour effet de mettre à null le champ qui contient la foreign key de la table "fille" lorsque l'élément de référence est supprimé :

TABLE_1 : id_table_1, ...
TABLE_2 : id_table_2, fk_table_1, ...

Si je fais une contrainte de type foreign key de la TABLE_2 vers TABLE_1 sur le champ fk_table_1 puis que je supprime des enregistrements dans table_1, alors :
- ON DELETE CASCADE : delete tous les enregistrements de TABLE_2 pour lesquels fk_table_1 est égal aux id_table_1 des enregistrements qui ont été supprimés.
- ON DELETE SET NULL : update tous les enregistrements de TABLE_1 pour lesquels fk_table_1 est égal aux id_table_1 des enregistrements qui ont été supprimés en donnant la valeur NULL au champ fk_table_1

HTH

10 mai 2007, 08:16

Oui ce que je veux c'est que seul le champ fk_table_1 (correspondant à l'id_table1 qui a été supprimé) soit mis à NULL sans pour autant supprimer toute la ligne.
Un déréférencement pour ne pas que par la suite fk_table_1 pointe sur n'importe quoi !

10 mai 2007, 11:29

C'est donc bien ça

Code : Tout sélectionner

ALTER TABLE table_2
    ADD FOREIGN KEY (fk_table_1)
    REFERENCES table_1 (id_table_1)
    ON DELETE SET NULL

11 mai 2007, 09:14

C'est donc bien ça

Code : Tout sélectionner
ALTER TABLE table_2 ADD FOREIGN KEY (fk_table_1) REFERENCES table_1 (id_table_1) ON DELETE SET NULL

Ouais mais je refais la bd en y intégrant directement c'est plus mieux bien je trouve, et sutout c'est propre et ça ne me coûte rien.
Voilà mon code générer avec dbdesigner :

Code : Tout sélectionner

  PRIMARY KEY(codeINSEE),
  INDEX Commune_FKIndex1(IDcis)

Puis je ajouter le on delete cascade juste derrière ou il faut que je change leur fameux index en foreign key references ?!

Merci pour tout.

11 mai 2007, 11:14

J'ai fait ma BD mais le on delete set null ne marche pas.

Voici par exemple pour ma classe commune le code que j'entre dans mysql pour créer la table :

Code : Tout sélectionner

CREATE TABLE `commune` (
  `codeINSEE` int(10) unsigned NOT NULL auto_increment,
  `IDSSP` int(10) unsigned NOT NULL default '0',
  `idCartographie` int(10) unsigned NOT NULL default '0',
  `IDcis` int(10) unsigned NOT NULL default '0',
  `codePostal` int(10) unsigned default NULL,
  `nom_commune` varchar(45) default NULL,
  `canton` varchar(45) default NULL,
  `arrondissement` varchar(45) default NULL,
  `nb_habitants` int(10) unsigned default NULL,
  `mail_commune` varchar(45) default NULL,
  `nom_maire` varchar(45) default NULL,
  PRIMARY KEY  (`codeINSEE`),
  KEY `Commune_FKIndex1` (`IDcis`),
  KEY `Commune_FKIndex2` (`idCartographie`),
  KEY `Commune_FKIndex3` (`IDSSP`),
  FOREIGN KEY ( `IDcis` ) REFERENCES centre_intervention_securite ( `IDcis` ) ON DELETE SET NULL, 
  FOREIGN KEY ( `idCartographie` ) REFERENCES cartographie ( `idCartographie` ) ON DELETE SET NULL, 
  FOREIGN KEY ( `IDSSP` ) REFERENCES service_securite_publique ( `IDSSP` ) ON DELETE SET NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Voici ce que j'obtiens lorsque j'exporte depuis mysql :

Code : Tout sélectionner

-- 
-- Structure de la table `commune`
-- 

CREATE TABLE `commune` (
  `codeINSEE` int(10) unsigned NOT NULL auto_increment,
  `IDSSP` int(10) unsigned NOT NULL default '0',
  `idCartographie` int(10) unsigned NOT NULL default '0',
  `IDcis` int(10) unsigned NOT NULL default '0',
  `codePostal` int(10) unsigned default NULL,
  `nom_commune` varchar(45) default NULL,
  `canton` varchar(45) default NULL,
  `arrondissement` varchar(45) default NULL,
  `nb_habitants` int(10) unsigned default NULL,
  `mail_commune` varchar(45) default NULL,
  `nom_maire` varchar(45) default NULL,
  PRIMARY KEY  (`codeINSEE`),
  KEY `Commune_FKIndex1` (`IDcis`),
  KEY `Commune_FKIndex2` (`idCartographie`),
  KEY `Commune_FKIndex3` (`IDSSP`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=39101 ;

Où sont passé mes foreign key et mes references ???

J'ai l'impression qu'il ne me les prend pas en compte.
Je tiens à dire que ma bd marche niquel juste le fait qu'il me prend pas en compte les on delete set null ...
Par exemple à ma commune je lui affecte un SSP donc IDSSP = 1 de commune passe à 1, lorsque je supprime le SSP j'aimerais que IDSSP repasse à 0 ou NULL, mais non il reste à 1 malgrès que le SSP ait été supprimé.

Je suis en utf8_general_ci dans mysql 4.19 (easyPHP 1.8).

Merci de votre aide.

11 mai 2007, 14:27

Je précise qu'il me dit que mes requêtes sql ont été insérré avec succès mais pourtant quand je test ça ne marche pas =/
Il ne met pas à null la clé étrangère lors d'une suppression de l'élément référencé!!

Quand j'essaye d'exporter la bd que j'avais au préalable créée avec des foreign key/references/on delete set null il n'y a plus rien juste le Key correspondant à l'index.
Comment dois je m'y prendre pour qu'il me prenne en compte mes contraintes ?

Merci

11 mai 2007, 16:12

Bon je fais du monologue mais j'ai trouvé une partie de la solution.
Il faut absolument que la BD soit de type InnoDB ... vous le savez sûrement déjà tous ici mais moi "putin de bordel" bah je ne le savais pas XD

11 mai 2007, 16:36

Je vous rassure j'ai trouvé.
Merci de votre aide. Non je déconne faut pas pousser non plus

[On delete Cascade] Demande de conseils

Qui est en ligne