Magento 2 – Foreign Key for catalog_product_entity

databaseforeign keymagento2

Good evening.

I am trying to create new table (product_store):

<?php
/**
 * @author Convert Team
 * @copyright Copyright (c) Convert (http://www.convert.no/)
 */

namespace Convert\PhysicalStoreStock\Setup;

use Magento\Framework\Setup\InstallSchemaInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;

/**
 * Class InstallSchema
 * @package Convert\PhysicalStoreStock\Setup
 */
class InstallSchema implements InstallSchemaInterface
{
    /**
     * @param SchemaSetupInterface $setup
     * @param ModuleContextInterface $context
     */
    public function install(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
        /**
         * Create table 'product_store'
         */


        $table = $setup->getConnection()
            ->newTable($setup->getTable('product_store'))
            ->addColumn(
                'entity_id',
                \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
                null,
                ['identity' => true, 'unsigned' => true, 'nullable' => false, 'primary' => true],
                'Entity ID'
            )
            ->addColumn(
                'product_id',
                \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
                10,
                ['nullable' => false],
                'Product ID'
            )
            ->addColumn(
                'stockist_id',
                \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
                null,
                ['nullable' => false],
                'Stockist Id'
            )
            ->addColumn(
                'quantity',
                \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
                null,
                ['nullable' => false],
                'Quantity Value'
            )
            ->addForeignKey(
                $setup->getFkName(
                    'product_store',
                    'product_id',
                    'catalog_product_entity',
                    'entity_id'
                ),
                'entity_id',
                $setup->getTable('catalog_product_entity'),
                'entity_id'
            )
            ->setComment("Relations between stockists and products");
        $setup->getConnection()->createTable($table);
    }
}

But getting error message after run: php bin/magento setup:upgrade

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint, query was: CREATE TABLE IF NOT EXISTS `product_store` (
  `entity_id` int UNSIGNED NOT NULL auto_increment COMMENT 'Entity ID' ,
  `product_id` int NOT NULL COMMENT 'Product ID' ,
  `stockist_id` int NOT NULL COMMENT 'Stockist Id' ,
  `quantity` int NOT NULL COMMENT 'Quantity Value' ,
  PRIMARY KEY (`entity_id`),
  CONSTRAINT `PRODUCT_STORE_PRODUCT_ID_CATALOG_PRODUCT_ENTITY_ENTITY_ID` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE NO ACTION
) COMMENT='Relations between stockists and products' ENGINE=innodb charset=utf8 COLLATE=utf8_general_ci

I've checked each column and compared key (product_id and entity_id - both int(10))

Also info about new product_store.

mysql> describe catalog_product_entity;
+------------------+----------------------+------+-----+-------------------+-----------------------------+
| Field            | Type                 | Null | Key | Default           | Extra                       |
+------------------+----------------------+------+-----+-------------------+-----------------------------+
| entity_id        | int(10) unsigned     | NO   | PRI | NULL              | auto_increment              |
| attribute_set_id | smallint(5) unsigned | NO   | MUL | 0                 |                             |
| type_id          | varchar(32)          | NO   |     | simple            |                             |
| sku              | varchar(64)          | YES  | MUL | NULL              |                             |
| has_options      | smallint(6)          | NO   |     | 0                 |                             |
| required_options | smallint(5) unsigned | NO   |     | 0                 |                             |
| created_at       | timestamp            | NO   |     | CURRENT_TIMESTAMP |                             |
| updated_at       | timestamp            | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------------+----------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.01 sec)

I've googled, but found no any useful information.

Thanks for any advise and help 🙂

Best Answer

You should change this

->addColumn(
                'product_id',
                \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
                10,
                ['nullable' => false],
                'Product ID'
            )

to

->addColumn(
                'product_id',
                \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
                null,
                ['unsigned' => true, 'nullable' => false],
                'Product ID'
            )
Related Topic