Magento – Magento 2 How to add foreign key in update schema

install-scriptmagento2tableupgradeschema

I try to add foreign key constrains for Magento 2 existing tables. I have UpdateSchema script but it's not working.

<?php

namespace Retailers\Customer\Setup;

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

class UpgradeSchema implements UpgradeSchemaInterface
{
    public function upgrade(
        SchemaSetupInterface $setup,
        ModuleContextInterface $context
    ) {
        if (version_compare($context->getVersion(), '2.0.1') < 0) {
            $setup->startSetup();
            $setup->getConnection();
            $setup->getTable('customer_grid_flat')
                ->addColumn(
                    'status',
                    [
                        'type' => \Magento\Framework\DB\Ddl\Table::TYPE_SMALLINT,
                        'length' => '11',
                        'nullable' => false,
                        'default' => '3',
                        'comment' => 'Retailers Is Allow Status',
                    ]
                )
                ->addForeignKey(
                    $setup->getFkName('customer_grid_flat', 'entity_id', 'customer_entity_varchar', 'entity_id'),
                    'entity_id',
                    $setup->getTable('customer_entity_varchar'),
                    'entity_id',
                    \Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
                );
            $setup->endSetup();
        }
    }
}

Best Answer

Try this:

->addForeignKey(
      $installer->getFkName('<ChildTable>', 'entity_id', '<ParentTable>', 'entity_id'),
      'entity_id',
      $installer->getTable('<ParentTable>'),
      'entity_id',
      \Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
)

Note: Both table has same field and set primary key to parent table.

You are adding Foreign key to wrong table, You can not set customer_grid_flat table's entity_id as foreign key until you set primary key to customer_entity_varchar table's entity_id.

For Add foreign key with store table in Magento 2, Check blog for add foreign key with store table

Related Topic