Error 1452 when trying to set up a stored procedure in MySQL

user_17619256 0
Viewed 50

Trying to set up a stored procedure to fill out linking tables/ fill out the linking tables itself. But when trying to set it up I am getting:

Error Code: 1452 Cannot add or update a child row: a foreign key constraint fails (`movie_rentals`.`linking_personnel`, CONSTRAINT `linking_personnel_ibfk_1` FOREIGN KEY (`movie_id`) REFERENCES `movie` (`movie_id`))

I am not sure on where I need to be setting the foreign key constraints.

The stored procedure is set up as

DROP PROCEDURE IF EXISTS test_link;
DELIMITER //
CREATE PROCEDURE test_link( IN film_title VARCHAR(35), IN first_name VARCHAR(30))
BEGIN
START TRANSACTION;
select movie_id from movie_rentals.movie where film_title=film_title;
select person_id from movie_rentals.personnel where first_name=first_name;
INSERT INTO linking_personnel(movie_id, person_id) VALUES (movie_id, person_id);
COMMIT;
END//
DELIMITER ;

But when trying to call the procedure I get the error and it enters the wrong values into the table anyway.

Thanks.

mysql sql
1 Answers

You need to declare the movie_id and person_id variables, and use SELECT ... INTO variable to assign them. But you can simply use the SELECT query as the source of data in the INSERT rather than using variables.

You also should name your procedure parameters differently from the table columns. Your condition WHERE film_title = film_title is always true because it's comparing the variable with itself, not with the table column.

DROP PROCEDURE IF EXISTS test_link;
DELIMITER //
CREATE PROCEDURE test_link( IN p_film_title VARCHAR(35), IN p_first_name VARCHAR(30))
BEGIN
    START TRANSACTION;
    INSERT INTO linking_personnel (movie_id, person_id)
    SELECT movie_id, person_id
    FROM movie_rentals.movie
    CROSS JOIN movie_rentals.personnel
    WHERE film_title = p_film_title
    AND first_name = p_first_name;
    COMMIT;
END//
DELIMITER ;
user_14918950
Related