| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
11-21-09, 19:50
|
|
Registered User
|
|
Join Date: Nov 2009
Posts: 24
|
|
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?
|
|
11-21-09, 21:15
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,085
|
|
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
|
|
11-22-09, 09:44
|
|
Registered User
|
|
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?
|
|
11-22-09, 09:55
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,085
|
|
Quote:
Originally Posted by Theresonly1
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
|
|
11-22-09, 11:14
|
|
Registered User
|
|
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?
|
|
11-22-09, 15:16
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,085
|
|
Quote:
Originally Posted by Theresonly1
So would it be like this:
|
yes, it would
|
|
11-22-09, 15:55
|
|
Registered User
|
|
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?
|
|
11-22-09, 16:05
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,085
|
|
Quote:
Originally Posted by Theresonly1
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)
|
|
11-22-09, 16:46
|
|
Registered User
|
|
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.
|
|
11-22-09, 17:03
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,085
|
|
you'll need to write a trigger
|
|
11-24-09, 15:46
|
|
Registered User
|
|
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
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
How to correctly use ON CASCADE DELETE?
