6

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();
        }
    }
}
3
  • What is your module.xml version? Sep 7 '17 at 7:14
  • module version: 2.0.1 Sep 7 '17 at 7:56
  • I cant find entity_id in your table,
    – Keyur Shah
    Sep 7 '17 at 8:15
15

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

2
  • in case some one follow this code but not working, make sure that the 'type' of both field are the same, also both field are set to 'unsigned'.
    – fudu
    Aug 18 '21 at 3:54
  • @fudu yes, might be possible because this code was valid for magento 2.0.1 and you might be trying this on magento 2.3.x or more Sep 26 '21 at 11:24
3

1 more important thing you need to do is make your column in foreign table is THE SAME as the column in your primary table or it will throw an error when you try to run "php bin/magento setup:upgrade", it took me a lot time to figure this out.

For example:

I have 2 table, 1 is "banner", 2 is "banner_slide", and i want to make "banner_id" in table "banner_slide" become foreign key of "id" in table "banner".

table "banner"

enter image description here

table "banner_slide"


enter image description here


Do you understand what i'm saying? You will need to make those 3 "attributes, null and default" as same as the column you want to define as foreign key.
Also, here is my code to make a foreign key, please take a look.

if ($installer->tableExists('banner_slide')) {
                if ($installer->tableExists('banner')) {
                    if ($installer->tableExists('slide')) {
                        $connection = $installer->getConnection();
                        $connection->addForeignKey(
                            $installer->getFkName('banner_slide','banner_id','banner','id'),
                            'banner_slide',
                            'banner_id',
                            'banner',
                            'id',
                            \Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
                        );
                    }
                }
            }


And here is update code if you want to know as well.

if ($installer->tableExists('banner_slide')){
                $connection = $installer->getConnection();
                $tableName = $installer->getTable('banner_slide');
                $banner_id = 'banner_id';

            if($connection->tableColumnExists($tableName,$banner_id, $schemaName = null)){
                $connection->changeColumn(
                    $tableName,
                    $banner_id,
                    $banner_id,
                    ['type' => Table::TYPE_INTEGER, 'nullable' => false, 'unsigned' => true]
                );
            }

            $slide_id = 'slide_id';
            if($connection->tableColumnExists($tableName,$slide_id, $schemaName = null)){
                $connection->changeColumn(
                    $tableName,
                    $slide_id,
                    $slide_id,
                    ['type' => Table::TYPE_INTEGER, 'nullable' => false, 'unsigned' => true]
                );
            }

        }
2

According to new update we should use declarative schema to create and update tables in Magento.

To add foreign key we have to add constraint tag for particular table

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
<table name="declarative_table">
   <constraint xsi:type="foreign" referenceId="FL_ALLOWED_SEVERITIES" table="declarative_table"
           column="severity" referenceTable="severities" referenceColumn="severity_identifier"
           onDelete="CASCADE"/>
</table>

Please follow instruction from official DevDoc.

2

Sample for foreign key,

<table comment="projectfinder_attributevalue Table" engine="innodb" name="projectfinder_attributevalue" resource="default">
    <column comment="Entity Id" identity="true" name="attributevalue_id" nullable="false" padding="6" unsigned="true" xsi:type="smallint"/>
    <constraint referenceId="PRIMARY" xsi:type="primary">
        <column name="attributevalue_id"/>
    </constraint>
    <column xsi:type="int" name="option_id" padding="10" unsigned="true" nullable="false" comment="Option ID"/>
    <column name="option_value" nullable="true" xsi:type="text"/>
    <column comment="attrtbl_id" name="attrtbl_id" nullable="false" padding="6" unsigned="false" xsi:type="smallint"/>
    <column name="attribute_id" nullable="true" xsi:type="smallint"/>
    <column name="attribute_value" nullable="true" xsi:type="text"/>
    <column name="attribute_code" nullable="true" xsi:type="text"/>

    <constraint xsi:type="foreign" referenceId="ATTRIBUTE_VALUE_ID_ATTRIBUTE_TABLE" table="projectfinder_attributevalue" column="attrtbl_id" referenceTable="projectfinder_attribute" referenceColumn="id" onDelete="CASCADE"/>

    <constraint xsi:type="foreign" referenceId="ATTRIBUTE_OPTION_ID_EAV_ATTRIBUTE" table="projectfinder_attributevalue" column="option_id" referenceTable="eav_attribute_option" referenceColumn="option_id" onDelete="CASCADE"/>
</table>
0

In Upgrade schema do like this

$setup->getConnection()->addForeignKey(
                $setup->getFkName('current_table', 'current_column', $setup->getTable('parent_table'), 'parent_column'),
                $setup->getTable('current_table'),
                'current_column',
                $setup->getTable('parent_table'),
                'parent_column',
                \Magento\Framework\DB\Ddl\Table::ACTION_NO_ACTION
            );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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