Integrity constraint violation firstOrCreate()

Question

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))'

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
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

What happens if your remove the unique constraint from 'item_ref_id'? — user320487
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
@aynber I want item_ref_id to be a unique key, not a primary key, and id to be the primary key. — user919426
Out of curiosity, what happens if you remove the 'name' => 'Item1' and leave only the ref id as the search parameter? — Naltroc

Arty Arty · Accepted Answer · 2017-05-03T19:20:17

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')] );

Integrity constraint violation firstOrCreate()

1 Answers