Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
how to fix this?
$ php artisan migrate:refresh
[Illuminate\Database\QueryException]
SQLSTATE[23000]: Integrity constraint violation: 1217 Cannot delete or update a parent row: a foreign key constraint fails (SQL: drop table `imag
es`)
[PDOException]
SQLSTATE[23000]: Integrity constraint violation: 1217 Cannot delete or update a parent row: a foreign key constraint fails
It's basically just saying that you're trying to drop a table that has a foreign key referencing to another table. Just try switching the order these table drop commands are executed, or you can simply ignore the foreign key checks in your migrations down() method, like so:
public function down()
{
DB::statement('SET FOREIGN_KEY_CHECKS = 0');
Schema::drop('tableName');
DB::statement('SET FOREIGN_KEY_CHECKS = 1');
}
Hope I could help.
@toniperic How about if its parent table?
@toniperic Hi,, this was the best answer but it seems it doesnt work on my end.
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
class Products extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('products', function (Blueprint $table) {
$table->id();
$table->bigInteger('product_type_id')->unsigned()->nullable(true);
$table->string('name')->nullable(false);
$table->string('description');
$table->float('price')->nullable(false);
$table->float('old_price');
$table->string('status')->comment('active, inactive, removed');
$table->float('tax');
$table->float('unit_price');
$table->float('taxable_amount');
$table->float('taxable_total');
$table->dateTime('deleted_at');
$table->timestamps();
});
Schema::table('products', function (Blueprint $table) {
$table->foreign('product_type_id')->references('id')->on('product_type');
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
DB::statement('SET FOREIGN_KEY_CHECKS = 0');
Schema::dropIfExists('products');
DB::statement('SET FOREIGN_KEY_CHECKS = 1');
}
}
then I run php artisan migrate:refresh --path=database\migrations\2022_06_03_135557_products.php
but this error shows up:
Migrating: 2022_06_03_135557_products
Illuminate\Database\QueryException
SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'products' already exists (SQL: create table `products` (`id` bigint unsigned not null auto_increment primary key, `product_type_id` bigint unsigned null, `name` varchar(191) not null, `description` varchar(191) not null, `price` double(8, 2) not null, `old_price` double(8, 2) not null, `status` varchar(191) not null comment 'active, inactive, removed', `tax` double(8, 2) not null, `unit_price` double(8, 2) not null, `taxable_amount` double(8, 2) not null, `taxable_total` double(8, 2) not null, `deleted_at` datetime not null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci')
like that? not working
<?php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateCatsTable extends Migration {
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('cats', function(Blueprint $table)
{
$table->increments('id');
$table->string('name');
$table->integer('order_id')->unsigned()->index();
$table->integer('parent_id')->unsigned()->index();
});
Schema::create('article_cat', function(Blueprint $table)
{
$table->integer('cat_id')->unsigned()->index();
$table->foreign('cat_id')->references('id')->on('cats')->onDelete('cascade');
$table->integer('article_id')->unsigned()->index();
$table->foreign('article_id')->references('id')->on('articles')->onDelete('cascade');
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::drop('cats');
DB::statement('SET FOREIGN_KEY_CHECKS = 0');
Schema::drop('article_cat');
DB::statement('SET FOREIGN_KEY_CHECKS = 1');
}
}
now i try that and working
public function down()
{
DB::statement('SET FOREIGN_KEY_CHECKS = 0');
Schema::drop('cats');
Schema::drop('article_cat');
DB::statement('SET FOREIGN_KEY_CHECKS = 1');
}
Unfortunately, this does not work if each migration is in its own file with separate down() method and if you have migrations with production data in up() down() methods. Scenario:
In my case, I'm on Postgres, and it does not have SET FOREIGN_KEY_CHECKS. But even if it had, it would make my data inconsistent because now FKs in my seed data would point to non-existent production records. Of course, if I refresh entire database or rollback all migrations, I'll lose all the data anyway, but what if I rollback a single migration? Then my seed records are broken, unless I run --seed again.
To delete a table in Postgres and avoid foreign key constraint checks, do this:
DB::statement('drop table thetable cascade');
Just make sure that in the down method you first drop the latest table you created, in your case article_cat, and then cat .
What I did in a similar case, where my migrations are distributed in multiple files, I just added the
Schema::disableForeignKeyConstraints();
to the down method for the migration in question. Works like a charm (MariaDB).
@mikewink THIS is the BEST solution. simple and it works. should be in the Laravel Docs if it's not already. Thanks.
@mikewink how about this one,,, I tried what you suggested but its not working on my end.
public function up()
{
Schema::create('products', function (Blueprint $table) {
$table->id();
$table->bigInteger('product_type_id')->unsigned()->nullable(true);
$table->string('name')->nullable(false);
$table->string('description');
$table->float('price')->nullable(false);
$table->float('old_price');
$table->string('status')->comment('active, inactive, removed');
$table->float('tax');
$table->float('unit_price');
$table->float('taxable_amount');
$table->float('taxable_total');
$table->dateTime('deleted_at');
$table->timestamps();
});
Schema::table('products', function (Blueprint $table) {
$table->foreign('product_type_id')->references('id')->on('product_type');
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::disableForeignKeyConstraints();
Schema::dropIfExists('products');
}
after running this command Schema::disableForeignKeyConstraints();
I got this error
Migrating: 2022_06_03_135557_products
Illuminate\Database\QueryException
SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'products' already exists (SQL: create table `products` (`id` bigint unsigned not null auto_increment primary key, `product_type_id` bigint unsigned null, `name` varchar(191) not null, `description` varchar(191) not null, `price` double(8, 2) not null, `old_price` double(8, 2) not null, `status` varchar(191) not null comment 'active, inactive, removed', `tax` double(8, 2) not null, `unit_price` double(8, 2) not null, `taxable_amount` double(8, 2) not null, `taxable_total` double(8, 2) not null, `deleted_at` datetime not null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci')
thank's
Please or to participate in this conversation.