How does Magento save catalog rule website data into relational tables?

Question

So I'm trying to build a custom extension, which extends from Mage_Rule and is based on Mage_CatalogRule.

Everything has worked fine so far, the conditions work fine and the rule saves normally, but now I've hit a roadblock.

My extension saves a rule, just like the CatalogRule does in a table and then attempts to save the website ids and customer group ids into two relational tables, frostnet_rule/website and frostnet_rule/customer_group. These tables are identical in schema to the catalogrule/website and catalogrule/customer_group tables - the only difference is the frostnet_rule namespace used in place of catalogrule.

When I save a Catalog rule, the website id and customer group ids are saved normally into the respective tables, yet when I attempt the same with my extension, I get a foreign key constraint error (see below).

Now I'm not doing anything custom here, I'm just trying to insert two IDs (rule-id and website_id) into the frostnet_rule_website relational table.

My Resource model for saving my custom rule extends Mage_Rule_Model_Resource_Abstract, which saves website and customer group ids by passing them to Mage_Rule_Model_Resource_Abstract::bindRuleToEntity() (which I've not overridden) and which is called from Mage_Rule_Model_Resource_Abstract::_afterSave() (which I've also not overridden).

So if it's not a code issue (I'm using the same code and saving the same data as the catalog rule module) and it's not a DB issue (I'm using the same db schema, as noted before), then what is it?

Here's the exception:

exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`magento1810_local`.`frostnet_rule_website`, CONSTRAINT `FK_FROSTNET_RULE_WS_RULE_ID_FROSTNET_RULE_RULE_RULE_ID` FOREIGN KEY (`rule_id`) REFERENCES `frostnet_rule/rule` (`rule_)' in /Users/simonfrost/PhpStormProjects/magento1810.local/www/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /Users/simonfrost/PhpStormProjects/magento1810.local/www/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /Users/simonfrost/PhpStormProjects/magento1810.local/www/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /Users/simonfrost/PhpStormProjects/magento1810.local/www/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /Users/simonfrost/PhpStormProjects/magento1810.local/www/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#4 /Users/simonfrost/PhpStormProjects/magento1810.local/www/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `fr...', Array)
#5 /Users/simonfrost/PhpStormProjects/magento1810.local/www/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `fr...', Array)
#6 /Users/simonfrost/PhpStormProjects/magento1810.local/www/lib/Varien/Db/Adapter/Pdo/Mysql.php(1934): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `fr...', Array)
#7 /Users/simonfrost/PhpStormProjects/magento1810.local/www/app/code/core/Mage/Rule/Model/Resource/Abstract.php(171): Varien_Db_Adapter_Pdo_Mysql->insertOnDuplicate('frostnet_messageru...', Array, Array)
#8 /Users/simonfrost/PhpStormProjects/magento1810.local/www/app/code/local/Frostnet/Rule/Model/Resource/Rule.php(61): Mage_Rule_Model_Resource_Abstract->bindRuleToEntity('1', Array, 'website')

I can't for the life of me figure out why this works in the Mage_CatalogRule extension and not in my extension.

Here's the schema for my relational table:

$table = $installer->getConnection()->newTable($installer->getTable('frostnet_rule/website'))
            ->addColumn('rule_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
                    'unsigned'  => true,
                    'nullable'  => false,
                    'primary'   => true
                ),
                'Rule Id'
            )
            ->addColumn('website_id', Varien_Db_Ddl_Table::TYPE_SMALLINT, null, array(
                    'unsigned'  => true,
                    'nullable'  => false,
                    'primary'   => true
                ),
                'Website Id'
            )
            ->addIndex(
                $installer->getIdxName('frostnet_rule/website', array('rule_id')),
                array('rule_id')
            )
            ->addIndex(
                $installer->getIdxName('frostnet_rule/website', array('website_id')),
                array('website_id')
            )
            ->addForeignKey(
                $installer->getFkName('frostnet_rule/website', 'rule_id', 'frostnet_rule/rule', 'rule_id'),
                'rule_id', 'frostnet_rule/rule', 'rule_id',
                Varien_Db_Ddl_Table::ACTION_CASCADE, Varien_Db_Ddl_Table::ACTION_CASCADE
            )
            ->addForeignKey(
                $installer->getFkName('frostnet_rule/website', 'website_id', 'core/website', 'website_id'),
                'website_id', 'core/website', 'website_id',
                Varien_Db_Ddl_Table::ACTION_CASCADE, Varien_Db_Ddl_Table::ACTION_CASCADE
            )
            ->setComment('Zone8 MessageRule Rules To Websites Relations');

        $installer->getConnection()->createTable($table);

Answer 1

OK, so I figured it out.

I was going to drop the foreign keys so I could continue working on this whilst the interwebs offered any suggestions (no such luck, evidently). After running a SHOW CREATE TABLE frostnet_rule_website SQL query to reveal the generated foreign key name, I noticed that the foreign key was referencing the tables frostnet_rule/rule and core/website.

Spotted the problem? Magento database tables don't have slashes in them! So obviously in the addForeignKey() call in my install script, I had entered the group name for the database table as the foreign table parameter, rather than the actual database table name. I should have wrapped that parameter in a call to $installer->getTable('frostnet_rule/rule')

I assume I got confused between the way that the group name for the database table is passed straight in to the getFkName() call without it needing to be wrapped in a getTable() call at that point (because it gets translated in a deeper method).

TL;DR: When specifying the foreign table name of a foreign key in addForeignKey(), pass the group name into getTable() to get the actual table name the modules' config.xml.

Perversely, you don't need to do this when generating the foreign key name with getFkName().

So

->addForeignKey(
                $installer->getFkName('frostnet_rule/website', 'rule_id', 'frostnet_rule/rule', 'rule_id'),
                'rule_id', 'frostnet_rule/rule', 'rule_id',
                Varien_Db_Ddl_Table::ACTION_CASCADE, Varien_Db_Ddl_Table::ACTION_CASCADE
            )

should've been:

->addForeignKey(
                $installer->getFkName('frostnet_rule/website', 'rule_id', 'frostnet_rule/rule', 'rule_id'),
                'rule_id', $installer->getTable('frostnet_rule/rule'), 'rule_id',
                Varien_Db_Ddl_Table::ACTION_CASCADE, Varien_Db_Ddl_Table::ACTION_CASCADE
            )