-3

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)

GMB
  • 216,147
  • 25
  • 84
  • 135
Watch-Online.in
  • 13
  • 3
  • 6
  • The error seems to be nothing to do with the tables you've shown. It's complaining that you have a row in movie_actors which references the movies you're trying to delete. So you must delete that row from movie_actors before you can continue – ADyson Oct 04 '20 at 10:22
  • Check out ```on delete``` and ```cascade```/```set null``` in MySQL. This is something best handled by the database anyway. – Kyrre Oct 04 '20 at 10:26

1 Answers1

2

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;
L Q
  • 81
  • 4