Laravel 5.4 SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0' Live website

Question

I have a product tables and product_galleries table, that are connect together through has many relation, the issue appears when I try to add a new product from the back-end, PS: The error only appears in my live website, in my local environment the back-end works fine and can I add as many product as I want.

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0' for key 'PRIMARY' (SQL: insert into `products` (`product_name`, `product_description`, `product_preview`, `category_id`, `color_id`, `size_id`, `material_id`, `fantasia_id`, `slug`, `updated_at`, `created_at`) values (Test 2, , 1499770479-Petronius0039.jpg, 1, , , , , test-2, 2017-07-11 10:54:39, 2017-07-11 10:54:39)) 

The product table

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateProductsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('products', function (Blueprint $table) {
            $table->increments('id');
            $table->string('product_name');
            $table->string('product_preview')->nullable();
            $table->text('product_description')->nullable();
            $table->integer('category_id')->nullable()->unsigned();
            $table->integer('color_id')->nullable()->unsigned();
            $table->integer('material_id')->nullable()->unsigned();
            $table->integer('size_id')->nullable()->unsigned();
            $table->integer('model_id')->nullable()->unsigned();
            $table->integer('fantasia_id')->nullable()->unsigned();
            $table->string('slug');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('products');
    }
}

product_galleries table

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateProductGalleriesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('product_galleries', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('product_id')->unsigned();
            $table->foreign('product_id')->references('id')->on('products')->onDelete('cascade');
            $table->string('product_images')->nullable();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('product_galleries');
    }
}

product model

<?php

namespace App;

use Cviebrock\EloquentSluggable\Sluggable;
use Illuminate\Database\Eloquent\Model;
use App\Category;
use App\Color;
use App\Size;
use App\Material;
use App\Fantasia;
use App\ProductGallery;

class Product extends Model
{
    use Sluggable;
    protected $guarded = ['id'];
    protected $table = 'products';
    public function sluggable()
    {
        return [
            'slug' => [
                'source' => 'product_name'
            ]
        ];
    }

    public function categories(){
      return $this->belongsTo(Category::class, 'category_id');
    }
    /*Connect the product table with the image table as one product can have many images*/
    public function images(){
      return $this->belongsToMany(Image::class , 'image_product');
    }

    public function productgalleries(){
      return $this->hasMany(ProductGallery::class);
    }

    public function colors(){
      return $this->belongsTo(Color::class, 'color_id');
    }

    public function fantasias(){
      return $this->belongsTo(Fantasia::class, 'fantasia_id');
    }

    public function materials(){
      return $this->belongsTo(Material::class , 'material_id');
    }

    public function sizes(){
      return $this->belongsTo(Size::class, 'size_id');
    }

    public function newProducts(){
      return $this->hasOne(Size::class, 'size_id');
    }
}

Product gallery model

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use App\Product;

class ProductGallery extends Model
{
  protected $table = 'product_galleries';
  protected $fillable = [
   'product_id',
   'product_images'
  ];
    public function products(){
       return $this->belongsTo(Product::class, 'product_id');
   }
}

Product controller

<?php

namespace App\Http\Controllers;

use App\Product;
use App\ProductGallery;
use App\Category;
use App\Color;
use App\Image;
use App\Size;
use App\Material;
use App\Fantasia;
use App\Productgalleries;
use DB;
use File;
use Illuminate\Support\Facades\Input;
use Illuminate\Http\Request;
use App\Http\Requests\UploadRequest;

