php - SQLSTATE[HY000]: General error: 3780 Referencing column 'store_id' and referenced column 'store_id' in foreign key constraint are incompatible
Get the solution ↓↓↓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();
}
}
Answer
Solution:
I guess you're on Laravel 5.8 or above. This issue was at first never mentioned in Laravel update docs.
It's a column definition mismatch which throwing error on 'incompatible types'. your 'store_id' column's type is 'smallint' which is incompatible to to the 'store_id' of the referred column(?). If you make sure both of same type, you can avoid this error. could you post the referencing table structure?
check carefully the types defined on each migration.
more here,
https://logic.edchen.org/how-to-resolve-cannot-add-foreign-key-constraint-in-mysql/
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: regex stop at first match
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.