How to Delete an Existing Foreign Key in Magento 2 Installer?

Hello Magento Friends,

In today’s Magento 2 tutorial, I will explain How to Delete an Existing Foreign Key in the Magento 2 Installer.

A foreign key is a column or a group of columns in a database table that uniquely identifies a row in another table. It establishes a relationship between two tables, known as the parent table and the child table, where the foreign key column(s) in the child table reference the primary key column(s) in the parent table.

Foreign keys in Magento 2 databases play a crucial role in maintaining data integrity and enforcing referential integrity between tables. Check the below image, you can find foreign keys with details in the table.

However, there may be instances where you need to delete an existing foreign key, perhaps during module uninstallation or database restructuring.

Let’s move to the steps to delete an existing foreign key in Magento 2 Installer.

Steps to Delete an Existing Foreign Key in Magento 2 Installer:

Step 1: Create DropExistingForeignKey.php file

app/code/Vendor/Extension/Setup/Patch/Schema/

Then add the code as follows

<?php

namespace Vendor\Extension\Setup\Patch\Schema;

use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\Setup\Patch\PatchVersionInterface;
use Magento\Framework\Setup\Patch\SchemaPatchInterface;

class DropExistingForeignKey implements SchemaPatchInterface, PatchVersionInterface
{
    private $schemaSetup;

    public function __construct(
        SchemaSetupInterface $schemaSetup
    ) {
        $this->schemaSetup = $schemaSetup;
    }

    public function apply()
    {
        $this->schemaSetup->startSetup();
        $tableName = $this->schemaSetup->getTable('Custom'); //Table Name

        $existingForeignKeys = $this->schemaSetup->getConnection()->getForeignKeys($tableName);

        foreach ($existingForeignKeys as $constraint) {
            if ($constraint['TABLE_NAME'] == $tableName && isset($constraint['FK_NAME'])) {
                $this->schemaSetup->getConnection()->dropForeignKey($tableName, $constraint['FK_NAME']);
            }
        }

        $this->schemaSetup->endSetup();
    }

    /**
     * Get dependencies
     */
    public static function getDependencies()
    {
        return [];
    }

    /**
     * Get version
     */
    public static function getVersion()
    {
        return '2.0.4';
    }

    /**
     * Get aliases
     */
    public function getAliases()
    {
        return [];
    }
}

Output:

You can find that the foreign keys have been removed from the table

Best Practices before Delete an Existing Foreign Key:

• Always backup your database before making any changes, especially when deleting foreign keys.
• Follow Magento’s coding standards and best practices while writing patches and SQL queries.
• Document your changes thoroughly for future reference and maintainability.

Conclusion:

Deleting existing foreign keys in Magento 2 installers requires careful consideration and adherence to best practices to ensure data integrity and application stability. By following the steps outlined in this guide and exercising caution, you can effectively manage foreign key constraints in your Magento 2 modules, contributing to a robust and maintainable e-commerce platform.

Hire Magento Developer to manage your Magento 2 database without any data loss.

Happy Coding!