asked on

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

Avinash Zala

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

sahanlak

ASKER

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 ?

k_murli_krishna

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.

ASKER CERTIFIED SOLUTION

k_murli_krishna

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial