eve_mf eve_mf - 18 days ago 6
MySQL Question

mysql PDO ON DELETE CASCADE - not working

I have seen my question may already have an answer, and "in mysql, on delete cascade not working" seems to be more similar..... but I can't see anything advised on that post working for me.

The problem is, when I delete a recipe, I want that its attachment gets also deleted (well, step by step, at the moment I am just trying to remove it from mysql table, not from the folder where it is stored).

After I post here a similar question but regarding how to create mysql trigger, I set the foreign key, and on delete cascade, so I though, when a recipe gets delete, the attachment as well, but it happens absolutely nothing to the attachment.... What am I doing wrong?

Next to each recipe I have a button to delete it:

echo '<a class="toLink" href="delete_recipe.php?id=' . $recipe['id'] . '" title="delete recipe" onclick="return confirm(\'Are you sure you want to delete this recipe?\');">Delete recipe</a>';


In delete_recipe.php:

<?php require 'includes/functions.php';

$recipeId = filter_input(INPUT_GET, 'id', FILTER_SANITIZE_NUMBER_INT);
if(delete_recipe($recipeId) == true) {
header('Location: index.php');
exit;
} else {
$error_message = "Could not delete recipe";
}


In functions.php:

function delete_recipe($recipe_id = ':recipe_id') {
include 'db_connection.php';
try {
$sql = "DELETE FROM recipes ";
$sql .= "WHERE id =:recipe_id ";
$sql .= "LIMIT 1";

$results = $conn->prepare($sql);
$results->bindParam(':recipe_id', $recipe_id, PDO::PARAM_INT);

if($results->execute()) {
echo '1 row has been removed';
}

$conn = null;

} catch(PDOException $e) {
echo 'Error: ' . $e->getMessage() . '<br />';
return false;
}

return true;
}


I think I have set the foreign key and "delete on cascade" properly..... if I do:

show create table recipes:

| recipes | CREATE TABLE `recipes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`attachment_id` int(11) NOT NULL,
`chef_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_recipes_attachments1_idx` (`attachment_id`),
KEY `fk_recipes_chefs1_idx` (`chef_id`),
CONSTRAINT `fk_recipes_attachments1` FOREIGN KEY (`attachment_id`) REFERENCES `attachments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_recipes_chefs1` FOREIGN KEY (`chef_id`) REFERENCES `chefs` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 |


show create table attachments:

| attachments | CREATE TABLE `attachments` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`attachment_path` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 |


Any idea why it does not remove the attachment from the attachments table that belongs to the recipe I am deleting?

Thank you

Answer

You have your foreign key relationship backwards. The attachments table should have a recipe_id column, and that should be a foreign key to recipes.

CREATE TABLE `attachments` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `attachment_path` varchar(255) NOT NULL,
    `recipe_id` INT(11),
    PRIMARY KEY (`id`),
    FOREIGN KEY (`recipe_id`) REFERENCES `recipe` (`id`) ON DELETE CASCADE
);

The way you did it, deleting an attachment would delete the recipe.

Comments