I currently use Digital Ocean’s managed database for my primary database but I wanted to try out Planet Scale. When migrating a database over to Planet Scale you cannot use Foreign Keys. This got me thinking about a way to automatically generate a migration file to drop all foreign keys.

If you want to jump the intro and background you can jump directly to the good stuff here building a Laravel migration to drop all Foreign Keys.

---

I’m not sure when it first started, probably in my advanced database class when studying Information Technology at Teesside University, but someone told me to include foreign keys whenever I can. It’s been ingrained in my mind for as long as I can remember.

I then started working with migrations in Laravel and I soon became a fan of this simple one-liner to add a column, index and foreign key to to a table using a migration.

Schema::table('sites', function (Blueprint $table) {
    // Referencing the column and table yourself
    $table->foreign('theme_id')->references('id')->on('themes');

    // Using Laravel and it's magic
    $table->foreignId('themes')->index()->constrained()->cascadeOnDelete();
});

I’ve never really understood what benefits a foreign key gives you. To me, the most important thing in these types of columns is the index, but you can add that separately.

Schema::table('sites', function (Blueprint $table) {
    $table->bigInteger('theme_id')->index();
});

I do like the fact that when you’re browsing tables in software like TablePlus that you get a convenient arrow next to the foreign key which you can click and it will take you to the references row/table. I also like the fact that when you create Entity Relationship Diagrams (ERD) that it can automatically detect relationships and draw lines between tables.

Planet Scale – Modern Databases

I’m sure you’ve all heard about the hype around Planet Scale.

“The MySQL-compatible serverless database platform”.

They’ve recently introduced a hugely powerful migration process which gives you access to a simple and easy zero-downtime migration from your current database into Planet Scales solution.

I’ve been intrigued to see what performance benefits I could get from moving so this week I decided to have a play. I use Digital Ocean managed database so I cloned my primary database and started the process of migrating this over to Planet Scale. I anticipated that I may need to adjust things on my primary before I could do the migration so I wanted to be able to use a cloned version.

The setup process for Planet Scale is remarkably easy and they guide you through the migration process every step of the way. However, I hit my first stumbling block quite quickly. I was greated with a message informing me that Planet Scale does not support foreign keys.

To support scaling across multiple database servers, PlanetScale does not allow the use of foreign key constraints, which are normally used in relational databases to enforce relationships between data in different tables, and asks users to handle this manually in their applications.

I’m sure someone could give us a detailed explanation for why this is but I’m not that person. One thing was certain, if I wanted to continue, I needed to remove the foreign keys from my database.

Removing Foreign Keys from MySQL

It’s actually quite easy to drop a foreign key. You just need to know the reference name and then just run

ALTER TABLE sites DROP FOREIGN KEY sites_theme_id_foreign;

There is also a Laravel helper which you can add to a migration to do this.

Schema::table('sites', function (Blueprint $table) {
    // If you want to use the index name
    $table->dropForeign('sites_theme_id_foreign');

    // If you want Laravel to do it's magic based on the column name
    $table->dropForeign(['sites_theme_id']);
});

Building a Laravel migration to drop all Foreign Keys

I really didn’t want to have to dig through all my database tables to work out which ones had foreign keys and then hand write each table and column into a new migration. So I thought I’d write something to do it for me.

I’ve built a very simple Laravel Command that you can run. It will connect to your database, get information on all the tables and forging keys and then loop through and spit out some formatted dropForeign stuff for you into a storage/app/migration_stuff.txt file.

The source for the below is also available in a GitHub Gist

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;

class GenerateDropForeignKeyMigration extends Command
{
    protected $signature = 'migration';

    protected $description = 'Command description';

    public function handle()
    {
        $data = DB::select(
            "
            SELECT
              TABLE_NAME,
              COLUMN_NAME,
              CONSTRAINT_NAME,
              REFERENCED_TABLE_NAME,
              REFERENCED_COLUMN_NAME
            FROM
              INFORMATION_SCHEMA.KEY_COLUMN_USAGE
            WHERE
              REFERENCED_TABLE_SCHEMA = ?",
            [config('database.connections.mysql.database')]
        );

        $results = collect($data)->groupBy('TABLE_NAME');

        $text = '';

        foreach ($results as $table => $data) {
            $text .= sprintf(
                "Schema::table('%s', function (Blueprint \$table) {
",
                $table
            );

            foreach ($data as $item) {
                $text .= sprintf("    \$table->dropForeign('%s');\n", $item->CONSTRAINT_NAME);
            }

            $text .= "});\n";
        }

        Storage::put('migration_stuff.txt', $text);
    }
}

This dumps something like the below in storage/app/migration_stuff.txt

Schema::table('team_invitations', function (Blueprint $table) {
    $table->dropForeign('team_invitations_team_id_foreign');
});
Schema::table('placement_webhook', function (Blueprint $table) {
    $table->dropForeign('placement_webhook_placement_id_foreign');
    $table->dropForeign('placement_webhook_webhook_id_foreign');
});
Schema::table('site_partner_buckets', function (Blueprint $table) {
    $table->dropForeign('site_partner_buckets_bucket_id_foreign');
    $table->dropForeign('site_partner_buckets_partner_id_foreign');
    $table->dropForeign('site_partner_buckets_site_id_foreign');
});

I have thought about making this a package but I honestly don’t have the time right now so if you feel like you could package this up then please feel free!