DEV Community

Cover image for How to Make a major change to all your foreign keys (Mysql, Laravel)
Ahmed Magdy
Ahmed Magdy

Posted on • Edited on

2 1

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");
});

Enter fullscreen mode Exit fullscreen mode

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

Data

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);
            });
        }
    }

Enter fullscreen mode Exit fullscreen mode

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.

Image of Datadog

How to Diagram Your Cloud Architecture

Cloud architecture diagrams provide critical visibility into the resources in your environment and how they’re connected. In our latest eBook, AWS Solution Architects Jason Mimick and James Wenzel walk through best practices on how to build effective and professional diagrams.

Download the Free eBook

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay