FOREIGN_KEY_CHECKS for rollback of foreign key type migrations in Laravel

- Andrés Cruz

En español
FOREIGN_KEY_CHECKS for rollback of foreign key type migrations in Laravel

We will see how we can solve a rather annoying error that can occur when you rollback migrations that have foreign type relationships in Laravel: the error in question looks like the following:

SQLSTATE[HY000]: General error: 3730 Cannot drop table 'tutorials' referenced by a foreign key constraint 'inscribed_tutorial_id_foreign' on table 'inscribed'. (SQL: drop table if exists `tutorials`)
 at vendor/laravel/framework/src/Illuminate/Database/Connection.php:703
   699        // If an exception occurs when attempting to run a query, we'll format the error
   700    // message to include the bindings with SQL, which will make this exception a
   701      // lot more helpful to the developer instead of just the database's errors.
   702   catch (Exception $e) {
 703             throw new QueryException(
   704                $query, $this->prepareBindings($bindings), $e
   705            );
   706        }
   707    }

This is, at first analysis, an error of the foreign keys or foreign key that we apply to our tables, everything is very good when we do this type of relationship, in which, for example, the tutorials table exists before the inscribed_tutorial_id_foreign table, and to the latter, we apply a relationship of type FK or foreign:

<?php
use Carbon\Carbon;
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
class CreateTutorialsTable extends Migration
{
   /**
    * Run the migrations.
    *
    * @return void
    */
   public function up()
   {
       Schema::create('tutorials', function (Blueprint $table) {
           ***
           $table->foreignId('user_id')->constrained()
           ->onDelete('cascade');
           ***
       });
   }
   public function down()
   {
       DB::statement('SET FOREIGN_KEY_CHECKS = 0');
       Schema::dropIfExists('tutorials');
       DB::statement('SET FOREIGN_KEY_CHECKS = 1');
   }
}

As you can see, the novelty is that when deleting a table that is related to another FK type table, we deactivate the check for foreign keys.

Migraciones lista
Migraciones lista

Resolution

To avoid this, we can disable the check for foreign keys in those tables that is used by others to store references (FKs).

Optional:

In SQL it's something like this:

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS table1;
SET FOREIGN_KEY_CHECKS = 1;

In SQL it's something like this:

Andrés Cruz

Desarrollo con Laravel, Django, Flask, CodeIgniter, HTML5, CSS3, MySQL, JavaScript, Vue, Android, iOS, Flutter

Andrés Cruz en Udemy

Acepto recibir anuncios de interes sobre este Blog.