How to avoid duplicate entries in pivot table in Laravel

• Create pivot table with composite key in Laravel

  Run migration command to create pivot table

  php artisan make:migration create_project_user_table --create=project_user
  

  Add columns to migration file

  public function up()
  {
      Schema::create('project_user', function (Blueprint $table) {
          $table->foreignId('user_id')->constrained();
          $table->foreignId('project_id')->constrained();
          $table->boolean('is_manager')->default(false);
          $table->primary(['user_id', 'project_id']);
          $table->timestamps();
      });
  }
  

  Run migration command

  php artisan migrate
  

  Create projects() method with belongsToMany Relationship in - App\Models\User.php .

  // App\Models\User.php
  public function projects(){
      return $this->belongsToMany('App\Models\Project')
                  ->withPivot('is_manager')
                  ->withTimestamps();
  }
  

  Create a contoller and add the below method in it.

  app\Http\Controllers<YourController>.php

  Code under controller’s method

  $user = User::findOrFail(2);
  $projectIds = array('1', '2');
  try{
      $user->projects()->attach($projectIds, ['is_manager'=>1]);
  }catch(\Exception $e){
      return $e->getMessage();
  }
  

  This code snippet will insert the value in the pivot table if the value of both column user_id and project_id is unique else it will give SQL error SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-2' for key 'PRIMARY'. So it avoids duplicate data entry in a table with a combination of user_id and project_id columns.

  In the Pivot table, project_id and user_id columns have been set as the primary keys so there cannot be duplicates with both the same user_id and project_id.

   

  To create project model and table structure of project table visit here - Create project table with model and migration .

  If you want to get code snippets to create pivot table for projects and users tables you can visit here -  create pivot table for projects and users table  .

• Create if not exists and update record if exists in pivot table with extra column using attach and updateExistingPivot method in Laravel

  --PATH routes\web.php

  Route::get('/save-or-update-pivot-table-record', function(){
      $user = App\Models\User::find(1);
      $project = App\Models\Project::find(1);
  
      if (!$user->projects->contains($project->id)) {
          $user->projects()->attach($project->id, ['is_manager'=> 1]) ;
      }else{
          $user->projects()->updateExistingPivot($project->id, ['is_manager'=> 0]);
      }
  });
  

  This code snippet will help you to create a record in pivot table with extra column if record is not already exists in it and update record if exists in pivot table.

   

  Projects method with belongsToMany relation

  app\Models\User.php

  public function projects(){
      return $this->belongsToMany('App\Models\Project')
                  ->withPivot('is_manager')
                  ->withTimestamps();
  }

• Create if not exists and update record if exists in pivot table with additional column using sync method

  --PATH routes\web.php

  Route::get('/save-or-update-pivot-table-record', function(){
      $user = App\Models\User::find(1);
      $project = App\Models\Project::find(1);
      $user->projects()->sync([$project->id => ['is_manager'=> 1]], false);
  });
  

  This code snippet will help you to create a new record if the record is not already exist in pivot table else it will update the existing record from table.

Related Queries

• How to create pivot table in laravel using migration
• Laravel create table migration with model

Back to code snippet queries related laravel