class ProductsController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
      $products = Product::with('categories', 'colors' , 'sizes', 'materials' , 'fantasias')->get();
      return view('backend.product.product-library', compact('products'));
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create()
    {
        $categories = Category::all();
        $colors = Color::all();
        $sizes = Size::all();
        $materials = Material::all();
        $fantasias = Fantasia::all();
        return view('backend.product.product-create', compact('categories','colors','sizes', 'materials', 'fantasias'));
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(UploadRequest $request)
    {
        $product = new Product();
        $product->product_name = $request->product_name;
        $product->product_description = $request->product_description;
        if($request->hasFile('product_preview')) {
          $file = Input::file('product_preview');
          $filename = time(). '-' .$file->getClientOriginalName();
          $product->product_preview = $filename;
          $file->move(public_path().'/images/product-feature', $filename);
        }
        $product->category_id = $request->category_id;
        $product->color_id = $request->color_id;
        $product->size_id = $request->size_id;
        $product->material_id = $request->material_id;
        $product->fantasia_id = $request->fantasia_id;
        $product->save();


        if($request->hasFile('images')) {
          $photos = Input::file('images');
          $file_count = count($photos);
          $uploadcount = 0;
          foreach($photos as $photo){
          $photoname = time(). '-' .$photo->getClientOriginalName();
          $photo->move(public_path().'/images/product-gallery', $photoname);
          $uploadcount ++;
           $productgallery = new ProductGallery();
           $productgallery->product_images = $photoname;
           $productgallery->product_id = $product->id; // Save it to the newly created product
           $productgallery->products()->associate($product);
           $productgallery->save();
        }
      }
      if($uploadcount == $file_count){
        return $this->create()->with('success', 'Uploaded Successfully');
      }
      else{
          return $this->create()->with('success', 'Uploaded fail');
      }

    }
    /**
     * Display the specified resource.
     *
     * @param  \App\Product  $product
     * @return \Illuminate\Http\Response
     */
    public function show(Product $product)
    {
        //
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  \App\Product  $product
     * @return \Illuminate\Http\Response
     */
    public function edit(Product $product)
    {
        $categories = Category::all();
        $cats = array();
        foreach ($categories as $category) {
        $cats[$category->id] = $category->category_name;
      }

        $colors = Color::all();
        $col = array();
        foreach ($colors as $color) {
        $col[$color->id] = $color->color_name;
      }

        $materials = Material::all();
        $mat = array();
        foreach ($materials as $material) {
        $mat[$material->id] = $material->material_type;
      }

        $sizes = Size::all();
        $si = array();
        foreach ($sizes as $size) {
        $si[$size->id] = $size->size_name;
      }

        $fantasias = Fantasia::all();
        $fant = array();
        foreach ($fantasias as $fantasia) {
        $fant[$fantasia->id] = $fantasia->fantasia_name;
      }

        if(!$product){
          return redirect('backend.dashboard')->with(['fail'=>'post not found']);
        }
        return view('backend.product.product-edit',compact('product', 'cats' , 'col' , 'mat', 'si', 'fant'));
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  \App\Product  $product
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, Product $product)
    {
      $this->validate($request, [
        'product_name'=>'required|max:120',
        'product_preview' => 'required|file|image|mimes:jpeg,png,jpg,gif,svg',
      ]);

      $product->product_name = $request->product_name;
      $product->product_description = $request->product_description;
      if($request->hasFile('product_preview')) {
        $file = Input::file('product_preview');
        $filename = time(). '-' .$file->getClientOriginalName();
        $file->move(public_path().'/images/product-feature', $filename);
        $oldfile = $product->product_preview;
        $product->product_preview = $filename;
        $oldfiledelete = File::delete(public_path().'/images/product-feature', $oldfile);
      }
      $product->category_id = $request->category_id;
      $product->color_id = $request->color_id;
      $product->size_id = $request->size_id;
      $product->material_id = $request->material_id;
      $product->fantasia_id = $request->fantasia_id;
      $product->update();
      return Redirect()->route('products.index')->with(['success'=> 'post successfully updated']);
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  \App\Product  $product
     * @return \Illuminate\Http\Response
     */
    public function destroy(Product $product)
    {
        if(!$product){
          return redirect('products.index')->with(['fail'=>'post not found']);
      }
        $product->delete();
        return Redirect()->route('products.index')->with(['success'=> 'post successfully updated']);
    }
}

product-create.blade.php (view)

@extends('layouts.backend-master')

@section('styles')
  <link rel="stylesheet" href="">
@endsection

@section('content')

  @if (count($errors) > 0)
    <div class="alert alert-danger">
      <strong>Whoops!</strong> There were some problems with your input.<br><br>
        <ul>
          @foreach ($errors->all() as $error)
            <li>{{ $error }}</li>
          @endforeach
        </ul>
     </div>
  @endif

  <h1>Add a new product</h1>
  <form action="{{route('products.store')}}" method="post" enctype="multipart/form-data">

    <div class="input-group">
      <label for="product_name">Name of the product</label>
      <input type="text" name="product_name" id="product_name"/>
    </div>

    <div class="input-group">
      <label for="product_description">Product Description</label>
      <textarea type="text" name="product_description" id="product_description" rows="8"></textarea>
    </div>

    <div class="input-group">
      <label for="product_preview">Feature Image:</label>
      <input type="file" name="product_preview" id="file">
    </div>

    <div class="input-group">
      <label for="category_id">Category</label>
      <select name="category_id" id="category_id">
        @foreach($categories as $category)
          <option value="{{ $category->id }}">{{ $category->category_name }}</option>
        @endforeach
      </select>
    </div>

    <div class="input-group">
      <label for="color_id">Color</label>
      <select name="color_id" id="color_id">
        @foreach($colors as $color)
          <option value="{{ $color->id }}">{{ $color->color_name }}</option>
        @endforeach
      </select>
    </div>

    <div class="input-group">
      <label for="size_id">Size</label>
      <select name="size_id" id="size_id">
        <option selected disabled>-</option>
        @foreach($sizes as $size)
          <option value="{{ $size->id }}">{{ $size->size_name }}</option>
        @endforeach
      </select>
    </div>

    <div class="input-group">
      <label for="material_id">Material</label>
      <select name="material_id" id="material_id">
        <option selected disabled>-</option>
        @foreach($materials as $material)
          <option value="{{ $material->id }}">{{ $material->material_type }}</option>
        @endforeach
      </select>
    </div>

    <div class="input-group">
      <label for="fantasia_id">Model</label>
      <select name="fantasia_id" id="fantasia_id">
        <option selected disabled>-</option>
        @foreach($fantasias as $fantasia)
          <option value="{{ $fantasia->id }}">{{ $fantasia->fantasia_name }}</option>
        @endforeach
      </select>
    </div>

    <div class="input-group">
      <label for="images">Product Gallery:</label>
      <input type="file" name="images[]" multiple="true">
    </div>

    <button type="submit" class="btn">Add</button>
    <input type="hidden" name="_token" value="{{Session::token()}}">
  </form>
@endsection

@section('scripts')
@endsection

Thank you in advance

Answer 1

It seems that increments() is not working for you.

You can try unsignedInteger(), as it creates a column with Int type, and also allows you to create it with auto_increment constraint. It is same as increments(), just way around. You should write code as below.

$table->unsignedInteger('id', true);

Here unsignedInteger() takes 1st argument as string for Name of Column, and 2nd argument as boolean for auto_increment. Try this out, and let us know if it works.