Laravel firstOrCreate method throws duplicate ID error

Question

I am in the midst of writing a command in my Laravel project which inserts categories to a database table, but depending on whether or not they already exist.

I investigated the way to do this and came across firstOrCreate method, so I wrote the following in my command:

$comCats = new CommunicationsCategories();

$comCats->firstOrCreate(
    ['name' => 'Job Updates'], ['region_id' => 1]);
$comCats->firstOrCreate(
    ['name' => 'Alerts'], ['region_id' => 1]);

Basically, I need to create these two categories in the communications_categories table with a region ID of 1. The Job Updates category already exists, so it skipped that as expected but when it tries to create the Alerts category which doesn't exist I get the following error in my console:

SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "communications_categories_pkey"

DETAIL: Key (id)=(2) already exists. (SQL: insert into "communications_categories" ("name", "region_id", "updated_at", "created_at") values (Alerts, 1, 2018-06-19 09:38:20, 2018-06-19 09:38:20) returning "id")

It appears that it's trying to allocate a primary key ID of 2 when it already exists - but the table structure has a nextval on the primary key which I thought would take the last ID added and create a new one after that. According to the Laravel documentation on Eloquent Inserts here there's no mention of having to stipulate the actual primary key id itself, and the fillable elements are only name and region_id.

Any help on this appreciated, as I'm reasonably new to Laravel and the eloqent database methods. If it helps, I'm using a PostgreSQL database.

Pretty sure firstOrCreate doesn't take 2 arguments. `$comCats->firstOrCreate(['name' => 'Job Updates', 'region_id' => 1]);` — Tuim, Jun 19 '18 at 08:58
@Tuim yes, it does. It's on the documentation. In this case I am saying, if the name 'Job Updates' does not exist, fill it in with a region_id of 1. What you have written there is, check to see if Job Updates exists AND has region_id of 1. — Michael Emerson, Jun 19 '18 at 10:11
i think your primary key field is not auto increment. try to add auto increment in your primary key field. https://chartio.com/resources/tutorials/how-to-define-an-auto-increment-primary-key-in-postgresql/ — rkj, Jun 19 '18 at 11:50
Please post the `communications_categories` migration (or table structure). — Jonas Staudenmeir, Jun 19 '18 at 17:23
[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

score 2 · Answer 1 · answered Jan 21 '20 at 16:14

The other answer seems to have assumed that you need to have firstOrCreate explained to you, when in fact you have actually encountered a bug in the Laravel framework and also assumed that Key (id)=(2) refers to region_id instead of your autoincrementing primary key id.

You most likely want a unique index on communications_categories.name.

You can instead use this SQL in postgres (assuming you have a unique index on name) which should be more reliable than firstOrCreate, but if you were planning to use other laravel features with firstOrCreate (like observers, or more query builders), their functionality could be lost.

    INSERT INTO communications_categories (name, region_id, created_at, updated_at)
    VALUES ('Job Updates', 1, NOW(), NOW())
    ON CONFLICT (name) DO
    UPDATE SET region_id = excluded.region_id,
        updated_at = NOW()
    RETURNING *;

which can be used like:

    $values = DB::select($sql); // will return Array of StdClass

or without returning * if you don't need the id values.

    DB::insert($sql); // will return true

If you need the Eloquent object returned, I would recommend including returning * or returning id and passing that to CommunicationsCategories::findOrFail($values[0]['id']). Putting that complicated insert statement into an Eloquent select will probably be a lot of work. Eloquent also has a function called hydrate which could work without additional SQL calls.

The SQL statement will insert the desired values, except when there is a conflict on the unique constraint, then which it will apply the values excluded by conflict from insert to the pre-existing rows via an update.

score 1 · Answer 2 · answered Jun 19 '18 at 08:58

1

The firstOrCreate method will attempt to locate a database record using the given column / value pairs. If the model can not be found in the database, a record will be inserted with the attributes from the first parameter, along with those in the optional second parameter.

$comCats->firstOrCreate(
    ['name' => 'Job Updates'], ['region_id' => 1]);

here you are trying to locate first a name with "Job Updates" and a region_id with 1 ,if laravel cant find the specific data, it will try to create or insert the given parameters, but when the system tries to insert, region_id with value of "1" already exist.

if region_id is your primary key try:

  $comCats->firstOrCreate(
        ['name' => 'Job Updates']);

answered Jun 19 '18 at 08:58

Chill Music Hits

29
2

region_id is not my primary key. The table has many categories assigned to the same region ID. As i mentioned in an above comment, the way I have written it is, check to see if a name of Job Updates exists, and if it does NOT, add it in WITH a region_id of 1. This is documented on https://laravel.com/docs/5.6/eloquent#inserts under the Other Creation Methods section. – Michael Emerson Jun 19 '18 at 10:14
can you check the index of field region_id on tables communications_categories_pkey and communications_categories make sure that its not unique. – Chill Music Hits Jun 20 '18 at 01:12
I don't need to - it's definitely 100% not unique. This data has been in here for months. I'm only just taking over the project - there are many categories assigned to a single region, otherwise it'd be a bit pointless. – Michael Emerson Jun 20 '18 at 12:59

Dale Ryan · Answer 3 · 2021-08-03T00:47:40.287

Pardon me for I cannot post a comment yet...

If somebody encounter this same issue about postgresql, this is definitely a bug. In my case this bug occurred after I imported a local postgresql database using heroku following this procedure:

Workaround for pushing to heroku database on windows machince.

I tried running the query again and it worked... it fails sometimes too. Now this sucks. I'll update this I found another way around this issue.

Edit: After looking at more searches about the issue, I found the following which perfectly matches my situation:

PostgreSQL: Unique violation: 7 ERROR: duplicate key value violates unique constraint “users_pkey”

I have the same issue with Heroku PostgreSQL and haven't found a solution yet other than to check for the existence first, then create which defeats the purpose of firstOrCreate — Colin, May 01 '23 at 18:16

Laravel firstOrCreate method throws duplicate ID error

3 Answers3