ssquare's avatar

SQLSTATE[HY000]: General error: 1 ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint error on test environment

I am using mysql on my laravel enviroment and I am using sqlite as database on .env.testing. I could easily run php artisan migrate:fresh --seed on laravel but if I run php artisan migrate:fresh --seed --env=testing I am getting error:

SQLSTATE[HY000]: General error: 1 ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint 

And my this particular seeder where it is throwing error looks like:

<?php

namespace Database\Seeders;

use App\Models\Permission;
use Illuminate\Database\Seeder;

class PermissionsSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        Permission::upsert([
            /**
             * User Model Related Permissions 1-4
             */
            [
                'name' => 'user-view',
                'label' => 'User View',
                'description' => 'Allows to view users',
                'created_at' => now(),
                'updated_at' => now()
            ],
            [
                'name' => 'user-create',
                'label' => 'User Create',
                'description' => 'Allows to create user',
                'created_at' => now(),
                'updated_at' => now()
            ],
            [
                'name' => 'user-update',
                'label' => 'User Update',
                'description' => 'Allows to update user',
                'created_at' => now(),
                'updated_at' => now()
            ],
            [
                'name' => 'user-destroy',
                'label' => 'User Destroy',
                'description' => 'Allows to destroy user',
                'created_at' => now(),
                'updated_at' => now()
            ],
            
        ], ['name'], ['label','description','updated_at']);
    }
}

This same code in working on .env environment but not on .env.testing environment, but as I already mentioned .env is using mysql where as .env.testing is using sqlite

0 likes
3 replies
ssquare's avatar

could you please elaborate on your answer?

circleback's avatar

According to Upsert section in Laravel Docs, unlike MySQL, you need to create "primary" or "unique" index for sqlite or any other databases.

All databases except SQL Server require the columns in the second argument of the upsert method to have a "primary" or "unique" index. In addition, the MySQL database driver ignores the second argument of the upsert method and always uses the "primary" and "unique" indexes of the table to detect existing records.

So in your case, you need to set name column as primary key in your Permission Model's migration file like this:

$table->string('name')->primary();

Please or to participate in this conversation.