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

muragijimana's avatar

how to set foreign key check to zero

how to SET FOREIGN KEY CHECKS to zero on migration rollback thanks,

0 likes
11 replies
mstnorris's avatar
DB::statement('SET FOREIGN_KEY_CHECKS=0');

I also use the cleanDatabase method that Jeffrey wrote a couple of years ago:

public function cleanDatabase()
{
    DB::statement('SET FOREIGN_KEY_CHECKS=0');
    foreach ($this->tables as $table) {
        DB::table($table)->truncate();
    }
    DB::statement('SET FOREIGN_KEY_CHECKS=1');
}

Then when you are migrating your database or seeding it:

$this->cleanDatabase();
9 likes
davorminchorov's avatar
public function down()
{
    DB::statement('SET FOREIGN_KEY_CHECKS=0');
    Schema::drop('table_name');
    DB::statement('SET FOREIGN_KEY_CHECKS=1');
}

but it's a bad idea to remove foreign key checks in production. It's ok if you use it for seeding or testing (during development)

1 like
Shovels's avatar

Update: Since 5.2 this can be done inside a migration using:

Schema::enableForeignKeyConstraints();

Schema::disableForeignKeyConstraints();
5 likes
thepanda's avatar

@Shovels Does this disable foreign key constraint for the current connection or does it disable it on table level?

shez1983's avatar

i know i am late to the party but i have soe trouble with this - before it used to work but now when i set them to 0 and truncate some tables i get the cannot truncate as it references something else error

if i set them to 0 in mysql directly and truncate said table it all works fine..

fuzin's avatar

Had this problem by seeding.

instead of

DB::table('TABLE_NAME')->truncate();

use

DB::table('TABLE_NAME')->delete();

By migrations you have to be careful to use opposite order. So first you have to drop pivot tables or ones that have CONSTRAINTS and then main table.

Example:

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        ...
   });

   Shema::create('user_tag', function (Blueprint $table) {
        ...
   });

}

public function down()
{
    Shema::drop('user_tag'); 
    Shema::drop('users');
}

I would maybe disable FOREIGN_KEY_CHECKS on seeding but I consider a bad practice using it in migrations.

Tarlan's avatar

This is how I fixed this issue



public function up(): void {
        Schema::disableForeignKeyConstraints();
        Schema::create('posts', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->id();
            $table->string('title');
            $table->text('comment');
            $table->string('image')->nullable();
            $table->unsignedBigInteger('likes')->nullable();
            $table->boolean('is_published')->default(1);
            $table->timestamps();
            $table->softDeletes();
            $table->unsignedBigInteger('category_id')->nullable();
            $table->index('category_id','post_category_idx');
            $table->foreign('category_id','post_category_fk')->on('categories')->references('id');
        });
    }


public function up(): void
    {
        Schema::disableForeignKeyConstraints();
        Schema::create('categories', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->id();
            $table->string('title');
            $table->timestamps();
        });

        Schema::table('posts', function (Blueprint $table) {
            $table->foreign('category_id')->references('id')->on('categories');
        });
    }
1 like

Please or to participate in this conversation.