How to add a foreign key to a database table

dmitry_fedyuk · January 26, 2016, 10:31pm

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

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


/**
 * Add new Foreign Key to table
 * If Foreign Key with same name is exist - it will be deleted
 *
 * @param string $fkName
 * @param string $tableName
 * @param string $columnName
 * @param string $refTableName
 * @param string $refColumnName
 * @param string $onDelete
 * @param string $onUpdate
 * @param boolean $purge trying remove invalid data
 * @param string $schemaName
 * @param string $refSchemaName
 * @return \Magento\Framework\DB\Adapter\AdapterInterface
 *
 * @SuppressWarnings(PHPMD.ExcessiveParameterList)
 */
public function addForeignKey(
    $fkName,
    $tableName,
    $columnName,
    $refTableName,
    $refColumnName,
    $onDelete = self::FK_ACTION_CASCADE,
    $purge = false,
    $schemaName = null,
    $refSchemaName = null
);

Usage examples from the core:

github.com

magento/magento2/blob/720667e/app/code/Magento/Variable/Setup/InstallSchema.php#L133-L140


$connection->addForeignKey(
    $installer->getFkName('variable_value', 'store_id', 'store', 'store_id'),
    $variableValueTableName,
    'store_id',
    $installer->getTable('store'),
    'store_id',
    \Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
);

github.com

magento/magento2/blob/720667e/app/code/Magento/Variable/Setup/InstallSchema.php#L141-L148


$connection->addForeignKey(
    $installer->getFkName('variable_value', 'variable_id', 'variable', 'variable_id'),
    $variableValueTableName,
    'variable_id',
    $installer->getTable('variable'),
    'variable_id',
    \Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
);

github.com

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


/**
 * Add foreign keys again
 */
$setup->getConnection()->addForeignKey(
    $setup->getFkName(
        Gallery::GALLERY_VALUE_TABLE,
        'value_id',
        Gallery::GALLERY_TABLE,
        'value_id'
    ),
    $setup->getTable(Gallery::GALLERY_VALUE_TABLE),
    'value_id',
    $setup->getTable(Gallery::GALLERY_TABLE),
    'value_id',
    \Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
);

github.com

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


$setup->getConnection()->addForeignKey(
    $setup->getFkName(
        Gallery::GALLERY_VALUE_TABLE,
        'store_id',
        $setup->getTable('store'),
        'store_id'
    ),
    $setup->getTable(Gallery::GALLERY_VALUE_TABLE),
    'store_id',
    $setup->getTable('store'),
    'store_id',
    \Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
);

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#L2636-L2669


public function addForeignKey(
    $fkName,
    $tableName,
    $columnName,
    $refTableName,
    $refColumnName,
    $onDelete = AdapterInterface::FK_ACTION_CASCADE,
    $purge = false,
    $schemaName = null,
    $refSchemaName = null
) {
    $this->dropForeignKey($tableName, $fkName, $schemaName);


    if ($purge) {
        $this->purgeOrphanRecords($tableName, $columnName, $refTableName, $refColumnName, $onDelete);
    }


    $query = sprintf(
        'ALTER TABLE %s ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s (%s)',
        $this->quoteIdentifier($this->_getTableName($tableName, $schemaName)),
        $this->quoteIdentifier($fkName),
        $this->quoteIdentifier($columnName),
        $this->quoteIdentifier($this->_getTableName($refTableName, $refSchemaName)),
        $this->quoteIdentifier($refColumnName)
    );


    if ($onDelete !== null) {
        $query .= ' ON DELETE ' . strtoupper($onDelete);
    }


    $result = $this->rawQuery($query);
    $this->resetDdlCache($tableName);
    return $result;
}

dmitry_fedyuk · January 26, 2016, 10:40pm