Delete series of tables.. FK ?

Hello,

I have 3 tables,

The flow of foreign key is like this

movies >>      links  >>   link_set
id               movie_id        links_id

So I haven't set up foreign keys yet,

When I delete a movie from movies table, I want to delete the links (get from movie_id) and when links get deleted I link_set needs to get deleted (links_id). So can I do this with foreign key ? or do I need to write separate code for each delete job ?

thanks
sahanlakAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Avinash ZalaWeb ExpertCommented:
Check my attached code:

Hope this helps.

Addy
<?php
// For Example
	$id='5';
	
	$firstquery ='select movie_id from links where id="'.$id.'"';
	$rs1 = mysql_query($firstquery);
	$movieIDArr = array();
	
	while($data= mysql_fetch_assoc($rs1))
	{
		$movieIDArr[]=$data['movie_id'];
	}
	
	$movieIDlist = implode(',',$movieIDArr);
	
	
	$linkIDArr=array();
	
	$secondQuery ='select links_id from link_set where movie_id IN ('.$movieIDlist.')';
	
	$rs2 = mysql_query($secondQuery);
	
	while($data2= mysql_fetch_assoc($rs2))
	{
		$linkIDArr[]=$data2['links_id'];
	}
	
	$linkSetdeleteQuery = 'delele from link_set WHERE links_id IN ('.$linkIDArr.')';
	mysql_query($linkdeleteQuery);
	
	$LinkDelQuery = 'delete from links WHERE movie_id IN ('.$movieIDArr.')';
	mysql_query($LinkDelQuery);

	$movieDelQuery = 'delete from links WHERE movie_id IN ('.$movieIDArr.')';
	mysql_query($movieDelQuery);
	
	
?>

Open in new window

0
sahanlakAuthor Commented:
Seems your suggesting to delete one by one from each table ? I mean if I delete only the movie, other info on other tables cannot be deleted automatically ? What does the cascade do in FK ?  
0
k_murli_krishnaCommented:
ALTER TABLE links
    ADD [CONSTRAINT [FK_LINKS]] FOREIGN KEY
    (movie_id)
    REFERENCES movies (movie_id)
    [ON DELETE CASCADE]
    [ON UPDATE CASCADE];

ALTER TABLE link_set
    ADD [CONSTRAINT [FK_LINK_SET]] FOREIGN KEY
    (links_id)
    REFERENCES links (links_id)
    [ON DELETE CASCADE]
    [ON UPDATE CASCADE];

Now, when you delete a record in movies table i.e. a movie_id as well, then all corresponding/same movie_id records will be deleted from links table. If you update, then same value gets updated in links as well.

When you delete a record in links table i.e. a links_id as well, then all corresponding/same links_id records will be deleted from link_set table. If you update, then same value gets updated in link_set as well.

All options are:

RESTRICT |            CASCADE |     SET NULL |                               NO ACTION
(disallow/prevent)                      (make all values as NULL)        (nothing happens)

Please refer:
FOREIGN KEY Constraints
http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

Foreign keys are allowed in InnoDB tables only.
0
k_murli_krishnaCommented:
The other way is if ON DELETE CASCADE is not used OR foreign keys are not allowed in MyISAM tables OR FK's are deliberately not used/created, in SQL level, first delete from child, then from parent as a good practice. Code the same way in an application.

Always, good practice is to have a Excel workbook with relations i.e. Parent/Master table - Primary Key - Chile/Detail table - Foreign Key.

Now, one sheet will have everything sorted alphabetically based on parent table, second on child table, third on key columns, fourth having CREATE/INSERT order, fifth having DROP/DELETE order of all tables. Dangling/unreferenced/non-related tables can be marked in green. They will not be there in first three excel sheets.

This, one must prepare and maintain whether foreign keys are used or not. Cheers.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.