Magento – Use Flat Catalog Category disable

When filtering by stock status on a product collection you can use the standard way in magento as follows.

Mage::getSingleton('cataloginventory/stock')->addInStockFilterToCollection($collection);

This will eventually call setInStockFilterToCollection on Mage_CatalogInventory_Model_Resource_Stock

public function setInStockFilterToCollection($collection)
{
    $manageStock = Mage::getStoreConfig(Mage_CatalogInventory_Model_Stock_Item::XML_PATH_MANAGE_STOCK);
    $cond = array(
        '{{table}}.use_config_manage_stock = 0 AND {{table}}.manage_stock=1 AND {{table}}.is_in_stock=1',
        '{{table}}.use_config_manage_stock = 0 AND {{table}}.manage_stock=0',
    );

    if ($manageStock) {
        $cond[] = '{{table}}.use_config_manage_stock = 1 AND {{table}}.is_in_stock=1';
    } else {
        $cond[] = '{{table}}.use_config_manage_stock = 1';
    }

    $collection->joinField(
        'inventory_in_stock',
        'cataloginventory/stock_item',
        'is_in_stock',
        'product_id=entity_id',
        '(' . join(') OR (', $cond) . ')'
    );
    return $this;
}

From looking at the MySQL error code 1215, it looks like you have mismatching column definition in your flat table. When you establish a FK constraint, the columns must be identical.

Your FK FK_CATALOG_CATEGORY_FLAT_STORE_1_STORE_ID_CORE_STORE_STORE_ID uses core_store.store_id as the reference. I checked CE 1.9, and it's supposed to be of smallint(5) unsigned NOT NULL. However, I'm seeing your flat table is trying to create store_id with smallint UNSIGNED NOT NULL default, which gives you an error.

I'm not familiar with how the flat table gets created, but there's definitely something wrong with the column definitions. I suggest you check your core_store.store_id and make sure that is the same as what the flat table is trying to create.

Update:

If you're missing FKs on the flat table, it's not correct, as it's supposed to be there. You may have some customization that removed things incorrectly.

Check out Mage_Catalog_Model_Resource_Category_Flat::_getStaticColumns, and see if you see store_id get 'type' => array(Varien_Db_Ddl_Table::TYPE_SMALLINT, 5).