1

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 :)

1
  • your column product_id is not unsigned but the entity_id in catalog_product_entity is
    – HelgeB
    Feb 4, 2019 at 21:31

1 Answer 1

4

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'
            )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.