1

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 ?

4
  • Does the store database table exists ? I reckon that's the problem here Mar 11 '16 at 11:16
  • yes, it does exist Mar 11 '16 at 11:37
  • What if you try to run the SQL query directly on the database, you may get more informations ? Mar 11 '16 at 11:41
  • i tried that already, the same error occurs - Error Code: 1215. Cannot add foreign key constraint. I use MySQL Workbench Mar 11 '16 at 11:46
12

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.

2
  • Thanks..!! Working perfectly. Feb 21 '20 at 9:57
  • Thanks Its working perfectly
    – Deki Akbar
    Jul 30 '20 at 2:43
0

if you have taken the backup of same table and if foreign key constraint is same as bkp table and current table mysql will throw exception, so change the constraint name or delete the existing constraint

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.