Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

canadianlover's avatar

SQLite - unique constraint failed

I have a test in my aqpplication. I have the following model factory:



use App\Hand;
/*
|--------------------------------------------------------------------------
| Model Factories
|---------------?php


use App\Hand;
/*
|--------------------------------------------------------------------------
| Model Factories
|--------------------------------------------------------------------------
|
| Here you may define all of your model factories. Model factories give
| you a convenient way to create models for testing and seeding your
| database. Just tell the factory how a default model should look.
|
*/

/** @var \Illuminate\Database\Eloquent\Factory $factory */
$factory->define(App\User::class, function (Faker\Generator $faker) {
    static $password;

    return [
        'id' => $faker->unique(true)->randomNumber,
        'name' => $faker->name,
        'email' => $faker->unique()->safeEmail,
        'password' => $password ?: $password = bcrypt('secret'),
        'remember_token' => str_random(10),
    ];
});

$factory->define(App\Hand::class, function(Faker\Generator $faker) {
    return ['game_id' => function () {
        return factory('App\Game')->create()->id;
    },
      'user_id' => function() {
        return factory('App\User')->create()->id;
      }
        ];


});

$factory->define(App\Game::class, function(Faker\Generator $faker) {
    return [
        'id' => $faker->unique()->randomDigit,
        'user_id' => function() {
      return  factory('App\User')->create()->id;
    },
        'hand_id' => function() {
        return factory('App\Hand')->create()->id;
        }];
});

$factory->define(App\Card::class, function(Faker\Generator $faker) {
    $game = factory('App\Game');
    static $card_count = 0;


    });

and this test:

<?php


use App\Hand;
/*
|--------------------------------------------------------------------------
| Model Factories
|---------------?php


use App\Hand;
/*
|--------------------------------------------------------------------------
| Model Factories
|--------------------------------------------------------------------------
|
| Here you may define all of your model factories. Model factories give
| you a convenient way to create models for testing and seeding your
| database. Just tell the factory how a default model should look.
|
*/

/** @var \Illuminate\Database\Eloquent\Factory $factory */
$factory->define(App\User::class, function (Faker\Generator $faker) {
    static $password;

    return [
        'id' => $faker->unique(true)->randomNumber,
        'name' => $faker->name,
        'email' => $faker->unique()->safeEmail,
        'password' => $password ?: $password = bcrypt('secret'),
        'remember_token' => str_random(10),
    ];
});

$factory->define(App\Hand::class, function(Faker\Generator $faker) {
    return ['game_id' => function () {
        return factory('App\Game')->create()->id;
    },
      'user_id' => function() {
        return factory('App\User')->create()->id;
      }
        ];


});

$factory->define(App\Game::class, function(Faker\Generator $faker) {
    return [
        'id' => $faker->unique()->randomDigit,
        'user_id' => function() {
      return  factory('App\User')->create()->id;
    },
        'hand_id' => function() {
        return factory('App\Hand')->create()->id;
        }];
});

$factory->define(App\Card::class, function(Faker\Generator $faker) {
    $game = factory('App\Game');
    static $card_count = 0;


    });

When I run phpunit I get the following error

1) Tests\Feature\GameTest::test_both_hands_have_unique_cards Illuminate\Database\QueryException: SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: users.id (SQL: insert into "users" ("id", "name", "email", "password", "remember_token", "updated_at", "created_at") values (8, Reinhold Murazik PhD, bill51@example.com, $2y$10$q1JAUfI6hAT3L2xBTYjMgeqow2wKTnvIlKENuPl.77hBRObZZG45O, 6t3D4wuEyS, 2019-05-16 13:52:50, 2019-05-16 13:52:50))

I was curious if this was an issue with SQLite, so I fired up tinker and successfully created a new user record by running factory('App\User')->create. However, I still get the integrity constraint violation error in my testing environment. Why do I get the error in my SQLIte test database but not my main MySQL database

0 likes
5 replies
Tray2's avatar

This error is thrown since you are not removing the created user after each test. So when the test tries to create the user it already exists thus the error.

I suggest you use an in memory database for your testing. The other way is to have a teardown in your test class.

Tray2's avatar

@devfrey Good eyes but wrong suggestion.

The id should never ever be added with code, the database should always increment it that way it will never be a duplicate.

1 like
devfrey's avatar
devfrey
Best Answer
Level 11

@TRAY2 - Ah, you're right. I missed the 'id' column in the factory.

@canadianlover - Remove the 'id' column from your model factory and let SQLite generate a unique ID for you.

Please or to participate in this conversation.