Leveraging Laravel Eloquent: Retrieving Foreign Key Constraints from the Database

Foreign key constraints play a pivotal role in maintaining data integrity within relational databases. However, accessing information about these constraints programmatically can be challenging. In this guide, we'll explore how to leverage Laravel's Eloquent ORM to effortlessly retrieve foreign key constraints from the database, streamlining your development process and ensuring data consistency.

Understanding Foreign Key Constraints:
Before diving into the implementation details, let's briefly review what foreign key constraints are and why they're essential. Foreign key constraints establish relationships between tables in a relational database, ensuring that data consistency is maintained across related records. By enforcing referential integrity, foreign key constraints prevent orphaned records and maintain the integrity of your data model.

Retrieving Foreign Key Constraints with Laravel Eloquent:
In Laravel, Eloquent provides a powerful and intuitive interface for interacting with the database. We can leverage Eloquent to retrieve information about foreign key constraints from the database schema effortlessly. Here's how to do it:

Step 1: Define the Model:
Start by creating a model for the information_schema.key_column_usage table. Since this table resides in the information_schema database, we need to specify the connection name for this model.

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class KeyColumnUsage extends Model
{
    protected $table = 'information_schema.key_column_usage';

    public $timestamps = false;
}

Step 2: Retrieve Foreign Key Constraints:
Once we've defined the model, we can use Eloquent to query the information_schema.key_column_usage table and retrieve foreign key constraints. Here's how to do it:

use App\Models\KeyColumnUsage;

$results = KeyColumnUsage::select('TABLE_NAME as table_name', 'COLUMN_NAME as column_name', 'CONSTRAINT_NAME as constraint_name', 'REFERENCED_TABLE_NAME as referenced_table_name', 'REFERENCED_COLUMN_NAME as referenced_column_name')
    ->where('REFERENCED_TABLE_SCHEMA', DB::getDatabaseName())
    ->whereNotNull('REFERENCED_TABLE_NAME')
    ->get();

This query selects relevant columns from the information_schema.key_column_usage table, filters by the current database schema, and retrieves rows where the REFERENCED_TABLE_NAME column is not null.

In a recent project, I faced a big problem with foreign key constraints when trying to implement force delete functionality. The issue arose because different database engines treated these constraints differently. To solve this problem, I came up with a new plan: I decided to get rid of all the foreign key constraints and keep them only as indexes.

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class ForeignKeyToIndexCommand extends Command
{
    /**
     * @var string
     */
    protected $signature = 'foreign-key-to-index';

    /**
     * @var string
     */
    protected $description = 'Replace Database Foreign Key with Index Key.';

    /**
     * @return void
     */
    public function handle()
    {
        $sql = "SELECT `TABLE_NAME` as `table_name`, `COLUMN_NAME` as `column_name`, `CONSTRAINT_NAME` as `constraint_name`, `REFERENCED_TABLE_NAME` as `referenced_table_name`, `REFERENCED_COLUMN_NAME` as `referenced_column_name` FROM `information_schema`.`key_column_usage` WHERE `REFERENCED_TABLE_SCHEMA` = DATABASE() AND `REFERENCED_TABLE_NAME` IS NOT NULL";

        $results = DB::select($sql);

        if (!sizeof($results)) {
            return $this->info("* No Foreign Key Constraints Found. * \n");
        }

        foreach ($results as $k => $result) {
            Schema::table($result->table_name, function (Blueprint $table) use ($result) {
                if ($this->confirm("* Would you like to Drop Foreign Key [{$result->constraint_name}], and Add Index [y/n] *")) {
                    $table->dropForeign($result->constraint_name);

                    $this->info("* Dropped foreign key constraint: {$result->constraint_name} * \n");
                }

                $index_column = "{$result->table_name}_{$result->column_name}_index";

                $this->info("* Querying INDEX Key {$index_column} * \n");

                $index_stack = [];

                $table_indexes = DB::select("SHOW INDEXES FROM $result->table_name");

                foreach ($table_indexes as $index) {
                    if ($index->Key_name == $index_column) {
                        $index_stack[] = $index_column;
                    }
                }

                if (!sizeof($index_stack)) {
                    $table->index([$result->column_name]);

                    $this->info("* Added INDEX column {$index_column} * \n");
                } else {
                    $this->info("* INDEX column {$index_column} already exists.* \n");
                }
            });
        }
    }
}

Now, updating the console command as Laravel ORM style.

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
use App\Models\KeyColumnUsage;

class ForeignKeyToIndexCommand extends Command
{
    /**
     * @var string
     */
    protected $signature = 'foreign-key-to-index';

    /**
     * @var string
     */
    protected $description = 'Replace Database Foreign Key with Index Key.';

    /**
     * @return void
     */
    public function handle()
    {
        $results = KeyColumnUsage::select(
            'TABLE_NAME as table_name',
            'COLUMN_NAME as column_name',
            'CONSTRAINT_NAME as constraint_name',
            'REFERENCED_TABLE_NAME as referenced_table_name',
            'REFERENCED_COLUMN_NAME as referenced_column_name'
        )
        ->where('REFERENCED_TABLE_SCHEMA', DB::getDatabaseName())
        ->whereNotNull('REFERENCED_TABLE_NAME')
        ->get();

        if (!$results->count()) {
            return $this->info("* No Foreign Key Constraints Found. * \n");
        }

        foreach ($results as $k => $result) {
            Schema::table($result->table_name, function (Blueprint $table) use ($result) {
                if ($this->confirm("* Would you like to Drop Foreign Key [{$result->constraint_name}], and Add Index [y/n] *")) {
                    $table->dropForeign($result->constraint_name);

                    $this->info("* Dropped foreign key constraint: {$result->constraint_name} * \n");
                }

                $index_column = "{$result->table_name}_{$result->column_name}_index";

                $this->info("* Querying INDEX Key {$index_column} * \n");

                $index_stack = [];

                $table_indexes = DB::select("SHOW INDEXES FROM $result->table_name");

                foreach ($table_indexes as $index) {
                    if ($index->Key_name == $index_column) {
                        $index_stack[] = $index_column;
                    }
                }

                if (!sizeof($index_stack)) {
                    $table->index([$result->column_name]);

                    $this->info("* Added INDEX column {$index_column} * \n");
                } else {
                    $this->info("* INDEX column {$index_column} already exists.* \n");
                }
            });
        }
    }
}

Conclusion:
By utilizing the power of Laravel's Eloquent ORM, we can seamlessly retrieve foreign key constraints from the database, empowering us to ensure data integrity and streamline schema management. Whether you're building a new application or maintaining an existing one, leveraging Eloquent for database interactions offers unparalleled flexibility and ease of use.

Wrapping Up:
In this guide, we've explored how to use Laravel's Eloquent ORM to retrieve foreign key constraints from the database schema. By following the steps outlined above, you can effortlessly access vital information about your database structure, enhancing data integrity and simplifying schema management in your Laravel applications.