I can't delete my image when it is liked because of the foreign key in mysql

Question

There is the schema of my db :

And I've got this error message when I try to delete a picture :

SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`test`.`scores`, CONSTRAINT `scores_ibfk_2` FOREIGN KEY (`picture_id`) REFERENCES `pictures` (`id`)) in /Applications/MAMP/htdocs/legogram/app/model/model.php:173

When my image is not liked, I can delete it but when it is liked, I can not

Here is the function to list my images :

    public function listPictures_session()
{
    $this->open();
    $stmt = $this->pdo->prepare("SELECT pictures.id, pictures.owner_id, pictures.title, pictures.description, pictures.path,
    SUM(scores.score)/COUNT(scores.id) AS 'score'
    FROM pictures LEFT OUTER JOIN scores ON pictures.id=scores.picture_id 
    GROUP BY pictures.id
    ORDER BY pictures.id DESC LIMIT 100");
    $stmt->execute();
    return $stmt->fetchAll();
}

And how I call it in my view :

    $pictures = $this->model->listPictures_session(); 
foreach ($pictures as $picture) { 
if ($picture["owner_id"] == $_SESSION["id"]) { 
echo picture["id"] }"

But I do not select scores.id in the select of my listPictures_session function. So I tried to add a SELECT scores.id, So I can delete it in first place. But when I try to add scores.id to the SELECT I have a GROUP BY error.

I was adding the scores.id this way but it was not working :

    SELECT pictures.id, pictures.owner_id, pictures.title, pictures.description, pictures.path,
    SUM(scores.score)/COUNT(scores.id) AS 'score', scores.id

And the function to delete it :

    public function deletepicture($user)
{
    $this->open();
    $stmt = $this->pdo->prepare("DELETE FROM pictures WHERE id=:id");
    return $stmt->execute(array(":id" => $user["id"]));
}

How can I fix this problem so I can delete my image? Thank you very much and have a nice day

Answer 1

The way I see it, what you actually want to do is delete a row from the pictures table. You have a foreign key constraint in scores on scores.picture_id that references pictures.id. These are good things.

Your error message is as follows:

SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`test`.`scores`, CONSTRAINT `scores_ibfk_2` FOREIGN KEY (`picture_id`) REFERENCES `pictures` (`id`)) in /Applications/MAMP/htdocs/legogram/app/model/model.php:173

which means that this foreign key constraint is being violated when trying to delete the row in the pictures table. This is caused by one or more rows in the scores table with a picture_id that reference pictures.id. You cannot delete the row in pictures without eliminating all the references to it. You can either set the reference to null if the referencing column allows null, or you could delete the row altogether. From what I can tell, this table is used to store users' ratings of other users' pictures, and rows in this table don't make sense without the picture ID it references, so your best approach here is probably to delete the offending rows altogether. You can do this as a part of the delete process:

public function deletepicture($user) {
    $this->open();
    $stmt = $this->pdo->prepare(
            <<<SQL
            DELETE FROM
                scores
            WHERE
                picture_id = :pictureId
            SQL
        );
    $stmt->execute([
            ":pictureId" => $user["id"]
        ]);
    $stmt = $this->pdo->prepare(
            <<<SQL
            DELETE FROM
                pictures
            WHERE
                id = :id
            SQL
        );
    return $stmt->execute([
            ":id" => $user["id"]
        ]);
}

Answer 2

foreign key of table (`test`.`scores`) reference to table pictures

you must delete records of (`test`.`scores`) reference to pictures before delete record of pictures

Answer 3

I don't think your GROUP BY error is relevant to your actual issue.

Basically if you have rows in scores which refer to a specific picture ID, then you must delete all rows from scores which contain that picture ID before you can delete the related row in pictures.

e.g.

DELETE FROM scores WHERE picture_id = :id

DELETE FROM pictures WHERE id = :id