How to delete (drop) a foreign key from a database table

dmitry_fedyuk · January 26, 2016, 4:13pm

Use the dropForeignKey() method of the \Magento\Framework\DB\Adapter\AdapterInterface:

magento/magento2/blob/720667e/lib/internal/Magento/Framework/DB/Adapter/AdapterInterface.php#L393-L401


/**
 * Drop the Foreign Key from table
 *
 * @param string $tableName
 * @param string $fkName
 * @param string $schemaName
 * @return \Magento\Framework\DB\Adapter\AdapterInterface
 */
public function dropForeignKey($tableName, $fkName, $schemaName = null);

A usage example from the core:

github.com

magento/magento2/blob/720667e/app/code/Magento/Catalog/Setup/UpgradeSchema.php#L180-L191


/**
 * Drop primary index
 */
$setup->getConnection()->dropForeignKey(
    $setup->getTable(Gallery::GALLERY_VALUE_TABLE),
    $setup->getFkName(
        Gallery::GALLERY_VALUE_TABLE,
        'value_id',
        Gallery::GALLERY_TABLE,
        'value_id'
    )
);

By default the \Magento\Framework\DB\Adapter\AdapterInterface interface is implemented by the \Magento\Framework\DB\Adapter\Pdo\Mysql class:

github.com

magento/magento2/blob/720667e/lib/internal/Magento/Framework/DB/Adapter/Pdo/Mysql.php#L753-L772


public function dropForeignKey($tableName, $fkName, $schemaName = null)
{
    $foreignKeys = $this->getForeignKeys($tableName, $schemaName);
    $fkName = strtoupper($fkName);
    if (substr($fkName, 0, 3) == 'FK_') {
        $fkName = substr($fkName, 3);
    }
    foreach ([$fkName, 'FK_' . $fkName] as $key) {
        if (isset($foreignKeys[$key])) {
            $sql = sprintf(
                'ALTER TABLE %s DROP FOREIGN KEY %s',
                $this->quoteIdentifier($this->_getTableName($tableName, $schemaName)),
                $this->quoteIdentifier($foreignKeys[$key]['FK_NAME'])
            );
            $this->resetDdlCache($tableName, $schemaName);
            $this->rawQuery($sql);
        }
    }
    return $this;
}

dmitry_fedyuk · January 26, 2016, 4:20pm