If you’ve moved across sites from a development to live site or your encountering this error after importing/exporting then here’s the solution that works every time.
1. Search for “catalog_product_flat” . Under my installation its called catalog_product_flat_1
2. Then in mysql run:
SET FOREIGN_KEY_CHECKS=0; DROP TABLE catalog_product_flat_1
3. Last step open up SSH and run (or do this from the Magento admin)
php path/to/shell/indexer.php --reindex catalog_product_flat or navigate to the /shell directory and run php indexer.php --reindex catalog_product_flat
You should get “Product Flat Data index was rebuilt successfully”