Undesirable delete in a many to many relationship in MySql

Question

There is a similare question here but It didn't help

I have a classic many to many relationship between Organisme | Organisme_groupe | Groupe.

I want to keep Organisme when deleting Groupe and vice-versa. Actually if I delete Organisme, the attached Groupe is deleted also.

I'am little confused, because from what I understand, the cascade action wouldn't go farther than the child table (which is the intermediate table here).

Organisme

  CREATE TABLE IF NOT EXISTS `organisme` (
  `ORGANISME_ID` int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,  
  `TYPE_ORGANISME` varchar(128) NOT NULL DEFAULT '',
  `NAME` varchar(80) DEFAULT ''  
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Groupe

CREATE TABLE IF NOT EXISTS `groupe` (
  `GROUPE_ID` int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  `NAME_GROUPE` varchar(50) NOT NULL DEFAULT ''  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Organisme_groupe

 CREATE TABLE IF NOT EXISTS  `organisme_groupe`(
      `ORGANISME_ID` int(10) NOT NULL,
      `GROUPE_ID` int(10) NOT NULL,
       PRIMARY KEY (ORGANISME_ID, GROUPE_ID)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

The constraints

ALTER TABLE `organisme_groupe`
  ADD CONSTRAINT `FK_organisme_groupe_1` FOREIGN KEY (`ORGANISME_ID`) REFERENCES `organisme` (`ORGANISME_ID`)  ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `FK_organisme_groupe_2` FOREIGN KEY (`GROUPE_ID`) REFERENCES `groupe` (`GROUPE_ID`)  ON DELETE CASCADE ON UPDATE CASCADE;

Do you mean: When delete `organisme`, delete `organisme_groupe`, but keep `groupe`. When delete `groupe`, delete `organisme_groupe`, but keep `organisme`. — mikespook, Sep 13 '12 at 01:35
Your understanding is correct. Your schema definition is correct. This should work as you've stated, identical to the situation in the question to which you've linked. Can you show us the query deleting an Organisme which is also deleting some Groupes? — aib, Sep 13 '12 at 01:37
@JohnWoo but the intermediate table would keep ids of deleted organismes/groupes. — Momo, Sep 13 '12 at 01:39
@Moh: You're contradicting yourself. Is mikespook entirely correct or not? Could you specify the behavior you want and the behavior you see in the same form as mikespook's comment? — aib, Sep 13 '12 at 01:43
@aib when I checked the code that delete Organisme I stumbled upon a Cascade annotation from hibernate, when it's removed, it worked as I wanted... I didn't know actually that Hibernate can override MySql setting... I should also update the tags — Momo, Sep 13 '12 at 01:49

score 0 · Accepted Answer · answered Sep 18 '12 at 14:58

0

MySql mapping was coorect, the problem was that I was adding the Cascading annotation in the Hibernate model which seems to override the MySQL constraints

answered Sep 18 '12 at 14:58

Momo

2,471
5
31
52

Undesirable delete in a many to many relationship in MySql

1 Answers1