Laravel 5.1 QueryException when trying to delete a project
I am building something that allows users to upload their graphical work and other users can comments on the projects and like other projects.
Now when a user wants to delete their own project it works but as soon as the project has comments or likes I get this error:
Integrity constraint violation: 1451
Cannot delete or update a parent row: a foreign key constraint fails
(`scotchbox`.`comments`, CONSTRAINT `comments_on_projects_foreign` FOREIGN KEY (`on_projects`)
REFERENCES `projects` (`id`)) (SQL: delete from `projects`
where `id` = 31 and `user_id` = 32)
I assume this can be resolved by deleting the comments from the comments table and the likes from the likes table aswell? but I honestly have no idea how to fix this issue with the foreign keys.
I think I need a way to delete the comments and likes of the project before deleting the project itself. Is this possible from the destroy function of my ProjectsController?
This is my destroy function for deleting the projects:
public function destroy($id)
{
$input = Request::all();
Project::whereId($id)->whereUserId(Auth::user()->id)->delete();
return redirect('projects/');
}
Answer
When you have a foreign key, you can choose the behavior of deletion.
You have: Users, Projects
User has many Projects
so that means in the Projects schema you will have something similar to this
Schema::create('projects', function(Blueprint $table){
// table fields
$table->integer('user_id')->unsigned();
$table->foreign('user_id')->references('id')->on('users');
});
In this case, you have established a relation without saying "how to behave" if a deletion occurs on the parent (User), that means if you attempt to delete the user, the query exception will be thrown to protect you.
So, if this is the case, you will have to find all the children (Projects) of that parent (User), and delete them before you are able to delete the Parent.
the 2nd solution would be adding onDelete() behavior:
Schema::create('projects', function(Blueprint $table){
// table fields
$table->integer('user_id')->unsigned();
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
});
This means, if you delete the Parent (User) it will automatically find all of the children (Projects) and delete them one by one for you.
Finally, please note that i am using Parent Children analogy in order to clarify the situation, where this analogy only fits in the One to Many relations.
Please avoid using the analogy when you are surrounded by Databasists to protect yourself from further consequences.
Related Questions
- → "failed to open stream" error when executing "migrate:make"
- → October CMS Plugin Routes.php not registering
- → OctoberCMS Migrate Table
- → OctoberCMS Rain User plugin not working or redirecting
- → October CMS Custom Mail Layout
- → October CMS - How to correctly route
- → October CMS create a multi select Form field
- → October CMS - Conditionally Load a Different Page
- → How to disable assets combining on development in OctoberCMS
- → October CMS - Radio Button Ajax Click Twice in a Row Causes Content to disappear
- → OctoberCms component: How to display all ID(items) instead of sorting only one ID?
- → In OctoberCMS how do you find the hint path?
- → How to register middlewares in OctoberCMS plugin?