moayadcodes's avatar

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

I have a Laravel 11 application setup on my local using Sail with PostgreSQL. The application contains a queue job that, amongst other things, runs the following code:

$entry = Entry::firstOrCreate(
    [
        'url' => $video['url'], // url has a unique index
    ],
    [
        'youtube_id' => $video['id'],
        'title' => !empty($video['title']) ? $video['title'] : null,
        'description' => !empty($video['description']) ? $video['description'] : null,
        'image_url' => !empty($video['thumbnail_url']) ? $video['thumbnail_url'] : null,
        'published_at' => !empty($video['published']) ? Carbon::parse($video['published']) : null,
    ]
);

The first time I run this job, it works without issues. The second time I run the job, it throws the error:

local.ERROR: SQLSTATE[23505]: Unique violation: 7 ERROR:  duplicate key value violates unique constraint "entries_url_unique"
DETAIL:  Key (url)=(https://www.youtube.com/watch?v=myVideoId) already exists.

If I go into Tinker and run App\Models\Entry::where('url', 'https://www.youtube.com/watch?v=myVideoId')->get(), I get back null indicating the record doesn't exist. If I try to insert a record with this same URL however, I get the same duplicate key error above... Why is this happening and how do I fix it?

0 likes
4 replies
JussiMannisto's avatar

Does your model use soft-deletion? If the entry model with that URL were soft-deleted, it would explain this behavior.

moayadcodes's avatar

Figured it out. The issue was that I added a global scope to the Entry model that I forgot about. But even then, and maybe there's a use case I'm not thinking of, but a global scope probably shouldn't apply to an insert operation, should it?

JussiMannisto's avatar

@moayadcodes firstOrCreate is not an insert operation, it's a combination of two operations:

  1. Find the first matching model.
  2. Create the model if no match was found.

The global scope is applied on step 1.

Please or to participate in this conversation.