cancel
Showing results for 
Search instead for 
Did you mean: 

SQLSTATE[HY000]: General error: 3780 Referencing column 'store_id' and referenced column 'store_id'

SQLSTATE[HY000]: General error: 3780 Referencing column 'store_id' and referenced column 'store_id'

create new table in my new module by InstallSchema facing this error when run php bin/magento setup:upgrade command (magento 2.4) SQLSTATE[HY000]: General error: 3780 Referencing column 'store_id' and referenced column 'store_id' in foreign key constraint are incompatible

<?php

namespace Dealer\Sellout\Setup;

use Magento\Framework\Setup\InstallSchemaInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\DB\Ddl\Table;

class InstallSchema implements InstallSchemaInterface
{
    /**
     * {@inheritdoc}
     */
    public function install(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
        $setup->startSetup();

        /**
         * Create table 'sell-out-order'
        */

        $table = $setup->getConnection()->newTable(
            $setup->getTable('sell-out-order')
        )->addColumn(
            'id',
            Table::TYPE_INTEGER,
            null,
            ['identity' => true, 'nullable' => false, 'primary' => true],
            'SellOut ID'
        )->addColumn(
            'company_user_id',
            Table::TYPE_INTEGER,
            255,
            ['nullable' => false],
            'Company User Id'
        )->addColumn(
            'customer_id',
            Table::TYPE_INTEGER,
            255,
            ['nullable' => false],
            'Customer Id'
        )->addColumn(
            'store_id',
            Table::TYPE_INTEGER,
            255,
            ['nullable' => false],
            'Store Id'
        )->addIndex(
            $setup->getIdxName('sell-out-order', ['customer_id']),
            ['customer_id']
        )->setComment(
            'Sell Outs'
        )->addForeignKey(
            $setup->getFkName('sell-out-order', 'store_id', 'store', 'store_id'),
            'store_id',
            $setup->getTable('store'),
            'store_id',
            \Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
        )->addForeignKey(
            $setup->getFkName('sell-out-order', 'customer_id', 'customer_entity', 'customer_id'),
            'customer_id',
            $setup->getTable('customer_entity'),
            'customer_id',
            \Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
        );
        $setup->getConnection()->createTable($table);

        /**
         * Create table 'sell-out-order-item'
        */

        $table = $setup->getConnection()->newTable(
            $setup->getTable('sell-out-order-item')
        )->addColumn(
            'id',
            Table::TYPE_INTEGER,
            null,
            ['identity' => true, 'nullable' => false, 'primary' => true],
            'SellOut Order Item ID'
        )->addColumn(
            'sell-out-order_id',
            Table::TYPE_INTEGER,
            255,
            ['nullable' => false],
            'SellOut Order Id'
        )->addColumn(
            'product_id',
            Table::TYPE_INTEGER,
            255,
            ['nullable' => false],
            'Product Id'
        )->addColumn(
            'name',
            Table::TYPE_TEXT,
            255,
            ['nullable' => false],
            'Product SKU'
        )->addColumn(
            'product_type',
            Table::TYPE_TEXT,
            255,
            ['nullable' => false],
            'Product SKU'
        )->addColumn(
            'sku',
            Table::TYPE_INTEGER,
            255,
            ['nullable' => false],
            'Product SKU'
        )->addColumn(
            'qty',
            Table::TYPE_INTEGER,
            255,
            ['nullable' => false],
            'Qty'
        )->addColumn(
            'store_id',
            Table::TYPE_INTEGER,
            255,
            ['nullable' => false],
            'Store Id'
        )->addIndex(
            $setup->getIdxName('sell-out-order-item', ['sku']),
            ['sku']
        )->setComment(
            'Sell Out Items'
        )->addForeignKey(
            $setup->getFkName('sell-out-order-item', 'store_id', 'store', 'store_id'),
            'store_id',
            $setup->getTable('store'),
            'store_id',
            \Magento\Framework\DB\Ddl\Table::ACTION_NO_ACTION
        )->addForeignKey(
            $setup->getFkName('sell-out-order-item', 'sell-out-order_id', 'sell-out-order', 'sell-out-order_id'),
            'sell-out-order_id',
            $setup->getTable('sell-out-order'),
            'sell-out-order_id',
            \Magento\Framework\DB\Ddl\Table::ACTION_NO_ACTION
        );
        $setup->getConnection()->createTable($table);
        $setup->endSetup();
    }
}

Screenshot from 2020-12-16 08-42-16.pngScreenshot from 2020-12-16 10-12-13.png

1 REPLY 1

Re: SQLSTATE[HY000]: General error: 3780 Referencing column 'store_id' and referenced column 'store_

Hi @emanmohameda68,

 

You could try edit the column Store ID by using the below code:

addColumn(
            'store_id',
            Table::TYPE_SMALLINT,
            255,
            ['nullable' => false, 'unsigned' => true],
            'Store Id'
        )

Hope this can help you! Let me know if you need further assistance. 

__________

If issue solved, Click Kudos & Accept as Solution.

 

LitExtension - #1 Shopping Cart Migration Expert

LitExtension helps store owners and agencies migrate all important data from one eCommerce platform to another accurately, securely and at the highest speed.

Visit website: http://litextension.com/