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.

 Expand source

$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.

 Expand source

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


 Expand source



  • No labels