1

I am getting the following error after trying to call firstOrCreate() twice on a Model (ProductItem), representing a Schema with a primary key AND a unique key:

Illuminate\Database\QueryException with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1000' for key 'product_items_item_ref_id_unique' (SQL: insert into product_items (item_ref_id, name, updated_at, created_at) values (1000, 'Item 1', 2017-05-03 19:20:26, 2017-05-03 19:20:26))'

  1. On first run, I would expect that firstOrCreate() would attempt to fetch for an item, otherwise if it does not exist, it creates (inserts) and returns a new one. <- it inserts successfully
  2. If I run it a second time, I assume it should return the existing one. <- this is where the error occurs

The migration is as follows:

class CreateProductItemTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('product_items', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('item_ref_id')->unique(); 
            $table->string('name');
            $table->timestamps();
        });
    }

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

The code used to create the item:

$product_item = App\ProductItem::firstOrCreate(['item_ref_id'=>1000,'name'=>'Item 1')] );

I have gone through the following posts, none of which have helped

  1. laravel first0rNew Integrity Constraint Violation
  2. Laravel 5 Integrity constraint violation
  3. Laravel - Integrity constraint violation: 1062 Duplicate entry
Community
  • 1
  • 1
tinonetic
  • 7,751
  • 11
  • 54
  • 79
  • What happens if your remove the unique constraint from 'item_ref_id'? –  May 03 '17 at 18:31
  • `firstOrCreate` goes off of the `id` field. IF you want `item_ref_id` to be your primary key, define that as primary with a specified value and remove the increments column. If you want the `item_ref_id` to be the auto-increment column, then change the increments id, and make sure to set the primaryKey in your model. – aynber May 03 '17 at 18:44
  • @aynber I want `item_ref_id` to be a unique key, not a primary key, and `id` to be the primary key. – tinonetic May 03 '17 at 18:46
  • Out of curiosity, what happens if you remove the `'name' => 'Item1'` and leave only the ref id as the search parameter? – Naltroc May 03 '17 at 19:11
  • [mpyw/laravel-retry-on-duplicate-key: Automatically retry non-atomic upsert operation when unique key constraints are violated.](https://github.com/mpyw/laravel-retry-on-duplicate-key) – mpyw Nov 22 '21 at 05:22

2 Answers2

1

I believe you have a syntax problem. The documentation describes the syntax as the following:

$flight = App\Flight::firstOrCreate(
    ['name' => 'Flight 10'], ['delayed' => 1]
);

Notice the fields are in different arrays, rather than two elements in the same array. Give that a shot. So for you it'd be:

$product_item = App\ProductItem::firstOrCreate(['item_ref_id'=>1000], ['name'=>'Item 1')] );
Arty
  • 246
  • 1
  • 8
  • It's possible that your syntax is also supposed to work, as I notice that the documentation uses it in the updateOrCreate() example. In which case there is a different problem. – Arty May 03 '17 at 19:22
1

I know this is old topic, but you can check the Traits you are using inside your model like "SoftDelete" this might show you the error also

because when the code try to check "Find" first it will not find it but its already in Database but deleted

Karim Haddad
  • 137
  • 6