Page tree
Skip to end of metadata
Go to start of metadata

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 functionsaddKey() and addConstraint(). A good example isapp/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 (<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 (<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 KEYUNIQUE,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) 



  • No labels