Test failing with Integrity constraint violation error, but on the first test case it passed. Why?

I have created a test. It keep failing with this error:

   FAIL  TestsFeatureWorkfieldTest
  ✓ index should be accessible by internal
  ⨯ index should be accessible by customer
  ⨯ index should be accessible by employee
  ⨯ index should not be accessible by guest

   IlluminateDatabaseQueryException 

  SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`myapp`.`workfields`, CONSTRAINT `workfields_specialization_id_foreign` FOREIGN KEY (`specialization_id`) REFERENCES `specializations` (`id`) ON DELETE CASCADE) (SQL: insert into `workfields` (`specialization_id`, `server_generated`, `updated_at`, `created_at`) values (1, 1, 2020-11-18 18:55:50, 2020-11-18 18:55:50))

I have googled it and read similar questions here and there but still can’t find the appropriate answer. I have defined and call the seeders on setUp, respecting the order of the table. The first call of test method are passed, the rest is failing. I see that the error has an insert call, meanwhile my test case is doing nothing but to get and count the data from it. So, what happened here?

Test

class WorkfieldTest extends TestCase
{
    use RefreshDatabase;

    private $internal;
    private $customer;
    private $employee;
    private $count;

    public function setUp(): void
    {
        parent::setUp();
        $this->app->make(PermissionRegistrar::class)->registerPermissions();
        $this->seed(RolesAndPermissionSeeder::class);
        $this->seed(SpecializationSeeder::class);
        // dd(Specialization::count()); -> 68
        $this->seed(WorkfieldSeeder::class);

        $this->internal = User::factory()->create()->assignRole('internal');
        $this->customer = User::factory()->create()->assignRole('customer');
        $this->employee = User::factory()->create()->assignRole('employee');

        $this->count = Workfield::count();
    }

    public function testIndexShouldBeAccessibleByInternal()
    {
        $this->actingAs($this->internal, 'api')
            ->getJson(route('workfields.index'))
            ->assertOk()
            ->assertJsonCount($this->count);
    }

    public function testIndexShouldBeAccessibleByCustomer()
    {
        $this->actingAs($this->customer, 'api')
            ->getJson(route('workfields.index'))
            ->assertOk()
            ->assertJsonCount($this->count);
    }

    public function testIndexShouldBeAccessibleByEmployee()
    {
        $this->actingAs($this->employee, 'api')
            ->getJson(route('workfields.index'))
            ->assertOk()
            ->assertJsonCount($this->count);
    }

    public function testIndexShouldNotBeAccessibleByGuest()
    {
        $this->getJson(route('workfields.index'))
            ->assertUnauthorized();
    }
}

Migration

public function up()
{
    Schema::create('workfields', function (Blueprint $table) {
        $table->id();
        $table->foreignId('specialization_id')->constrained()->onDelete('cascade');
        $table->boolean('server_generated')->default(false);
        $table->timestamps();
    });
}

public function down()
{
    Schema::dropIfExists('workfields');
}

Answer

The test file runs without error when using in-memory sqlite database by uncommenting the below two lines in standard phpunit.xml that comes with Laravel installation

    <server name="DB_CONNECTION" value="sqlite"/>
    <server name="DB_DATABASE" value=":memory:"/> 

However while testing against MySQL it fails as you mention. Probably the seeders running before each test is a problem – as your seeders contain fetching data from json files and then converting them to associated array before running Model::create(). With in memory database such problem is not encountered but with MySQL the problem arises somehow.

There are two ways to get around this. First most simple way is to include call to run all seeders in DatabaseSeeder class and then set the value of $seed property to true on the test class where you need the seed data – more info here

class WorkfieldTest extends TestCase
{
   use RefreshDatabase;

   /**
    * Indicates whether the database should be seeded before each test.
    *
    * @var bool
    */
    protected $seed = true;

   //... tests
}

The other option is to run the seeders with data fetched from json files only once before all tests by setting a static variable on the class and using it to control that certain seeders are run only once for all tests in the class.

class WorkfieldTest extends TestCase
{
    use RefreshDatabase;


    protected static $initialized = false;

    protected function setUp(): void
    {
        parent::setUp();
        $this->app->make(PermissionRegistrar::class)->registerPermissions();
        $this->seed(RolesAndPermissionSeeder::class);

        if (!static::$initialized) {
            $this->seed([SpecializationSeeder::class, WorkfieldSeeder::class]);
            static::$initialized = true;
        }
    }

   //... tests
}

Either of the above two will allow the tests to run with MySQL/Sqlite without any error.