Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2009
    Posts
    24

    Question Unanswered: How to correctly use ON CASCADE DELETE?

    I've got 3 tables employing a many - many relationship.

    Table A > Table B > Table C

    If i delete an entry in table A, any linked entries in B & C must be deleted also.

    How can i achieve that?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the first step is to understand that it's ON DELETE CASCADE, not ON CASCADE DELETE



    it is accomplished when you declare the foreign keys

    could you do a SHOW CREATE TABLE for each table please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2009
    Posts
    24
    Code:
    CREATE TABLE A (
    	tablea_id INT auto_increment, 
    	field_1 VARCHAR(30) NOT NULL, 
    	field_2 INT NOT NULL, 
    	field_3 INT NOT NULL, 
    	field_4 VARCHAR(30) default NULL, 
    	PRIMARY KEY (tablea_id)) ENGINE=INNODB;
    
    
    CREATE TABLE C (
    	tablec_id INT auto_increment, 
    	text_field TEXT, 
    	text_field2 TEXT, 
    	time TIME NOT NULL,
    	time2 TIME NOT NULL,
    	field VARCHAR(30),
    	PRIMARY KEY (tablec_id)
    	) ENGINE=INNODB;
    
    
    CREATE TABLE B (
    	a_id INT, 
    	c_id INT,
    	PRIMARY KEY (a_id, c_id),
    	INDEX (a_id),
    	INDEX (c_id),
    	FOREIGN KEY (a_id) REFERENCES A (tablea_id),
    	FOREIGN KEY (c_id) REFERENCES C (tablec_id)) ENGINE=INNODB;

    Where should i put ON DELETE CASCADE?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Theresonly1 View Post
    Where should i put ON DELETE CASCADE?
    in the FOREIGN KEY clauses

    by the way, slight correction to your diagram, it should be --

    Table A > Table B < Table C




    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2009
    Posts
    24
    So would it be like this:

    Code:
    FOREIGN KEY (a_id) REFERENCES A (tablea_id) ON DELETE CASCADE,
    FOREIGN KEY (c_id) REFERENCES C (tablec_id) ON DELETE CASCADE) ENGINE=INNODB;
    I would need to apply it to both foreign key references dont i?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Theresonly1 View Post
    So would it be like this:
    yes, it would
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2009
    Posts
    24
    Hm.. That didnt work really well though.

    It did delete the entry in table A and in table B but not in Table C. Any ideas why?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Theresonly1 View Post
    Any ideas why?
    yes, because it shouldn't

    suppose you had orders and items, a many-to-many relationship

    and suppose you delete order #1234 because the customer cancelled it or whatever

    you ~do~ want to delete the row for the order (table A) and also all the order_item rows belonging to that order (table B) but you do ~not~ want the products themselves deleted (table C)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2009
    Posts
    24
    For my scenario i actually do want entry in table C to be deleted. Because c only exists in the lifetime of a. So if a goes, so does c.

    How can i achieve my scenario. It has to be many -many relationship.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you'll need to write a trigger
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2009
    Posts
    24
    that could do i think.

    But i think i need to refactor my database design. might not need a many - many relationhip.

    But anyway, thanks for your help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •