Adding a Foreign Key Constraint or Index to Table

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

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):


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() 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.


Depending on your need, you would probably add another foreign key referencing catalog/products so than, when a Product is deleted, your Item’s relation to the Product would be deleted as well.

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)