Magento 2 – InstallSchema Error 1215 Fix

foreign keyinstall-scriptmagento2

I am trying to create InstallSchema script in magento2. However, I get the fallowing error:

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

The code is as fallows:

$table = $installer->getConnection()
                            ->newTable($installer->getTable('gift_products'))
                            ->addColumn(
                                    'gift_id',
                                    \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
                                    null,
                                    ['identity' => true, 'unsigned' => true, 'nullable' => false, 'primary' => true],
                                    'Gift Id'
                            )
                            ->addColumn(
                                    'store_id',
                                    \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
                                    null,
                                    ['unsigned' =>  true, 'nullable' => true],
                                    'Store Id'
                            )
                            ->addColumn(
                                    'entity_id',
                                    \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
                                    null,
                                    ['unsigned' =>  true, 'nullable' => true],
                                    'Product Id'
                            )
                            ->addColumn(
                                    'priority',
                                    \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
                                    null,
                                    ['unsigned' =>  true],
                                    'Gift priority'
                            )
                            ->addIndex(
                                    $installer->getIdxName('gift_products', ['gift_id']),
                                    ['gift_id']
                            )
                            ->addIndex(
                                    $installer->getIdxName('gift_products', ['priority']),
                                    ['priority']
                            )
                            ->addForeignKey(
                                    $installer->getFkName('gift_products', 'store_id', 'store', 'store_id'),
                                    'store_id',
                                    $installer->getTable('store'),
                                    'store_id',
                                    \Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
                            )
                            ->addForeignKey(
                                    $installer->getFkName('gift_products', 'entity_id', 'catalog_product_entity',   'entity_id'),
                                    'entity_id',
                                    $installer->getTable('catalog_product_entity'),
                                    'entity_id',
                                    \Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
                            )
                            ->setComment('Gift module table');

The generated query is :

CREATE TABLE IF NOT EXISTS `gift_products` (                           
    `gift_id` int UNSIGNED NOT NULL auto_increment COMMENT 'Gift Id' ,                                                                                      
    `store_id` int UNSIGNED NULL COMMENT 'Store Id' ,                                                                                                       
    `entity_id` int UNSIGNED NULL COMMENT 'Product Id' ,                                                                                                    
    `priority` int UNSIGNED NULL COMMENT 'Gift priority' ,                                                                                                  
    PRIMARY KEY (`gift_id`),                                                                                                                                
    INDEX `GIFT_PRODUCTS_GIFT_ID` (`gift_id`),                                                                                                              
    INDEX `GIFT_PRODUCTS_PRIORITY` (`priority`),                                                                                                            
    CONSTRAINT `GIFT_PRODUCTS_STORE_ID_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE CASCADE,                          
    CONSTRAINT `GIFT_PRODUCTS_ENTITY_ID_CATALOG_PRODUCT_ENTITY_ENTITY_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE                                                                                                                                             
  ) COMMENT='Gift module table' ENGINE=INNODB charset=utf8 COLLATE=utf8_general_ci

Where is the problem ?

Best Answer

data type for the child column must match the parent column exactly. check data type in table store store_id (type is smallint) and your store_id is INTEGER.

Related Topic