21

I am trying to add a set of new tables and I'm trying to add a foreign key to link these tables. I have looked online to try and find some information about addForeignKey() but cannot find anything.

Looking at Varien_Db_Adapter_Interface::addForeignKey() and Mage_Core_Model_Resource_Setup::getFkName() I thought I had got below setup correctly

->addForeignKey(
     $installer->getFkName('namespace_module/shop', 'area_id', 'namespace_module/area','area_id'),
     $installer->getTable('namespace_module/shop'), 
     'area_id',
     $installer->getTable('namespace_module/area'), 
     'area_id',
     Varien_Db_Ddl_Table::ACTION_CASCADE, 
     Varien_Db_Ddl_Table::ACTION_CASCADE
)  

But the exception says different

Zend_Db_Exception Object
(
[_previous:Zend_Exception:private] => 
[message:protected] => Undefined column "shop_locator_shop"
[string:Exception:private] => 
[code:protected] => 0
[file:protected] => /Users/myname/Sites/site/www/lib/Varien/Db/Ddl/Table.php
[line:protected] => 450
[trace:Exception:private] => Array
    (
        [0] => Array
            (
                [file] => /Users/myname/Sites/site/www/app/code/local/Namespace/Module/sql/namespace_module_setup/install-0.1.0.php
                [line] => 110
                [function] => addForeignKey
                [class] => Varien_Db_Ddl_Table
                [type] => ->
                [args] => Array
                    (
                        [0] => FK_SHOP_LOCATOR_SHOP_AREA_ID_SHOP_LOCATOR_AREA_AREA_ID
                        [1] => shop_locator_shop
                        [2] => area_id
                        [3] => shop_locator_area
                        [4] => area_id
                        [5] => CASCADE
                        [6] => CASCADE
                    )

            )

Basically how do I go about adding a foreign key to my table (I have added a screen shot of what I am trying to achieve)

database

29

Try it like this:

->addForeignKey(
     $installer->getFkName('namespace_module/shop', 'area_id', 'namespace_module/area','area_id'),
     'area_id',
     $installer->getTable('namespace_module/area'), 
     'area_id',
     Varien_Db_Ddl_Table::ACTION_CASCADE, 
     Varien_Db_Ddl_Table::ACTION_CASCADE
)  

The difference in my code is that I removed the first line below getFkName.
since you are calling addForeignKey on a table while creating it, you don't need to specify it's name again.

as a general rule use:

->addForeignKey(
     $installer->getFkName('TABLE ALIAS', 'FK_FIELD', 'REF_TABLE_ALIAS','REF_TABLE_FIELD'),
     'CURRENT_TABLE_FIELD_NAME',
     $installer->getTable('REF_TABLE_ALIAS'), 
     'REF_TABLE_FIELD',
     Varien_Db_Ddl_Table::ACTION_CASCADE, 
     Varien_Db_Ddl_Table::ACTION_CASCADE
)  

[Edit]
You should be looking at Varien_Db_Ddl_Table::addForeignKey because you are using it on an instance of Varien_Db_Ddl_Table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.