Adding a Foreign Key Constraint or Index to Table

Last modified by nikola99 on Wed, October 6, 2010 10:20
Source|Old Revisions  

This is an old revision of the document!


Magento has a built in adapter to MySQL which allows you to add and drop constraints and keys.

You will be leveraging Varien_Db_Adapter_Pdo_Mysql’s functions addKey() and addConstraint(). A good example is app/code/core/Mage/Wishlist/sql/wishlist_setup/mysql4-upgrade-0.7.6-0.7.7.php.

In the code examples assume we are modifying a module called Items. Let’s assume the Items module allows the user to add/edit Items each of which is associated to one or more Products.

You will be making changes in your module’s mysql4-upgrade-X.X.X-X.X.X.php file (e.g. app/code/local/<Namespace>/Items/sql/items_setup/mysql4-upgrade-0.0.1-0.0.2.php).

In addition you should set the new version number in the module’s config.xml (e.g. app/code/local/<Namespace>/Items/etc/config.xml):

<config>
    <modules>
        <[Namespace]_Items>
            <version>0.0.2</version>
...

See more in the Custom Module with Custom Database Table Wiki article.

Adding an Index

Add an index to items_relation.product_id.

$installer
    ->getConnection()
    ->addKey(
        $installer->getTable('items/relation'), 
        'IDX_PRODUCT', 
        'product_id'
    );

$installer→getConnection() returns Varien_Db_Adapter_Pdo_Mysql.

addKey Function Definition

addKey($tableName, $indexName, $fields, $indexType = 'index')

where $fields can be an array for a multiple-field index and $indexType can be one of PRIMARY KEY, UNIQUE, FULLTEXT or INDEX.

Adding a Foreign Key Constraint

Add a foreign key constraint to items_relation.item_id referencing items_item.item_id. Both ON DELETE and ON UPDATE are CASCADE.

$installer
    ->getConnection()
    ->addConstraint(
        'FK_ITEMS_RELATION_ITEM',
        $installer->getTable('items/relation'), 
        'item_id',
        $installer->getTable('items/item'), 
        'item_id',
        'cascade', 
        'cascade'
);

Depending on your need, you would probably add another index referencing catalog/products.

addConstraint Function Definition

/**
     * Add foreign key to table. If FK with same name exist - it will be deleted
     *
     * @param string $fkName foreign key name
     * @param string $tableName main table name
     * @param string $keyName main table field name
     * @param string $refTableName refered table name
     * @param string $refKeyName refered table field name
     * @param string $onUpdate on update statement
     * @param string $onDelete on delete statement
     * @param bool $purge
     * @return mixed
     */
    public function addConstraint($fkName, $tableName, $columnName,
        $refTableName, $refColumnName, $onDelete = 'cascade', $onUpdate = 'cascade', $purge = false)



 

Magento 2 GitHub Repository

Magento Job Board - Some sort of tag line goes here

Latest Posts| View all Jobs