How to DELETE from Many to Many Realationship tables in PHP

Question

So here is my table look like

Table : movies

#   Name    Type
1   movie_id Pk int(11)         
2   movie_name  text

Table : genres

#   Name    Type
1   genre_id Pk int(11)         
2   genres_name text

Table : movie_genres

#   Name    Type
1   id Pk   int(11)
2   movie_id Index  int(11) 
3   genres_id Index int(11) 

So how can I delete a row in movie so that its value from genres and movie_genres also delete automatically.

When i try to delete the row from phpmyadmin its showing error : #1451 - Cannot delete or update a parent row: a foreign key constraint fails (123movies.movie_actors, CONSTRAINT movie_actors_ibfk_1 FOREIGN KEY (movie_id) REFERENCES movies (movie_id))

Please help me out! how to do it in proper way! (in PHP)

Answer 1

First of all you shouldn't delete the records from genres table because the values are or will be used for other movies.

The error you get is because you have another table one to many (movie_actors) with a foreign key to prevent the deletion of the movie record, you'll have to delete the movie actors from that table first to be able to delete a movie Solution : Change the foreign key constraint on the movie_actor table to on delete/update to cascade (now it is restrict)

Add a Foriegn key on the genres table to Cascade on movie delete:

ALTER TABLE movie_genres
ADD CONSTRAINT FK_movgen
FOREIGN KEY (`movie_id`) REFERENCES `movies` (`movie_id`) ON DELETE CASCADE ON UPDATE CASCADE;