How to Make a major change to all your foreign keys (Mysql, Laravel)

I've recently stumbled upon this issue where I wanted to add ON DELETE CASCADE to every foreign key i had on my tables.

I couldn't afford to do this manually because the project I am working on had over 106 tables and it's still expanding, so the main idea here is to write a general script that can be applied to any project.

1- You need to fetch every foreign key for you DATABASE

SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = "<DATABASE_NAME>" AND REFERENCED_COLUMN_NAME IS NOT NULL AND CONSTRAINT_NAME LIKE "%_foreign"

Note I added CONSTRAINT_NAME LIKE "%_foreign" because when Laravel creates a foreign key it follows this convention below

CONVENTION:


Schema::table('student_grades', function(Blueprint $table){
  // adding a foreign key 
   $table->foreign("student_id")->references("id")->on("students");
});

The created foreign key will be named student_grades_student_id_foreign.

hence the condition CONSTRAINT_NAME LIKE "%_foreign".

If you apply the previous SQL Query you will get data like this

2- Re-create your foreign key

make a migration file php artisan make:migration alter_foreign_keys_on_tables

Now let's get to the code

  public function up() {

       // The SQL Query
        $data = DB::table("INFORMATION_SCHEMA.KEY_COLUMN_USAGE")
            ->select([
                "TABLE_NAME",
                "COLUMN_NAME",
                "CONSTRAINT_NAME",
                "REFERENCED_TABLE_NAME",
                "REFERENCED_COLUMN_NAME"
            ])
            ->whereRaw("TABLE_SCHEMA =  '<DATABASE_NAME>' 
                AND REFERENCED_COLUMN_NAME IS NOT NULL 
                AND CONSTRAINT_NAME LIKE '%_foreign'")
            ->get();
        // you need to write the next line to get back to your ..
        // Original Database 

        DB::statement("USE <DATABASE_NAME>");
        foreach ($data as $single) {
           // Don't forget to add protected $single = null; in migration class
            $this->single = $single;
            Schema::table($single->TABLE_NAME, function (Blueprint $table) {
               // drop the previous foreign key 
                $table->dropForeign($this->single->CONSTRAINT_NAME);
                // New Foreign key 
$table->foreign($this->single->COLUMN_NAME)->references($this->single->REFERENCED_COLUMN_NAME)
                    ->onDelete("cascade")->on($this->single->REFERENCED_TABLE_NAME);
            });
        }
    }

Final Note:

The purpose of this article was to show you how to make a major change to all your foreign keys.

feel free to contact me on my email ahmed.magdy.9611@gmail.com.

Thanks for coming to my Ted Talk.

Blog

How to Make a major change to all your foreign keys (Mysql, Laravel)

Ahmed Magdy

Join Our Newsletter. No Spam, Only the good stuff.

Related