You do not want the join table (movie_actor) to have primary keys, because neither column will be unique. The SQL for retrieving information about a specific actor would be like this:
SELECT * FROM actor WHERE name='Brad Pitt';To get all of Brad’s movies in the database (and assuming that his actor ID is “1”), you would need a join, like this:
FROM movie, movie_actor
WHERE movie.id = move_actor.movie_id
AND movie_actor.actor_id = 1;The WHERE clause performs the join (otherwise you get a cartesian product), and the AND clause limits the results to just those movies that starred actor number 1 (Brad Pitt).
Keystone Websites | si-blog